Oracle сохранить clob в файл
Когда-то наиболее естественным способом загрузки структурированных данных в БД Oracle была загрузка при помощи SQL*Loader. Требовалось подготовить файл с параметрами, управляющими загрузкой, и вызвать утилиту sqlloader, передав ей управляющий файл и файл с данными.
Затем производители Oracle предоставили новый механизм для загрузки данных из внешних файлов - external tables. Нужно создать внешнюю таблицу командой DDL CREATE TABLE , указав внешний файл в качестве источника данных и описав его структуру. Средства для описания структуры очень похожи на средства описания структуры для SQL*Loader.
Если вам приходится часто загружать данные из структурированных текстовых файлов, то и SQL*Loader и внешние таблицы начинают казаться слишком громоздкими. Оба инструмента позволяют достаточно гибко описывать структуры загружаемых данных, определять проверки и преобразования данных в ходе загрузки. Это плюс. Минус в том, что для загрузки в БД данных из очередного внешнего файла нужно специально создавать для этого файла таблицу, будь то обычная таблица, в которую загрузит данные SQL*Loader, или внешняя таблица, связанная с внешним файлом при ее создании.
В этой статье я расскажу о еще одном подходе к загрузке данных из внешних csv-файлов (которым уже давно успешно пользуюсь).
Для того, чтобы данные из csv-файла стали доступны для манипуляций при помощи языка SQL, прежде всего, для запроса SELECT , нужно решить две задачи:
- прочитать содержимое внешнего файла,
- представить данные из файла в виде таблицы.
Прочитать содержимое внешнего файла можно при помощи пакета UTL_FILE . Альтернативно, можно загрузить внешний файл в CLOB при помощи пакета DBMS_LOB . Оба эти способа предполагают, что файл помещен в директорию на сервере, для которой в БД создан объект directory. Есть и третий способ - загрузить файл в БД через web-интерфейс при помощи PL/SQL Gateway, после чего содержимое файла становится доступным внутри БД как LOB.
Когда csv-файл загружен в БД и его содержимое доступно для PL/SQL, представить его данные в виде таблицы - это вопрос разбиения данных на строки, строк - на поля, и возврата строк из конвейерной (pipelined) функции.
Таким образом, нам нужна конвейерная функция, которая будет принимать в качестве параметра имя csv-файла и возвращать его содержимое в виде таблицы. Конвейерная функция возвращает таблицу, тип которой должен быть определен на уровне схемы данных, как и тип строки данной таблицы. Для универсальности, объектный тип, представляющий строку таблицы, будет содержать атрибуты типа VARCHAR2, где каждый атрибут будет представлять отдельное поле (столбец) таблицы.
Создам тип t_varchar_row для представления строки таблицы с 5-ю столбцами (при необходимости количество столбцов можно увеличить):
Значения типа t_varchar_row создаются с помощью конструктора:
Создам табличный тип t_varchar_row_table и проверю новые типы с помощью тестовой конвейерной функции:
Теперь, когда идея оформилась и ее реализуемость не вызывает сомнений, осталось собрать вместе код, загружающий данные из внешнего файла, и код для представления этих данных в виде таблицы t_varchar_row_table .
Сделаю это в функции csv :
Помимо указания файла и директории для загрзуки, параметры функции позволяют указать
- p_charset - кодировку csv-файла,
- p_sep - символ-разделитель полей csv-файла,
- p_newline - символ(ы) завершения строки csv-файла.
Для разбора содержимого файла, загруженного в CLOB переменную l_clob в функции используется SQL семантика для LOB, позволяющая работать с большим объектом (N)CLOB как со строкой VARCHAR2. (Об SQL семантике для LOB я упоминал в статье О работе с большими объектами в СУБД Oracle.)
Для тестирования создам файл Hemingway.csv со списком романов Хэмингуэя и годами публикации:
Помещу файл в директорию файловой системы, доступную через объект directory FILES_DIR :
Если у вас нет подходящего объекта directory, создать его можно командой:
Получу данные из файла Hemingway.csv с помощью SELECT и функции csv :
Обратите внимание, что в последнем случае, когда SELECT выбирает не все строки, освобождение временного CLOB происходит в обработчике исключения NO_DATA_NEEDED функции csv .
Ещё один пример. Скопирую в каталог /home/oracle/files файл со словами английского языка, имеющийся во многих ОС семейстав Unix. Каждая строка файла содержит 1 слово, что позволяет рассматривать этот файл как как csv-файл с одним полем в строке:
Как видим, в файле 479623 слова. Получу его содержимое с помощью функции csv :
На последний запрос понадобилось больше 80 секунд!
Очевидно, что SQL семантика для LOB - не самый эффективный способ работы с содержанием больших объектов. Ведь при выполнении функций, перегруженных для типа (N)CLOB, неявно создаются временные (N)CLOB'ы.
Создам оптимизированную по быстродействию функцию csv2 , переписав фрагмент, ответственный за выделение строк из CLOB'а:
Проверим, насколько новая версия эффективней прежней:
Что ж, выбор из двух версий очевиден.
Отмечу ограничение функции сsv2 : строки в csv-файле не могут быть слишком длинными (максимальная длина строки зависит от кодировки, но не длиннее 32767). Это ограничение можно снять, отказавшись от предварительного выделения строк из содержимого СLOB. Кроме того, функция не допускает символов завершения строки и символов-разделителей в полях файла csv, так как не предусматривает их экранирования с помощью \ (как принято в Unix) или заключения значений полей в кавычки (как принято у Microsoft). При желании, можно реализовать тот и другой вариант, переработав фрагмент функции csv2 , отвечающий за разбор содержания CLOB.
В заключение, удалю демонстрационные функции и типы:
Продемонстрированный подход к загрузке данных из csv-файлов имеет преимущество перед SQL*Loader и external tables, не требуя создания таблиц специально для загрузки данных.
I am using Oracle SQL Developer and trying to export a table to a CSV file. Some of the fields are CLOB fields, and in many cases the entries are truncated when the export happens. I'm looking for a way to get the whole thing out, as my end goal is to not use Oracle here (I received an Oracle dump - which was loaded into an oracle db, but am using the data in another format so going via CSV as an intermediary).
If there are multiple solutions to this, given that it is a one time procedure for me, I don't mind the more hack-ish type solutions to more involved "do it right" solutions.
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.
This pl/sql code should work in oracle 11g. It dumps the text of the clobs into a directory with the title as filename.
If DBMS_XSLPROCESSOR isn't available then you could replace DBMS_XSLPROCESSOR.clob2file with a procedure that uses UTL_FILE.
Or perhaps replace DBMS_XSLPROCESSOR.clob2file with dbms_advisor.create_file.
@MatthewMoisen From what I understand, UTL_FILE.FFLUSH flushes the pending data identified by the file handle into the file. It's useful to use inside the loop if you want to read the file while it is still open. For example, to tail debugging/logging files while they are being written to. But if you just want to read the file after it's written, outside the loop is good enough.
@MatthewMoisen It's as you suspect. At that point something went wrong, so I didn't want FFLUSH to potentially raise a new less meaningfull exception. There was a problem with dumping the file, so why care about shaking that last spunk from the filebuffer?
Are you trying to generate files on the database server file system? Or on the client file system?
If you are trying to generate files on the database server file system, there is an example of exporting a CLOB to a file in another StackOverflow thread that is based on Tim Hall's LOB export examples (Tim's site appears to be down at the moment).
If you're trying to generate files on the client file system, it would involve much more complex SQLPlus scripting. You'd be looking at doing something like querying the table and using the data to dynamically generate one SQLPlus script per file that you wanted to generate and then dynamically calling those scripts. You'd be really pushing SQL*Plus's scripting capabilities so that's not an architecture that I would generally advocate but I believe it could be done.
If you do need to generate files on the client file system, I'd generally prefer to use something other than SQLPlus. For example, there is an example of a small Java class that reads and writes CLOB and BLOB data to and from files on the AskTom site. I'd tend to write a small Java utility that ran on the client and exported the data rather than trying to put too much logic in SQLPlus scripts.
Для работы с данными большого объема СУБД 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-сегментах данные, свести к минимуму их дублирование, шифровать эти данные. Но эта тема выходит за рамки данного очерка.
5 Answers 5
if you have access to the file system on your database box you could do something like this:
Which I copied and pasted from this site.
You may also find this previous question about UTL_FILE useful. It addresses exporting to CSV. I have no idea or experience with how UTL_FILE handles CLOBs, however.
UTL_FILE can handle CLOB pretty well, in the 9i and greater days, it's just like handling a VARCHAR2.
Hmm, I don't have access to that machine, but I can probably scare up someone who does if I can't do it directly.
You can use a Python script to take care of the export, the CLOBs won't get truncated:
assuming by an Oracle dump you meant a .dmp (either from export or expdp), you're looking at a binary file. You'll need to import the dumpfile into an Oracle database and then export the data to plain text using UTL_FILE or other means.
It is already loaded into an oracle db, it is just that my end goal isn't oracle, so i'm pulling out the bits that I want as CSV and dealing with them that way. This has been working until I ran into the CLOBs
Here is a short yet general python script that does just this - dumping tables (with CLOB fields, among the rest) to a flat csv file: OraDump
As Oracle SQL Developer is explicitly mentioned and "hack-ish type solutions" were encouraged (and in case somebody is still in need…):
If a single statement is executed from a SQL Worksheet (in Oracle SQL Developer), the result is displayed as a table. After clicking the result table, use either keyboard shortcuts or the Edit menu to first mark the whole table and then to copy its content. Proceed to your text editor of choice. Paste. Save to a file. Hopefully done. ;-)
Works even for CLOBs exceeding 4000 characters. Whether or not it actually helps, will very much depend on the CLOBs' actual content. Sometimes some SQL pre-processing might get you there…
Alternatively try in the result table's local menu Export…. Going from there through the Excel 95-2003 format option might work (better than CSV or text/TSV).
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
Читайте также: