Oracle blob преобразовать в текст
Я пытаюсь увидеть из консоли SQL, что находится внутри Oracle BLOB.
Я знаю, что он содержит довольно большой текст, и я хочу просто увидеть текст, но следующий запрос указывает только на то, что в этом поле есть BLOB:
результат, который я получаю, не совсем то, что я ожидал:
Итак, какие магические заклинания я могу использовать, чтобы превратить BLOB в его текстовое представление?
PS: Я просто пытаюсь посмотреть содержимое BLOB-объекта из консоли SQL (Eclipse Data Tools), а не использовать его в коде.
Прежде всего, вы можете захотеть хранить текст в столбцах CLOB / NCLOB вместо BLOB, который предназначен для двоичных данных (кстати, ваш запрос будет работать с CLOB).
Следующий запрос позволит вам увидеть первые 32767 символов (самое большее) текста внутри большого двоичного объекта при условии, что все наборы символов совместимы (исходная CS текста, хранящегося в BLOB, CS базы данных, используемой для VARCHAR2):
К сожалению, я не контролирую схему базы данных - мне просто нужно заглянуть в blob . Но все равно спасибо.
Спасибо, Mac, все работает нормально --- Но какова цель этого "dbms_lob.substr"? --- Только использование "select utl_raw.cast_to_varchar2 (BLOB_FIELD) . ", кажется, дает мне тот же результат .
У меня не работает - я получаю «ORA-06502: PL / SQL: числовая ошибка или ошибка значения: необработанная длина переменной слишком велика». Я могу поставить "2000,1" после BLOB_FIELD, чтобы получить до 2000 символов, но не более того.
если значение больше 4000, это вызовет ошибки, поскольку это максимальное значение для строк в sql. вам нужно добавить substr (BLOB_FIELD, 4000, 1). Если вам нужна более длительная полевая поддержка, используйте PL / SQL (я полагаю, до 32000)
SQL Developer также предоставляет эту функциональность:
Дважды щелкните ячейку сетки результатов и нажмите кнопку изменить:
Затем в правой верхней части всплывающего окна «Просмотреть как текст» (вы даже можете видеть изображения ..)
могу ли я преобразовать Oracle BLOB в Base64 CLOB за один раз?
Я знаю, что могу добавить функции / сохраненный proc для выполнения работы. Аспект производительности очень важен, поэтому я спрашиваю, есть ли способ преодолеть ограничение 32K, напрямую вставляя данные в CLOB.
при условии, что сохраненные процессы будут, несмотря на жизнеспособную альтернативу для вас, вот одно возможное решение вашей проблемы .
во-первых, давайте сделаем это красиво
эта функция есть отсюда должен делать свою работу.
тогда обновление может выглядеть как
обратите внимание, что, возможно, функция должна быть оптимизирована с помощью функции DBMS_LOB.Добавьте вместо этого оператор конкатенации. Попробуйте это, если у вас есть проблемы с производительностью.
Я решил эту же проблему на работе, используя хранимую процедуру Java. В таком подходе нет чанкинга/контатенации VARCHAR2s, поскольку возможность кодирования / декодирования base64 изначально встроена в Java, просто написание функции Oracle, которая тонко обертывает метод Java, работает хорошо и является высокопроизводительной, так как как только вы выполнили его несколько раз, HotSpot JVM компилирует Java proc в низкоуровневый код (высокая производительность так же, как и хранимая функция C). Я буду редактировать этот ответ позже и добавьте сведения об этом коде Java.
но чтобы отступить всего на один шаг, вопрос, почему вы храните эти данные как BLOB и base64 закодированы (CLOB)? это потому что у вас есть клиенты, которые хотят использовать данные в последнем формате? Я бы предпочел хранить только формат BLOB. Одна из причин заключается в том, что кодированная версия base64 может быть вдвое больше размера исходного двоичного BLOB, поэтому сохранение их обоих означает, возможно, 3x место хранения.
одно решение, которое я реализовал на работе, чтобы создать свою собственную сохраненную функцию Java base64_encode() который кодирует binary --> base64, а затем использует эту функцию для кодирования base64 на лету во время запроса (это не дорого). Со стороны приложения / клиента вы запросите что-то вроде SELECT base64_encode(image) FROM test WHERE .
если код приложения нельзя коснуться (т. е. приложение COTS) или если ваши разработчики не в восторге от использования функции, вы можете абстрагировать это для них (так как вы используете 11g+), используя виртуальный (вычисляемый) столбец в таблице, который содержит вычисляемый base64_encode(image) . Он будет функционировать как представление, поскольку физически не будет хранить закодированные Клобы, а будет генерировать их во время запроса. Для любого клиента они не смогут сказать, что не читают физическую колонку. Другое преимущество заключается в том, что если вы когда-либо обновляете jpg (BLOB), виртуальный CLOB немедленно и автоматически обновляется. Если вам когда-либо нужно вставить/обновить / удалить огромный пакет из BLOBs вы сэкономите 66% объема redo / archivelog от необходимости обрабатывать все CLOBs.
наконец, для производительности убедитесь, что вы используете Securefile LOBs (как для BLOBs, так и для CLOBs). Они действительно намного быстрее и лучше почти во всех отношениях.
обновление - я нашел свой код, по крайней мере, версию, которая использует хранимую процедуру Java, чтобы сделать обратное (преобразование кодированного CLOB base64 в его двоичную версию BLOB). Оно не будет трудно написать обратное.
самый простой способ, который я нашел, который работает со специальными символами (в вашем случае у вас нет этой проблемы), использует dbms_lob.конвертоклоб.
Can you please let me know how to extract blob data and save to file in readable format.
I had developed a Pl/SQl and used DBMS_LOB.READ for reading data from blob field and UTL_FILE.PUT to write to a file.
DBMS_LOB.READ( IBLOB, L_AMOUNT, L_POS, L_BUFFER);
@ symbols instead of actual data.
Please help what changes I have to do to code.
Явное преобразование типов
Oracle предоставляет обширный набор функций и операторов, с помощью которых можно выполнить преобразование типов данных в SQL и PL/SQL. Их полный список приведен в табл. 1. Большая часть функций описывается в других главах книги (для них в последнем столбце указан номер главы).
Таблица 1. Функции преобразования типов в PL/SQL
Функция | Выполняемое преобразование |
ASCIISTR | Строку из любого набора символов в строку ASCII из набора символов базы данных |
CAST | Одно значение встроенного типа данных или коллекции в другой встроенный тип данных или коллекцию. Этот способ может использоваться вместо традиционных функций (таких, как TO_DATE) |
CHARTOROWID | Строку в значение типа ROWID |
CONVERT | Строку из одного набора символов в другой |
FROM_TZ | В значение типа TIMESTAMP добавляет информацию о часовом поясе, преобразуя его тем самым в значение типа TIMESTAMP WITH TIME ZONE |
HEXTORAW | Значение из шестнадцатеричной системы в значение типа RAW |
MULTISET | Таблицу базы данных в коллекцию |
NUMTODSINTERVAL | Число (или числовое выражение) в литерал INTERVAL DAY TO SECOND |
NUMTOYMINTERVAL | Число (или числовое выражение) в литерал INTERVAL YEAR TO MONTH |
RAWTOHEX, RAWTONHEX | Значение типа RAW в шестнадцатеричный формат |
REFTOHEX | Значение типа REF в символьную строку, содержащую его шестнадцатеричное представление |
ROWIDTOCHAR, ROWIDTONCHAR | Двоичное значение типа ROWID в символьную строку |
TABLE | Коллекцию в таблицу базы данных; по своему действию обратна функции MULTISET |
THE | Значение столбца в строку виртуальной таблицы базы данных |
TO_BINARY_FLOAT | Число или строку в BINARY_FLOAT |
TO_BINARY_DOUBLE | Число или строку в BINARY_DOUBLE |
TO_CHAR, TO_NCHAR (числовая версия) | Число в строку (VARCHAR2 или NVARCHAR2 соответственно) |
TO_CHAR, TO_NCHAR (версия для дат) | Дату в строку |
TO_CHAR, TO_NCHAR (символьная версия) | Данные из набора символов базы данных в набор символов национального языка |
TO_BLOB | Значение типа RAW в BLOB |
TO_CLOB, TO_NCLOB | Значение типа VARCHAR2, NVARCHAR2 или NCLOB в CLOB (либо NCLOB) |
TO_DATE | Строку в дату |
TO_DSINTERVAL | Символьную строку типа CHAR, VARCHAR2, NCHAR или NVARCHAR2 в тип INTERVAL DAY TO SECOND |
TO_LOB | Значение типа LONG в LOB |
TO_MULTI_BYTE | Однобайтовые символы исходной строки в их многобайтовые эквиваленты (если это возможно) |
TO_NUMBER | Строку или число (например, BINARY_FLOAT) в NUMBER |
TO_RAW | Значение типа BLOB в RAW |
TO_SINGLE_BYTE | Многобайтовые символы исходной строки в соответствующие однобайтовые символы |
TO_TIMESTAMP | Символьную строку в значение типа TIMESTAMP |
TO_TIMESTAMP_TZ | Символьную строку в значение типа TO_TIMESTAMP_TZ |
TO_YMINTERVAL | Символьную строку типа CHAR, VARCHAR2, NCHAR или NVARCHAR2 в значение типа INTERVAL YEAR TO MONTH |
TRANSLATE . USING | Текст в набор символов, заданный для преобразования набора символов базы данных в национальный набор символов |
UNISTR | Строку произвольного набора символов в Юникод |
Функция RAWTOHEX
Преобразует значение типа RAW в шестнадцатеричную строку типа VARCHAR2 . Синтаксис функции RAWTOHEX :
Функция RAWTOHEX всегда возвращает строку переменной длины, хотя обратная ей перегруженная функция HEXTORAW поддерживает оба типа строк.
Ограничения неявного преобразования
Как видно из рис. 1, преобразование может выполняться только между определенными типами данных; PL/SQL не может преобразовать произвольный тип данных в любой другой. Более того, при некоторых неявных преобразованиях типов генерируются исключения. Возьмем следующую операцию присваивания:
В PL/SQL нельзя преобразовать строку «abc» в число, поэтому при выполнении приведенного кода инициируется исключение VALUE_ERROR . Вы сами должны позаботиться о том, чтобы значение, для которого PL/SQL выполняет преобразование типов, могло быть конвертировано без ошибок.
Недостатки неявного преобразования
Неявное преобразование типов имеет ряд недостатков.
- PL/SQL относится к языкам со статической типизацией. Неявные преобразования означают потерю некоторых преимуществ статической типизации — таких, как ясность и надежность кода.
- Каждое неявное преобразование означает частичную потерю контроля над программой. Программист не выполняет его самостоятельно и никак им не управляет, а лишь предполагает, что оно будет выполнено и даст желаемый эффект. В этом есть элемент неопределенности — если компания Oracle изменит способ или условие выполнения преобразований, это может отразиться на программе.
- Неявное преобразование типов в PL/SQL зависит от контекста. Оно может работать в одной программе и не работать в другой, хотя на первый взгляд код кажется одинаковым. Кроме того, результат преобразования типов не всегда соответствует ожиданиями программиста.
- Программу легче читать и понять, если данные в ней преобразуются явно, поскольку при этом фиксируются различия между типами данных в разных таблицах или в таблице и коде. Исключая из программы скрытые действия, вы устраняете и потенциальную возможность ошибок.
Таким образом, в SQL и PL/SQL рекомендуется избегать неявного преобразования типов. Лучше пользоваться функциями, которые выполняют явное преобразование — это гарантирует, что результат преобразования будет точно соответствовать вашим ожиданиям.
Функция CAST
Функция CAST является очень удобным и гибким механизмом преобразования данных. Она преобразует значение любого (или почти любого) встроенного типа данных или коллекции в другой встроенный тип данных или коллекцию, и скорее всего, будет знакома всем программистам с опытом работы на объектно-ориентированных языках.
С помощью функции CAST можно преобразовать неименованное выражение (число, дату, NULL и даже результат подзапроса) или именованную коллекцию (например, вложенную таблицу) в тип данных или именованную коллекцию совместимого типа. Допустимые преобразования между встроенными типами данных показаны на рис. 2. Необходимо соблюдать следующие правила:
- не допускается преобразование типов данных LONG , LONG RAW , любых типов данных LOB и типов, специфических для Oracle;
- обозначению « DATE » на рисунке соответствуют типы данных DATE , TIMESTAMP , TIMESTAMP WITH TIMEZONE , INTERVAL DAY TO SECOND и INTERVAL YEAR TO MONTH ;
- для преобразования именованной коллекции определенного типа в именованную коллекцию другого типа нужно, чтобы элементы обеих коллекций имели одинаковый тип;
Рис. 2. Преобразование встроенных типов данных PL/SQL
- тип UROWID не может быть преобразован в ROWID , если UROWID содержит значение ROWID индекс-таблицы.
Ниже приведен пример использования функции CAST для преобразования скалярных типов данных. Ее вызов может быть включен в SQL-команду:
Также возможен вызов в синтаксисе PL/SQL:
Намного более интересное применение CAST встречается при работе с коллекциями PL/SQL (вложенными таблицами и VARRAY), поскольку эта функция позволяет преобразовывать коллекцию из одного типа в другой. Кроме того, CAST может использоваться для работы (из инструкций SQL) с коллекцией, объявленной как переменная PL/SQL.
Обе темы подробно рассматриваются в главе 12, а следующий пример дает некоторое представление о синтаксисе и возможностях преобразования. Сначала мы создаем два типа вложенных таблиц и одну реляционную таблицу:
Далее пишется программа, которая связывает данные из таблицы favorite_authors с содержимым вложенной таблицы, объявленной и заполненной в другой программе. Рассмотрим следующий блок:
В строках 2 и 3 объявляется локальная вложенная таблица, заполняемая именами нескольких популярных авторов. В строках 7–11 с помощью оператора UNION объединяются строки таблиц favorite_authors и scifi_favorites. Для этого вложенная таблица scifi_favorites (локальная и не видимая для ядра SQL) преобразуется с использованием функции CAST в коллекцию типа names_t. Такое преобразование возможно благодаря совместимости их типов данных. После преобразования вызов команды TABLE сообщает ядру SQL, что вложенная таблица должна интерпретироваться как реляционная. На экран выводятся следующие результаты:
LONG И LONG RAW
Вероятно, спецы, знакомые с Oracle, заметили, что мы до сих пор не упоминали о типах данных LONG и LONG RAW . И это не случайно. Конечно, в столбцах типа LONG и LONG RAW базы данных можно хранить большие объемы (до 2 Гбайт) соответственно символьных и двоичных данных. Однако максимальная длина соответствующих им переменных PL/SQL значительно меньше: всего лишь 32 760 байт, что даже меньше длины переменных VARCHAR2 и RAW (32 767 байт). С учетом столь странного ограничения в программах PL/SQL лучше использовать переменные типа VARCHAR2 и RAW , а не типа LONG и LONG RAW.
Значения типов LONG и LONG RAW , извлекаемые из базы данных и содержащие более 32 767 байт данных, не могут присваиваться переменным типа VARCHAR2 и RAW . Это крайне неудобное ограничение, из-за которого типы LONG и LONG RAW лучше вообще не применять.
Эти типы официально считаются устаревшими и поддерживаются только для сохранения обратной совместимости кода. Компания Oracle не рекомендует ими пользоваться, и я с ней полностью согласен. В новых приложениях вместо них лучше использовать типы CLOB и BLOB . А для существующих приложений в документации Oracle SecureFiles and Large Objects Developer’s Guide приводятся рекомендации по преобразованию данных типа LONG в данные типа LOB .
Answers
Here's a function for converting a blob back into a file. You'll need to create an Oracle directory to use it.
create or replace procedure BlobToFile(pBlob blob, pOraDir varchar2, pFilename varchar2) is
vAmount binary_integer := 32767;
vPos integer := 1;
vFile := utl_file.fopen(pOraDir, pFilename, 'WB', 32767);
dbms_lob.read(pBlob, vAmount, vPos, vBuffer);
utl_file.put_raw(vFile, vBuffer, true);
vPos := vPos + vAmount;
when OTHERS then
if utl_file.is_open(vFile) then
Thanks Paulzip for your response.
PFB code I used.
DECLARE
IBLOB BLOB;
ILEN NUMBER;
L_BUFFER RAW(32767);
L_AMOUNT PLS_INTEGER := 32767;
L_POS INTEGER ;
i INTEGER :=1;
L_FILE UTL_FILE.FILE_TYPE;
cursor c1 is SELECT V_BLOB_COL FROM V_TABLE;
BEGIN
OPEN C1;
FETCH C1 INTO IBLOB ;
ILEN:=DBMS_LOB.GETLENGTH(IBLOB);
WHILE L_POS Loop
DBMS_LOB.READ( IBLOB, L_AMOUNT, L_POS, L_BUFFER);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
DBMS_OUTPUT.PUT_LINE('Cursor Closed');EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || ' SQLERRM=' || SQLERRM);
RAISE;
@ symbols and 99999999 at last.
Please let me know what changes to be done to my code to get correct data displayed in my file.
Thanks in advance.
How do you know that this isn't the content of the files?
How did you load them into the db?
Do you have an original source file to compare?
When we are using java code we are getting correct values .
So trying to get the data using PL/SQL.
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || ' SQLERRM=' || SQLERRM);
RAISE;
What a waste of code.
If you're going to handle an error, then handle it. Look at what Paul's done, he's said that if any error occurs then we must make sure that we close the file before raising the error up the stack. You do have one redeeming line: "raise;" so I won't give you zero marks! But if you had just left it with no exception handling then you would have the full error stack, you'd know which lines raised the error to begin with, debugging exceptions is made EASY.
Can you really read binary data? Does it make sense? What actual "data" exists in them? If you have character data then you have a clob - you can use the inbuilt procedure dbms_xslProcessor.clob2file to deal with that.
В ходе выполнения программы PL/SQL часто возникает необходимость преобразования данных из одного типа в другой. Преобразование может выполняться двумя способами:
- Неявно — поиск «оптимального варианта» поручается исполнительному ядру PL/SQL;
- Явно — преобразование выполняется вызовом функции или соответствующим оператором PL/SQL.
В этом разделе мы сначала разберемся, как в PL/SQL выполняются неявные преобразования, а затем перейдем к изучению функций и операторов явного преобразования.
Функция CHARTOROWID
Преобразует строку типа CHAR или VARCHAR2 в значение типа ROWID . Синтаксис функции:
Для успешного преобразования функцией CHARTOROWID строка должна состоять из 18 символов в формате:
где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке. Все четыре компонента задаются в формате Base64. Если исходная строка не соответствует этому формату, инициируется исключение VALUE_ERROR .
Функция CONVERT
Преобразует строку из одного набора символов в другой. Синтаксис функции:
Третий аргумент старый_набор_символов не является обязательным. Если он не задан, применяется набор символов, используемый в базе данных по умолчанию.
Функция CONVERT не переводит слова или фразы с одного языка на другой, а заменяет буквы или символы одного набора символов буквами или символами другого.
Неявное преобразование типов
Обнаружив необходимость преобразования, PL/SQL пытается привести значение к нужному типу. Вероятно, вас удивит, насколько часто это делается. На рис. 1 показано, какие виды неявного преобразования типов выполняются PL/SQL.
Рис. 1. Неявные преобразования типов, выполняемые PL/SQL
Неявное преобразование типов осуществляется при задании в операторе или выражении литерального значения в правильном внутреннем формате, которое PL/SQL преобразует по мере необходимости. В следующем примере PL/SQL преобразует литеральную строку «125» в числовое значение 125 и присваивает его числовой переменной:
Неявное преобразование типов выполняется также при передаче программе параметров не того формата, который в ней используется. В следующей процедуре таким параметром является дата. Вызывая эту процедуру, вы передаете ей строку в формате ДД-МММ-ГГ, которая автоматически преобразуется в дату:
Неявное преобразование строки в дату выполняется в соответствии со спецификацией NLS_DATE_FORMAT . Проблема заключается в том, что в случае изменения NLS_DATE_FORMAT работа программы будет нарушена.
Функция HEXTORAW
Преобразует шестнадцатеричную строку типа CHAR или VARCHAR2 в значение типа RAW . Синтаксис функции HEXTORAW :
Функция ROWIDTOCHAR
Преобразует двоичное значение типа ROWID в строку типа VARCHAR2 . Синтаксис функции ROWIDTOCHAR :
Возвращаемая функцией строка имеет следующий формат:
где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке PL/SQL. Все четыре компонента задаются в формате Base64. Пример:
Oracle и PL/SQL поддерживают несколько разновидностей типов данных, предназначенных специально для работы с большими объектами ( LOB, Large OBjects ). Такие объекты позволяют хранить огромные (от 8 до 128 терабайт) объемы двоичных (например, графических) или текстовых данных.
До выхода Oracle9i Release2 в объектах LOB можно было хранить до 4 Гбайт данных. Начиная с Oracle10g, ограничение было повышено до величины от 8 до 128 терабайт (конкретное значение зависит от размера блока вашей базы данных).
В PL/SQL можно объявлять большие объекты четырех типов:
- BFILE — двоичный файл. Переменная этого типа содержит локатор файла, указывающий на файл операционной системы вне базы данных. Oracle интерпретирует содержимое файла как двоичные данные.
- BLOB — большой двоичный объект. Переменная этого типа содержит локатор LOB , указывающий на большой двоичный объект, хранящийся в базе данных.
- CLOB — большой символьный объект. Переменная этого типа содержит локатор LOB , указывающий на хранящийся в базе данных большой блок текстовых данных в наборе символов базы данных.
- NCLOB — большой символьный объект с поддержкой символов национальных языков ( NLS ). Переменная этого типа содержит локатор LOB , указывающий на хранящийся в базе данных большой блок текстовых данных с национальным набором символов.
Большие объекты можно разделить на две категории: внутренние и внешние. Внутренние большие объекты (типы BLOB, CLOB и NCLOB ) хранятся в базе данных и могут участвовать в транзакциях на сервере базы данных. Внешние большие объекты (тип BFILE ) представляют двоичные данные, хранящиеся в файлах операционной системы вне таблиц базы данных. Они не могут участвовать в транзакциях, то есть вносимые в них изменения нельзя сохранить или отменить в зависимости от результата транзакции. Целостность данных обеспечивается только на уровне файловой системы. Кроме того, повторное чтение из BFILE может приводить к разным результатам — в отличие от внутренних больших объектов, соответствующих модели логической целостности чтения.
Читайте также: