Oracle сохранить clob в файл

Обновлено: 03.07.2024

Для работы с данными большого объема СУБД Oracle предоставляет типы данных BLOB, CLOB, NCLOB и BFILE. Здесь LOB означает large object, или большой объект, и далее по тексту термины LOB и "большой объект" взаимозаменяемы. По сути, большой объект - это абстрактный тип для манипуляции данными большого объема внутри БД, а типы BLOB, CLOB, NCLOB и BFILE - его конкретные реализации.

Указанные типы данных можно использовать в СУБД Oracle для определения столбцов таблиц, атрибутов объектных типов и переменных PL/SQL.

Вот краткая характеристика этих типов:

BFILE (от binary file) - данные хранятся во внешнем по отношению к БД файле, а значение типа BFILE содержит указатель на файл; данные считаются двоичными.
BLOB (от binary large object) - данные хранятся в базе данных в отдельном сегменте * , а значение типа BLOB содержит указатель на них (LOB locator); данные считаются двоичными.
CLOB (от character large object) - данные хранятся в базе данных в отдельном сегменте * , а значение типа CLOB содержит указатель на них (LOB locator); данные интерпретируются как текст в кодировке базы данных (database character set).
NCLOB (от national character large object) - данные хранятся в базе данных в отдельном сегменте * , а значение типа CLOB содержит указатель на них (LOB locator); данные интерпретируются как текст в национальной кодировке (national character set)

* По умолчанию LOB'ы размером до 4000 байт хранятся непосредственно в строках таблицы (в табличном сегменте), а LOB'ы большего размера - в отдельном сегменте (возможно, в отдельном табличном пространстве). Это поведение регулируется опцией ENABLE|DISABLE STORAGE IN ROW команд CREATE TABLE и ALTER TABLE .

Итак, по месту хранения LOB'ы делятся на

внутренние (BLOB, CLOB, NCLOB), данные которых хранятся в БД, и
внешние (BFILE), данные которых хранятся в файлах операционной системы,

а по содержанию на

двоичные (BFILE и BLOB), для хранения данных в двоичных форматах, например, MP3, JPG, объектный код программ, и
текстовые (CLOB и NCLOB), для хранения данных в текстовых форматах, таких как XML, HTML, JSON, обычный текст.

Oracle 11g, согласно документации, работает с внутренними LOB'ами размером до 2 32 -1 байт и с BFILE файлами размером до 2 64 -1 байт.

Для работы с LOB'ами cоздам таблицу со столбцами соответствующих типов:

Вместе с таблицей были созданы сегменты для хранения больших объектов:

Для столбца типа BFILE отдельный сегмент не создан - ведь данные этого типа хранятся во внешних файлах.

Значение типа LOB может быть

NULL - неинициализировано, не содержит указателя на LOB,
пустым (empty) - указатель на LOB указывает в никуда,
непустым - указатель на LOB указывает на данные LOB'а.

Пустые LOB значения создаются функциями EMPTY_CLOB и EMPTY_BLOB :

Последний запрос демонстрирует два способа проверить, является ли LOB пустым. Запрос использует пакет DBMS_LOB , содержащий процедуры и функции для работы с LOB'ами.

Начиная с версии Oracle 9i в SQL и PL/SQL поддерживается неявная конвертация между (N)CLOB и VARCHAR2, что позволяет манипулировать значениями в (N)CLOB столбцах и переменных так, как будто это значения типа VARCHAR2:

Как видим, функции и операторы, работающие с VARCHAR2, перегружены для типа (N)CLOB! При этом преодолеваются ограничения в 4000 символов, свойственные SQL типу VARCHAR2:

А вот операторы сравнения для (N)CLOB работают только в PL/SQL и не работают в SQL:

Выше я воспользовался функциями TO_NCLOB и TO_CLOB для явной конвертации значений VARCHAR2 в значения (N)CLOB. В следующей таблице представлены все функции для конвертации в LOB типы и обратно:

Функция	Где работает
TO_CLOB(character_data)	SQL и PL/SQL
TO_BLOB(raw_data)	SQL и PL/SQL
TO_LOB(long_data)	SQL and PL/SQL
TO_NCLOB(character_data)	SQL и PL/SQL
TO_RAW(blob_data)	только PL/SQL

Как видим, функция TO_RAW недоступна в SQL и, отсюда, возможности конвертации между BLOB и RAW в SQL ограничены. Например:

Зато в PL/SQL работают явная и неявная конвертации между BLOB и RAW:

Рассмотренные возможности по работе со значениями LOB как с VARCHAR2 получили название SQL семантика для LOB'ов (SQL semаntics for LOBs). С их использованием связаны некоторые ограничения, как мы увидим ниже.

С точки зрения PL/SQL большие объекты делятся на:

временные (temporary), время жизни которых не превышает сеанса работы с СУБД,
постоянные (persistent), которые хранятся в базе данных или во внешнем файле.

создаются либо с помощью DBMS_LOB.CREATETEMPORARY , либо простым присваиванием значения LOB переменной в PL/SQL коде,
располагаются на диске во временном табличном пространстве (temporary tablespace),
могут быть проверены с помощью DBMS_LOB.ISTEMPORARY ,
освобождаются с помощью DBMS_LOB.FREETEMPORARY , что приводит к инвалидированию указателя на LOB,
в отличие от постоянных, изменяются без создания записей в журнале БД (logging) и не контролируются транзакциями,
могут быть скопированы в постоянные LOB'ы c помощью DBMS_LOB.COPY .

В вышеприведенных примерах с PL/SQL мы имели дело с временными LOB'ами.

Для работы с постоянными LOB'ами в PL/SQL нужно сначала получить указатель на LOB, а затем с его помощью извлекать или изменять данные, используя пакет DBMS_LOB . Следующий пример демонстрирует получение постоянного LOB'а и его потерю(!) при попытке изменить его значение простым присваиванием:

Дело в том, что SQL семантика для LOB'ов всегда порождает временные LOB'ы - это и есть то ограничение, о котором я упоминал выше. Неявное приведение VARCHAR2 к LOB (строка 7) или функция, перегруженная для (N)CLOB (строка 14), дают нам временные LOB'ы. Как только переменной PL/SQL, указывающей на постоянный LOB, присваивается временный LOB, переменная начинает указывать на временный LOB. А связь переменной с постоянным LOB'ом утрачивается.

Значение временного LOB'а можно сохранить в БД - и тем самым сделать постоянным - либо с помощью SQL либо, как уже упоминалось, с помощью DBMS_LOB.COPY . Продемонстрирую обе возможности:

Обратите внимание, что процедура DBMS_LOB.COPY заменила в постоянном NCLOB c3 только фрагмент, равный по размеру значению исходного NCLOB'а c2 . Как вариант, можно было перед копированием очистить LOB назначения с помощью DBMS_LOB.ERASE .

Изменения внутренних постоянных LOB'ов (в отличие от внешних или временных) в СУБД Oracle подчиняются транзакциям. Убедимся в этом, отменив только что сделанные изменения:

Типичный алгоритм для чтения или изменения постоянного LOB'а с помощью PL/SQL таков:

Извлечь указатель на LOB из столбца таблицы с помощью SELECT .
Открыть большой объект с помощью DBMS_LOB.OPEN .
Получить оптимальный размер фрагмента для чтения (записи) LOB с помощью DBMS_LOB.GETCHUNKSIZE
Получить размер LOB'а в байтах (для BLOB и BFILE) или символах (для CLOB и NCLOB) с помощью DBMS_LOB.GETLENGTH .
Многократно вызывать DBMS_LOB.READ для чтения последовательных фрагментов LOB'а, пока не будут извлечены все данные
ИЛИ
многократно вызывать DBMS_LOB.WRITE , со смещением, или DBMS_LOB.WRITEAPPEND или иные процедуры DBMS_LOB для записи фрагментов данных.
Закрыть LOB с помощью DBMS_LOB.CLOSE .

В предыдущем примере с DBMS_LOB.COPY я не открывал и не закрывал постоянный LOB при помощи DBMS_LOB.OPEN и DBMS_LOB.CLOSE , однако, это стоит делать для улучшения производительности при изменениях больших объектов.

Приведу пример выгрузки данных из постоянного CLOB'а во внешний файл. Для доступа к внешним файлам потребуется создать директорию, например:

Следующий код выгружает содержимое столбца lobs_tab.clob_col в файл clob_col1.txt , используя пакет DBMS_LOB для чтения CLOB и пакет UTL_FILE для записи во внешний файл:

Альтернативно, можно выгрузить CLOB во внешний файл, пользуясь SQL семантикой для LOB и не прибегая к DBMS_LOB :

Для обратной операции - загрузки содержимого файла в LOB - также можно воспользоваться пакетами UTL_FILE и DBMS_LOB , циклически читая данные из файла и записывая в LOB. Но интереснее сделать это с помощью типа данных BFILE.

Тип данных BFILE содержит указатель на внешний файл, который

состоит из двух частей: имя директории и имя файла,
создается с помощью функции BFILENAME , например, BFILENMAE('FILES_DIR', 'novel.txt') ,
может указывать на несуществующий файл.

Пакет DBMS_LOB позволяет читать содержимое BFILE, но не изменять его. Чтение из BFILE возвращает двоичные данные как тип данных RAW. Для преобразования в VARCHAR2, при необходимости, используется функция UTL_RAW.CAST_TO_VARCHAR2 .

Пример чтения BFILE и записи во временный BLOB:

В примере BFILE открывается и закрывается с помощью OPEN и CLOSE , аналогично внутренним LOB'ам. Также, пакет DBMS_LOB содержит несколько процедур и функций специально для работы с объектами BFILE:

Процедура / Функция	Что делает
FILEGETNAME	возвращает имя директории и файла BFILE
FILEEXISTS	проверяет, что файл BFILE существует
FILEOPEN	открывает файл BFILE
FILEISOPEN	проверяет, что файл BFILE открыт
FILECLOSE	закрывает файл BFILE
FILECLOSEALL	закрывает все открытые в сеансе файлы BFILE

Вместо чтения BFILE по частям пакет DBMS_LOB позволяет

с помощью LOADCLOBFROMFILE загрузить содержимое BFILE в CLOB, указав, какую кодировку (набор символов) имеет содержимое,
с помощью LOADBLOBFROMFILE загрузить содержимое BFILE в BLOB.

Пример загрузки текстового файла во временный CLOB (аналогично можно загрузить и в постоянный CLOB):

Значения src_offset и dest_offset отличаются, поскольку первое, для BFILE, выражено в байтах, а второе, для CLOB, выражено в символах. В файле и CLOB'е имеются девять двухбайтовых русских букв - напомню, их содержимое начинается с " привет, мир ".

Приведу неполный список процедур и функций DBMS_LOB для чтения, анализа и изменения значений BLOB, CLOB и NCLOB:

Процедура / Функция	Что делает
APPEND	добавляет один LOB в конец другого
COPY	копирует все или часть содержимого LOB'а в другой LOB
ERASE	удаляет все или часть содержимого LOB'а
GETLENGTH	возвращает размер LOB'а
INSTR	ищет "подстроку" в LOB'е
ISOPEN	проверяет, открыт ли LOB
ISTEMPORARY	проверяет, временный ли LOB
READ	читает данные LOB'а
SUBSTR	получает "подстроку" из LOB'а
TRIM	сокращает размер LOB'а до указанного
WRITE	записывает данные в LOB
WRITEAPPEND	записывает данные в конец LOB'а

Следующий эксперимент покажет разницу между внутренними и внешними постоянными LOB'ами. Помещу в поле bfile_col таблицы lobs_tab объект BFILE и скопирую единственную строку таблицы во вторую строку:

Команда INSERT привела к тому, что значения bfile_col в обеих строках связаны с одним и тем же внешним файлом, и его изменение отразится на обоих значениях.

А вот значения столбцов clob_col , nclob_col и blob_col для строк 1 и 2 стали независимы - не только указатели на LOB, но и данные внутренних LOB'ов в LOB-сегментах были скопированы. Продемонстрирую их независимость, изменив значения clob_col и nclob_col для строки 2:

Аналогично, при присваивании BLOB и (N)CLOB переменных в PL/SQL мы получаем независимые копии LOB'ов:

Итак, мы на примерах рассмотрели работу с большими объектами в SQL и PL/SQL. Работа с большими объектами имеет и другой аспект - это технология SecureFiles, позволяющая, в частности, сжимать хранимые в LOB-сегментах данные, свести к минимуму их дублирование, шифровать эти данные. Но эта тема выходит за рамки данного очерка.

my student_t1 table consists of a blob column called image. I wrote a base64 function that correctly converts the BLOB into a CLOB. Now, I need to write this CLOB to my IO_DIR on my server /u01/app/oracle/io_dir .

When the following PL/SQL code is executed, the CLOB gets written to the directory as a text file. I believe the entire CLOB is not outputted though because when I try to decode the image is corrupted (only the upper portion of the image comes in). I ran it on a small image (11k) and it worked fine. I also monitored that l_pos is increasing correctly so it seems like it is reading each chunk of the CLOB (32kB) What am I doing wrong?

I am using Oracle 11g Express Edition (XE) and SQL Developer. Here is the error and the code

42.1k 9 9 gold badges 22 22 silver badges 48 48 bronze badges 51 1 1 gold badge 1 1 silver badge 5 5 bronze badges

3 Answers 3

Try using a smaller buffer size than 32767, in particular, one with size no more than 8191.

As far as I can tell, DBMS_LOB.SUBSTR does not always behave itself if the buffer size exceeds 8191 characters. If you ask for it to read (say), 10000 characters, it might only read 8191 from the LOB, but it will return to you a 10000-character long string where the first 8191 characters were what it read from the LOB and the other 1809 were what happened to be there before (e.g. from the last call to DBMS_LOB.SUBSTR ). If the buffer size is 8191 characters or smaller, this problem doesn't occur and DBMS_LOB.SUBSTR returns the number of characters you asked for.

I cannot fathom why this function would do this. It seems very odd. I can only conclude that it is a bug in the database. Whether it is specific to Oracle 11g, or XE, I can't say.

The Oracle documentation for DBMS_LOB.SUBSTR includes the following under Usage Notes, which doesn't explain the problem but does include a mention of the number 8191:

DBMS_LOB.SUBSTR will return 8191 or more characters based on the characters stored in the LOBs. If all characters are not returned as a consequence of the character byte size exceeding the available buffer, the user should either call DBMS_LOB.SUBSTR with a new offset to read the remaining characters, or call the subprogram on loop until all the data is extracted.

Finally, my copy of Oracle 11g XE is using a single-byte character set. The number 8191 may change if you are using a multi-byte character set.

Я использую Oracle SQL Developer и пытаюсь экспортировать таблицу в файл CSV. Некоторые из полей являются полями CLOB, и во многих случаях записи усекаются при экспорте. Я ищу способ получить все это, так как моя конечная цель - не использовать здесь Oracle (я получил дамп Oracle, который был загружен в базу данных Oracle, но я использую данные в другом формате, поэтому перехожу через CSV в качестве посредника).

Если есть несколько решений для этого, учитывая, что это одноразовая процедура для меня, я не возражаю против более хакерских решений для более сложных решений «делай это правильно».

Если у вас есть доступ к файловой системе в вашей базе данных, вы можете сделать что-то вроде этого:

Я скопировал и вставил с этого сайта.

Вы можете использовать скрипт Python, чтобы позаботиться об экспорте, CLOB не будут усечены:

Предполагая, что под дампом Oracle вы имели в виду .dmp (либо из экспорта, либо из expdp), вы смотрите на двоичный файл. Вам нужно будет импортировать файл дампа в базу данных Oracle, а затем экспортировать данные в обычный текст, используя UTL_FILE или другие средства.

Вот короткий, но общий скрипт на Python, который делает именно это - выгружает таблицы (среди прочего, с полями CLOB) в плоский CSV-файл: OraDump

Поскольку Oracle SQL Developer явно упоминается и поощряются «решения хакерского типа» (и в случае, если кто-то все еще нуждается…):

Если один оператор выполняется из таблицы SQL (в Oracle SQL Developer), результат отображается в виде таблицы. Нажав на таблицу результатов, используйте сочетания клавиш или меню Правка , чтобы сначала отметить всю таблицу, а затем скопировать ее содержимое. Перейдите к выбранному текстовому редактору. Вставить. Сохранить в файл. Надеюсь, готово. ;-)

Работает даже для CLOB, превышающих 4000 символов. Помогает это на самом деле или нет, во многом зависит от фактического содержания CLOB. Иногда предварительная обработка SQL может помочь вам .

Или попробуйте в локальном меню таблицы результатов Экспорт… . Переход оттуда через параметр формата Excel 95-2003 может работать (лучше, чем CSV или текст / TSV).

Пожалуйста, прокомментируйте, если и как это требует корректировки / более подробной информации.

Мне нужно написать процедуру plsql, чтобы получить этот кусок и разделить его так, чтобы у меня был массив из трех элементов [aaaaaa, cccccccc, bbbbbbb].

Есть ли возможные решения?

Вот фрагмент кода, который работает. Я предлагаю вам использовать явные курсоры вместо неявных (FOR i IN (select . )) для повышения производительности.

Во-первых, это сценарий для создания тестового примера.

Тогда вот сценарий для построчного чтения Clob:

Переменная amount используется для определения позиции конца строки. Будьте осторожны, в некоторых случаях конец строки - это CHR (10) || CHR (13) (CR + LF), а в некоторых других случаях это только CHR (10).

Конвейерная функция с некоторыми дополнительными параметрами для управления поведением. Протестировано / работает в Windows, Oracle 11g (у меня есть некоторые подозрения, что он может не работать в средах * nix из-за того, как заканчиваются строки).

Я создал таблицу под названием lixo_mq :

Я скопировал процедуру распечатки и изменил ее, чтобы она работала иначе:

Это элегантное решение, которое отлично работает с clobs, превышающим 32767 символов, или строками, превышающими 4K символов.

Стандартный запрос ANSI:

Специфический запрос Oracle (исходный пост):

Пустые строки вызвали ошибки
Новые строки там, где еще находятся в выводе на печать (может быть желательно)
«amount: =» не было внутри цикла while, я думаю, это вызывало большую ошибку, если какое-либо значение было короче первой строки в CLOB.

Я реализовал это как довольно быстрые и грязные исправления. Я уверен, что должны быть более элегантные решения . Вот и все. Примерная таблица:

И измененный код:

В случае . - у вас установлен APEX - и размер clob меньше 32 КБ, вы также можете изучить следующий код:

Хотя подход SQL regexp / connect by level, вероятно, является наиболее элегантным, он довольно плох с точки зрения производительности (для моего тестового примера на 11.2.0.3.0). Гораздо быстрее такой простой синтаксический анализ.

Читайте также: