Работа с большими объектами в PL/SQL (BFILE, LOB, SecureFiles)

Тема работы с большими объектами весьма объемна, поэтому мы не сможем рассмотреть все ее аспекты. Данную статью блога следует рассматривать как введение в программирование больших объектов для разработчиков PL/SQL. Мы познакомимся с некоторыми нюансами, которые необходимо учитывать, и рассмотрим примеры важнейших операций. Хочется надеяться, что представленный материал станет хорошей основой для ваших дальнейших исследований в этой области.

Прежде чем переходить к основному материалу, необходимо указать, что все примеры данного раздела основаны на следующем определении таблицы (см. файл source_code.sql): 

TABLE waterfalls (
   falls_name VARCHAR2(80),
   falls_photo BLOB,
   falls_directions CLOB,
   falls_description NCLOB,
   falls_web_page BFILE)

Рис. 1. Водопад Драйер-Хоуз возле Мунисинга (штат Мичиган)

Таблица содержит информацию о водопадах, расположенных в северной части штата Мичиган. На рис. 1 изображен водопад Драйер-Хоуз возле Мунисинга; в замерзшем состоянии его часто посещают альпинисты.

Каждый столбец таблицы предназначен для хранения больших объектов одного из четырех типов. Фотографии содержат большие объемы двоичных данных, поэтому для столбца falls_photo column определен тип BLOB. Столбцы с информацией о местоположении и с описанием водопадов (falls_directions и falls_descriptions) содержат текст, поэтому они определены соответственно с типами CLOB и NCLOB. В реальной таблице они имели бы один и тот же тип, но мы хотели продемонстрировать в своем примере все разновидности больших объектов. Наконец, копия веб-страницы каждого водопада хранится в HTML-файле вне базы данных; в таблицу включен столбец типа BFILE, содержащий указатели на эти файлы. Эти столбцы будут использоваться в последующих примерах для демонстрации различных аспектов работы с данными LOB в программах PL/SQL.

При описании больших объектов мы будем часто использовать сокращение LOB для обозначения всех описываемых типов данных — CLOB, BLOB, NCLOB и BFILE. При обсуждении конкретного типа будет приводиться его полное название.

Понятие локатора LOB

В работе с LOB важнейшая роль отводится понятию локатора (locator) LOB. Локатором называется хранящийся в базе данных указатель на данные большого объекта. Давайте посмотрим, что же происходит при выборке данных из столбца типа BLOB в переменную PL/SQL того же типа:

DECLARE
   photo BLOB;
BEGIN
   SELECT falls_photo
   INTO photo
   FROM waterfalls
   WHERE falls_name='Dryer Hose';

Какое значение будет содержать переменная photo после выполнения команды SELECT? Графические данные изображения? Нет, в переменной будет храниться только указатель на двоичные данные. Результат выполнения команды SELECT показан на рис. 2.

Рис. 2. Локатор LOB указывает на объект в базе данных

Этот механизм в корне отличается от того, как работают другие типы данных. Переменные и значения в столбцах LOB содержат локаторы больших объектов, которые идентифицируют реальные данные, хранящиеся в другом месте базы данных или вне ее. Для работы с данными типа LOB нужно сначала извлечь локатор, а затем с помощью встроенного пакета DBMS_LOB получить и/или модифицировать реальные данные. Так, для получения двоичных данных фотографии, локатор которой хранится в столбце BLOB из приведенной ранее таблицы, необходимо выполнить следующие действия:

1. Выполнить команду SELECT и выбрать из базы данных LOB-локатор фотографии.
2. Открыть объект LOB с помощью функции DBMS_LOB.OPEN.
3. Вызвать функцию DBMS_LOB.GETCHUNKSIZE для получения оптимального (с точки зрения затрат на выполнение операции чтения или записи) размера фрагмента данных типа LOB.
4. Вызвать функцию DBMS_LOB.GETLENGTH для определения количества байтов или символов объекта LOB.
5. Несколько раз вызвать функцию DBMS_LOB.GETLENGTH для считывания данных типа LOB.
6. Закрыть объект LOB.

Не все эти действия являются обязательными; не огорчайтесь, если что-то пока остается непонятным. Далее все эти операции будут описаны более подробно.

На первый взгляд кажется, что локаторы значительно усложняют схему работы с большими объектами. Однако предлагаемый Oracle подход имеет существенные преимущества, потому что он избавляет от необходимости возвращать все данные объекта LOB при каждой выборке строки из таблицы. Представьте, сколько времени потребовалось бы для передачи 128 терабайт данных LOB. Представьте, насколько это неэффективно, если вам нужна лишь небольшая часть этих данных. В механизме Oracle производится выборка локатора (быстрая операция), после чего читаются только те данные LOB, которые вам нужны. Кроме того, объекты LOB по умолчанию не кэшируются в буферном кэше, и для них не генерируются данные отмены (хотя генерируются данные повторного выполнения, если не установить параметр NOLOGGING). Таким образом, загрузка 50 гигабайт данных LOB не приведет к переполнению буферного кэша или таблиц отмены с общим снижением быстродействия. Раздельное кэширование и управление отменой LOB было дополнительно усовершенствовано в Oracle11g... впрочем, об этом позднее.

LOB-ДАННЫЕ В ДОКУМЕНТАЦИИ ORACLE

Если вам часто приходится работать с большими объектами, настоятельно рекомендуем ознакомиться со следующими документами Oracle:

• SecureFiles and Large Objects Developer’s Guide — руководство по программированию с использованием LOB для Oracle11g и выше.
• Application Developer’s Guide — Large Objects — руководство по программированию с использованием LOB для Oracle10g и более ранних версий.
• PL/SQL Packages and Types Reference — глава с описанием пакета DBMS_LOB.
• SQL Reference — раздел «Datatypes» главы 2 содержит важную информацию о LOB-объектах.

Это далеко не полный список документации об объектах LOB, но в этих документах можно найти все наиболее важные сведения.

Большие объекты — пустые и равные NULL

Теперь, когда вы понимаете различие между локатором LOB и значением, на которое он ссылается, мы введем еще одно важное понятие: пустой объект LOB. Так называется локатор, не указывающий ни на какие данные. Это не то же самое, что значение NULL, то есть LOB-столбец (или переменная), не содержащий локатора LOB. Рассмотрим пример: 

DECLARE
   directions CLOB;
BEGIN
   IF directions IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('directions is NULL');
   ELSE
      DBMS_OUTPUT.PUT_LINE('directions is not NULL');
   END IF;
END;
directions is NULL

Объявленная нами переменная CLOB содержит NULL, потому что ей пока не присвоено никакое значение. Выглядит все привычно, не правда ли? Да и происходит то же самое, что при работе с данными любого другого типа: переменная объявлена без присваивания значения, и ее проверка на NULL конструкцией IS NULL дает результат TRUE. В этом отношении данные LOB напоминают объекты: перед добавлением данных они тоже должны быть инициализированы.

Давайте сделаем следующий шаг и инициализируем LOB. В следующем коде вызов EMPTY_CLOB инициализирует (но еще не заполняет!) переменную LOB. Сначала программный код:

DECLARE
   directions CLOB;
BEGIN
   IF directions IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('Сначала directions is NULL');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Сначала directions is not NULL');
   END IF;
   DBMS_OUTPUT.PUT_LINE('Длина = '
      || DBMS_LOB.GETLENGTH(directions));
   -- инициализация переменной LOB
   directions := EMPTY_CLOB();
   IF directions IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('После инициализации directions is NULL');
   ELSE
      DBMS_OUTPUT.PUT_LINE('После инициализации directions is not NULL');
   END IF;
   DBMS_OUTPUT.PUT_LINE('Длина = '
      || DBMS_LOB.GETLENGTH(directions));
END;

Результат:

Сначала directions is NULL
Длина =
После инициализации directions is not NULL
Длина = 0 

Мы видим, что переменная CLOB сначала представляет собой атомарное значение NULL. Не удивительно, что длина NULL-LOB тоже равна NULL. После инициализации переменной типа CLOB встроенной функцией EMPTY_CLOB переменная уже не равна NULL, потому что она содержит значение: локатор. Функция DBMS_LOB.GETLENGTH показывает, что даже инициализированная (NOT NULL) переменная CLOB остается пустой.

Очень важно понимать это различие, потому что у типов LOB способ проверки наличия либо отсутствия данных получается более сложным, чем у скалярных типов. Для традиционных скалярных типов достаточно простой проверки IS NULL:

IF some_number IS NULL THEN
-- Значит, данных нет

Если проверка IS NULL для NUMBER или VARCHAR2 (или любого другого скалярного типа) возвращает TRUE, мы знаем, что переменная не содержит данных. Но при работе с LOB необходимо проверить не только равенство NULL (отсутствие локатора), но и длину:

IF some_clob IS NULL THEN
-- Данных нет
ELSIF DBMS_LOB.GETLENGTH(some_clob) = 0 THEN
-- Данных нет
ELSE
-- И только теперь данные присутствуют
END IF;

Итак, проверять нужно два условия вместо одного.

Для создания пустого объекта BLOB используется функция EMPTY_BLOB(). Для типов CLOB и NCLOB используется функция EMPTY_CLOB().

Запись данных в объекты LOB

При наличии локатора данные записываются в LOB-объект с помощью одной из процедур пакета DBMS_LOB:

• DBMS_LOB.WRITE — запись в произвольную позицию.
• DBMS_LOB.WRITEAPPEND — присоединение данных в конец LOB.

В приведенном ниже коде сначала создается локатор LOB для столбца directions таблицы waterfalls, а затем функция DBMS_LOB.WRITE записывает данные в CLOB-столбец строки «Munising Falls». Функция DBMS_LOB.WRITEAPPEND завершает дело:

/* Файл в Сети: munising_falls_01.sql */
DECLARE
   directions CLOB;
   amount BINARY_INTEGER;
   offset INTEGER;
   first_direction VARCHAR2(100);
   more_directions VARCHAR2(500);
BEGIN
   -- Удаление всех существующих строк со значением 'Munising Falls',
   -- чтобы этот пример мог выполняться многократно.
   DELETE
   FROM waterfalls
   WHERE falls_name='Munising Falls';
   -- Вставка новой строки и вызов функции EMPTY_CLOB() для создания локатора LOB
   INSERT INTO waterfalls
      (falls_name,falls_directions)
   VALUES ('Munising Falls',EMPTY_CLOB());
   -- Получение локатора LOB, созданного предыдущей инструкцией INSERT
   SELECT falls_directions
   INTO directions
   FROM waterfalls
   WHERE falls_name='Munising Falls';
   -- Открытие объекта LOB; делать это необязательно, но лучше аккуратно
   -- открывать и закрывать LOB-объекты
   DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);
   -- Вызов DBMS_LOB.WRITE начинает запись.
   first_direction := 'Follow I-75 across the Mackinac Bridge.';
   amount := LENGTH(first_direction); -- количество записываемых символов
   offset := 1; -- запись ведется с первого символа CLOB
   DBMS_LOB.WRITE(directions, amount, offset, first_direction);
   -- Вызов DBMS_LOB.WRITEAPPEND записывает дополнительные данные
   more_directions := ' Take US-2 west from St. Ignace to Blaney Park.'
      || ' Turn north on M-77 and drive to Seney.'
      || ' From Seney, take M-28 west to Munising.';
   DBMS_LOB.WRITEAPPEND(directions,
   LENGTH(more_directions), more_directions);
   -- И еще блок дополнительных данных
   more_directions := ' In front of the paper mill, turn right on H-58.'
   || ' Follow H-58 to Washington Street. Veer left onto'
   || ' Washington Street. You''ll find the Munising'
   || ' Falls visitor center across from the hospital at'
   || ' the point where Washington Street becomes'
   || ' Sand Point Road.';
   DBMS_LOB.WRITEAPPEND(directions,
   LENGTH(more_directions), more_directions);
   -- Закрываем объект LOB - работа выполнена.
   DBMS_LOB.CLOSE(directions);
END;

В этом примере используются обе процедуры, WRITE и WRITEAPPEND. Впрочем, это сделано лишь в учебных целях — с таким же успехом можно было ограничиться WRITEAPPEND, по-скольку объект изначально не содержит никаких данных. Обратите внимание на открытие и закрытие объекта CLOB. Процедуры OPEN и CLOSE лучше вызывать явно, особенно при использовании Oracle Text. В противном случае создаваемые Oracle Text индексы будут обновляться при каждом вызове WRITE или WRITEAPPEND, а не один раз при вызове CLOSE.

В разделе, посвященном типу данных BFILE, будет рассказано, как прочитать данные LOB прямо из внешнего файла операционной системы.

При записи данных в объекты LOB обновлять LOB-столбец в таблице не нужно, потому что локатор LOB остается неизменным. Мы не изменяли содержимое falls_directions (локатор) — вместо этого данные добавлялись в объект LOB, на который ссылался локатор.

Обновление LOB происходит в контексте транзакции. В данном примере мы не сохраняли результаты транзакции инструкцией COMMIT. Однако если вы хотите, чтобы записанная в объект LOB информация была сохранена в базе данных, после выполнения блока команд PL/SQL нужно выполнить инструкцию COMMIT. Если же после блока команд PL/SQL будет выполнена инструкция ROLLBACK, все произведенные в этом блоке изменения данных будут отменены.

В нашем примере данные записываются в столбец типа CLOB. Данные BLOB обрабатываются аналогично, с той лишь разницей, что аргументы процедур WRITE и WRITEAPPEND имеют тип данных RAW, а не VARCHAR2.

Следующий пример, реализованный в SQL*Plus, выводит на экран данные, помещенные ранее в таблицу waterfalls. В следующем разделе будет показано, как извлекать данные с помощью разных процедур пакета DBMS_LOB: 

SQL> SET LONG 2000
SQL> COLUMN falls_directions WORD_WRAPPED FORMAT A70
SQL> SELECT falls_directions
2 FROM waterfalls
3 WHERE falls_name='Munising Falls';
4 /
FALLS_DIRECTIONS
----------------------------------------------------------------------
Follow I-75 across the Mackinac Bridge. Take US-2 west from St. Ignace
to Blaney Park. Turn north on M-77 and drive to Seney. From Seney,
take M-28 west to Munising. In front of the paper mill, turn right on
H-58. Follow H-58 to Washington Street. Veer left onto Washington
Street. You'll find the Munising Falls visitor center across from the
hospital at the point where Washington Street becomes Sand Point Road.

Чтение данных из объектов LOB

Данные читаются из LOB при помощи процедуры DBMS_LOB.READ. Но сначала, конечно, следует получить локатор LOB. При чтении данных типа CLOB указывается смещение, задаваемое в символах. Операция чтения начинается с позиции, заданной смещением, а первый символ CLOB всегда имеет номер 1. В случае применения типа данных BLOB смещение задается в байтах. Кроме того, в вызове процедуры DBMS_LOB.READ необходимо задать количество читаемых символов или байтов. Поскольку объекты LOB бывают очень большими, имеет смысл извлекать их содержимое по частям.

Рассмотрим фрагмент кода, который загружает и выводит данные о местонахождении водопада Munising Falls. Мы четко определяем количество читаемых символов, чтобы оно соответствовало ограничению на длину строки в пакете DBMS_OUTPUT, а выводимый текст выглядел аккуратно. 

/* Файл в Сети: munising_falls_02.sql */
DECLARE
   directions CLOB;
   directions_1 VARCHAR2(300);
   directions_2 VARCHAR2(300);
   chars_read_1 BINARY_INTEGER;
   chars_read_2 BINARY_INTEGER;
   offset INTEGER;
BEGIN
   -- Получаем ранее добавленный локатор LOB
   SELECT falls_directions
   INTO directions
   FROM waterfalls
   WHERE falls_name='Munising Falls';
   -- Чтение начинается с первого символа
   offset := 1;
   -- Пытаемся прочитать 229 символов. В переменную chars_read_1
   -- будет записано реальное количество прочитанных символов
   chars_read_1 := 229;
   DBMS_LOB.READ(directions, chars_read_1, offset, directions_1);
   -- Если прочитано 229 символов, информация о смещении обновляется
   -- и производится попытка прочитать еще 255 символов.
   IF chars_read_1 = 229 THEN
      offset := offset + chars_read_1;
      chars_read_2 := 255;
      DBMS_LOB.READ(directions, chars_read_2, offset, directions_2);
   ELSE
      chars_read_2 := 0;
      directions_2 := '';
   END IF;
   -- Вывод общего количества прочитанных символов
   DBMS_OUTPUT.PUT_LINE('Characters read = ' ||
      TO_CHAR(chars_read_1+chars_read_2));
   -- вывод значения
   DBMS_OUTPUT.PUT_LINE(directions_1);
   DBMS_OUTPUT.PUT_LINE(directions_2);
END;

В результате выполнения этого кода выводится следующий текст:

Characters read = 414
Follow I-75 across the Mackinac Bridge. Take US-2 west from St. Ignace to Blaney
Park. Turn north on M-77 and drive to Seney. From Seney, take M-28 west to
Munising. In front of the paper mill, turn right on H-58. Follow H-58 to
Washington Street. Veer left onto Washington Street. You'll find the Munising
Falls visitor center across from the hospital at the point where Washington
Street becomes Sand Point Road. 

Процедуре DBMS_LOB.READ в качестве второго параметра, объявленного как IN OUT, передается переменная chars_read_1 (количество считанных символов). По окончании операции процедура обновляет его в соответствии с реальным количеством прочитанных символов. Если количество прочитанных символов (или байтов) будет меньше указанного, то это будет означать, что при чтении достигнут конец данных. К сожалению, обновить таким же образом информацию о смещении невозможно. Это приходится делать самостоятельно, по мере считывания последовательных фрагментов объектов LOB, в зависимости от количества прочитанных символов.

Для определения размера большого объекта можно воспользоваться функцией DBMS_LOB.GET_LENGTH (локатор_lob). Возвращаемое значение равно количеству байтов объекта BLOB или BFILE либо количеству символов объекта CLOB.

Особенности типа BFILE

Как упоминалось ранее, типы данных BLOB, CLOB и NCLOB представляют внутренние большие объекты, хранящиеся в базе данных, в то время как BFILE является внешним типом. Между объектами BFILE и внутренними LOB существуют три важных различия:

• Значение BFILE хранится не в базе данных, а в файле операционной системы.
• Объекты BFILE не участвуют в транзакциях (то есть внесенные в них изменения нельзя ни отменить, ни сохранить). Однако изменения, вносимые в локатор BFILE, в транзакциях можно и отменять, и сохранять.
• И в PL/SQL, и в Oracle объекты BFILE можно только считывать. Запись данных через типа BFILE не поддерживается. Внешние файлы, на которые указывают локаторы BFILE, должны создаваться вне СУБД Oracle.

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

Локатор BFILE состоит из псевдонима каталога и имени файла. Для получения локатора на основании этих двух параметров используется функция BFILENAME. Псевдоним каталога представляет собой определенное в Oracle имя, соответствующее имени каталога операционной системы. Псевдонимы позволяют программам PL/SQL работать с каталогами независимо от операционной системы. Имея привилегии CREATE ANY DIRECTORY, вы можете создать псевдоним каталога (сам каталог уже должен существовать в файловой системе) и предоставить доступ к нему следующим образом: 

CREATE DIRECTORY bfile_data AS 'c:\PLSQL Book\Ch13_Misc_Datatypes\'
GRANT READ ON DIRECTORY bfile_data TO gennick;

Впрочем, создание псевдонимов каталогов и управление доступом к ним — это функции администратора базы данных, не имеющие прямого отношения к PL/SQL, поэтому я не буду подробно рассматривать их. Если вам понадобятся более подробные сведения, поговорите с администратором базы данных или ознакомьтесь с разделом документации Oracle SQL Reference Manual, посвященным команде CREATE DIRECTORY. Чтобы получить список каталогов, к которым у вас имеется доступ, обратитесь с запросом к представлению ALL_DIRECTORIES.

Создание локатора BFILE

Чтобы создать локатор BFILE, следует вызвать функцию BFILENAME и передать ей псевдоним каталога и имя файла. В следующем примере создается локатор BFILE для HTML- файла, который затем сохраняется в таблице waterfalls. 

DECLARE
   web_page BFILE;
BEGIN
   -- Удаление строки Tannery Falls, чтобы этот пример
   -- мог выполняться многократно.
   DELETE FROM waterfalls WHERE falls_name='Tannery Falls';
   -- Вызов BFILENAME для создания локатора
   web_page := BFILENAME('BFILE_DATA','Tannery_Falls.htm');
   -- Сохранение локатора в таблице waterfalls
   INSERT INTO waterfalls (falls_name, falls_web_page)
   VALUES ('Tannery Falls',web_page);
END;

Локатор BFILE — всего лишь комбинация псевдонима каталога и имени файла. Реальный файл и каталог даже не обязаны существовать. Иначе говоря, Oracle позволяет создавать псевдонимы для несуществующих каталогов, а BFILENAME — локаторы для несуществующих файлов. Иногда это бывает удобно.

В имени каталога, которое вы указываете в вызовах BFILENAME, различается регистр символов, который должен соответствовать регистру, отображаемому представлением словаря данных ALL_DIRECTORIES. Сначала я использовал в своем примере запись bfile_data в нижнем регистре — и был огорошен многочисленными ошибками при попытке обратиться к внешнему файлу BFILE data (см. следующий раздел). В большинстве случаев имя каталога при вызове BFILENAME должно записываться в верхнем регистре.

Доступ к данным BFILE

Получив локатор BFILE, вы можете считывать данные из внешнего файла практически так же, как из объекта BLOB. В следующем примере из веб-страницы извлекаются первые 60 байт HTML-кода. Полученные данные, имеющие тип RAW, преобразуются в символьную строку функцией UTL_RAW.CAST_TO_VARCHAR2. 

DECLARE
   web_page BFILE;
   html RAW(60);
   amount BINARY_INTEGER := 60;
   offset INTEGER := 1;
BEGIN
   -- Получение LOB-локатора веб-страницы
   SELECT falls_web_page
   INTO web_page
   FROM waterfalls
   WHERE falls_name='Tannery Falls';
   -- Открываем локатор, читаем 60 байт и закрываем локатор
   DBMS_LOB.OPEN(web_page);
   DBMS_LOB.READ(web_page, amount, offset, html);
   DBMS_LOB.CLOSE(web_page);
   -- Чтобы результат выводился в шестнадцатеричной форме,
   -- удалите символы комментария из следующей строки
   --DBMS_OUTPUT.PUT_LINE(RAWTOHEX(html));
   -- Преобразование данных типа RAW в читабельную символьную строку
   DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(html));
END;

Результат выполнения этого кода:

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN

Максимальное число одновременно открытых в сеансе объектов BFILE устанавливается параметром базы данных SESSION_MAX_OPEN_FILES. Этот параметр определяет верхнюю границу количества файлов, которые могут быть одновременно открыты в сеансе (не только BFILE, но и любых других файлов, включая открытые с применением программ из пакета UTL_FILE).

Напомним, что Oracle поддерживает только чтение данных BFILE. Тип BFILE идеально подходит для работы с двоичными данными, созданными вне СУБД (графикой и т. д.). Например, вы можете загрузить на сервер изображения, отснятые цифровой камерой, создать локаторы BFILE для этих файлов и работать с ними в программах PL/SQL.

Использование BFILE для загрузки столбцов LOB

Тип BFILE не только обеспечивает доступ к двоичным файлам, созданным вне базы данных Oracle, но и является удобным средством загрузки внешних данных в столбцы LOB. В Oracle9i Release 1 для чтения двоичных данных из объекта BFILE и их записи в столбец BLOB использовалась функция DBMS_LOB.LOADFROMFILE, а в Oracle9i Release 2 появились намного более удобные средства:

• Функция DBMS_LOB.LOADCLOBFROMFILE загружает в столбец типа CLOB данные из объекта BFILE с необходимым преобразованием символов.
• Функция DBMS_LOB.LOADBLOBFROMFILE загружает в столбец типа BLOB данные из объекта BFILE. Она делает то же, что DBMS_LOB.LOADFROMFILE, но ее интерфейс приближен к интерфейсу функции DBMS_LOB.LOADCLOBFROMFILE.

Представьте, что информация о местонахождении водопада Tannery Falls хранится во внешнем текстовом файле с именем TanneryFalls.directions в каталоге, на который ссылается псевдоним BFILE_DATA. Следующий пример демонстрирует использование DBMS_LOB.LOADCLOBFROMFILE для загрузки информации в CLOB-столбец falls_directions таблицы waterfalls: 

/* Файл в Сети: munising_falls_03.sql */
DECLARE
   Tannery_Falls_Directions BFILE
      := BFILENAME('BFILE_DATA','TanneryFalls.directions');
   directions CLOB;
   destination_offset INTEGER := 1;
   source_offset INTEGER := 1;
   language_context INTEGER := DBMS_LOB.default_lang_ctx;
   warning_message INTEGER;
BEGIN
   -- Удаление строки 'Tannery Falls',
   -- чтобы этот пример мог выполняться многократно.
   DELETE FROM waterfalls WHERE falls_name='Tannery Falls';
   -- Вставка новой строки с созданием локатора LOB
   -- вызовом EMPTY_CLOB()
   INSERT INTO waterfalls
      (falls_name,falls_directions)
   VALUES ('Tannery Falls',EMPTY_CLOB());
   -- Чтение локатора LOB, созданного предыдущей командой INSERT
   SELECT falls_directions
   INTO directions
   FROM waterfalls
   WHERE falls_name='Tannery Falls';
   -- Открытие объекта CLOB и исходного объекта BFILE
   DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.OPEN(Tannery_Falls_Directions);
   -- Загрузка содержимого BFILE в столбец CLOB
   DBMS_LOB.LOADCLOBFROMFILE
      (directions, Tannery_Falls_Directions,
   DBMS_LOB.LOBMAXSIZE,
      destination_offset, source_offset,
   NLS_CHARSET_ID('US7ASCII'),
      language_context, warning_message);
   -- Проверка единственно возможного предупреждения
   IF warning_message = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
      DBMS_OUTPUT.PUT_LINE (
        'Warning! Some characters couldn''t be converted.');
   END IF;
   -- Закрытие обоих объектов LOB
   DBMS_LOB.CLOSE(directions);
   DBMS_LOB.CLOSE(Tannery_Falls_Directions);
END;

Основная работа в этом фрагменте кода выполняется вызовом DBMS_LOB.LOADCLOBFROMFILE. Эта процедура читает данные из внешнего файла, выполняет все необходимые преобразования набора символов и записывает данные в столбец CLOB. Я использую константу DBMS_LOB.LOBMAXSIZE для определения объема загружаемых данных. Мне нужны все данные из внешнего файла, а DBMS_LOB.LOBMAXSIZE определяет максимальный объем, который может храниться в CLOB.

Смещения источника и приемника начинаются с 1. Чтение должно начинаться с первого символа из BFILE, а запись — с первого символа CLOB. Чтобы упростить множественные последовательные вызовы LOADCLOBFROMFILE, процедура обновляет оба смещения так, чтобы они указывали в следующую позицию за последним прочитанным символом.

Так как они являются параметрами IN OUT, при вызове процедуры должны использоваться переменные, а не константы.

Вызов NLS_CHARSET_ID возвращает идентификатор набора символов внешнего файла. Процедура LOADCLOBFROMFILE преобразует загружаемые данные из этого набора в набор символов базы данных. Единственное возможное предупреждение, которое может вернуть LOADCLOBFROMFILE, означает, что некоторые символы не могут быть преобразованы из исходного набора данных в итоговый. В программе это предупреждение проверяется в команде IF, следующей за загрузкой.

Не путайте предупреждения с ошибками PL/SQL; загрузка все равно будет выполнена в соответствии с запросом.

Следующий пример SQL*Plus демонстрирует загрузку данных из внешнего файла с использованием LOADCLOBFROMFILE: 

SQL> SET LONG 2000
SQL> COLUMN falls_directions WORD_WRAPPED FORMAT A70
SQL> SELECT falls_directions
2 FROM waterfalls
3 WHERE falls_name='Tannery Falls';
4 /
FALLS_DIRECTIONS
----------------------------------------------------------------------
From downtown Munising, take Munising Avenue east. It will
shortly turn into H-58. Watch for Washington Street veering
off to your left. At that intersection you'll see a wooden
stairway going into the woods on your right. Go up that
stairway and follow the trail to the falls. Do not park
on H-58! You'll get a ticket. You can park on Nestor Street,
which is just uphill from the stairway.

SecureFiles и BasicFiles

Технология SecureFiles, появившаяся в Oracle11g, обладает рядом преимуществ по сравнению со старыми реализациями LOB, теперь известными под общим названием BasicFiles. Эти преимущества в основном относятся к внутренней реализации и остаются прозрачными для программистов — в коде используются те же ключевые слова, тот же синтаксис и операции. Во внутренней реализации SecureFiles усовершенствованы многие аспекты управления LOB, включая формат хранения данных на диске, кэширование, блокировку, отмену и алгоритмы управления дисковым пространством. Обновленная технология заметно улучшает быстродействие и позволяет устранять дублирование, сжимать и шифровать объекты LOB посредством определения простых параметров. Кроме того, был введен новый уровень ведения журнала FILESYSTEM_LIKE_LOGGING в дополнение к существующим режимам LOGGING и NOLOGGING. В новом режиме фиксируются только изменения метаданных (по аналогии с тем, как это делается в журнальных файловых системах).

По данным тестирования Oracle, повышение быстродействия от использования SecureFiles составляет от 200 до 900%. В простом тесте с загрузкой файлов PDF на сервер Microsoft Windows я наблюдал снижение времени загрузки от 80 до 90% от 169 секунд до 20–30 секунд (в зависимости от конфигурации и количества загрузок). В системе x86 Linux выигрыш был более скромным. Возможно, в вашей ситуации цифры будут другими, но ускорение будет наверняка!

Чтобы использовать SecureFiles при работе с данными LOB, задайте параметру базы данных DB_SECUREFILE значение PERMITTED (используется по умолчанию). Кроме того, табличное пространство, в котором будут храниться данные LOB, должно использовать технологию ASSM (Automatic Segment Space Management).

В Oracle Database 12c технология SecureFiles используется по умолчанию; в Oracle Database 11g (Release 1 или Release 2) механизм хранения определяется параметром DB_SECUREFILE. Если задать ему значение ALWAYS, то по умолчанию для объектов LOB используется технология SecureFiles; в противном случае используется BasicFiles.

Если вы не уверены относительно базы данных, обратитесь за помощью к администратору.

Устранение дубликатов

В режиме устранения дубликатов база данных хранит только одну копию каждого объекта LOB. Для каждого объекта генерируется хеш-код, который сравнивается с хеш-кодами других объектов LOB в таблице или в ее разделе. Учтите, что устранение дубликатов не работает между разделами и подразделами.

Сжатие

В режиме сжатия SecureFiles база данных сжимает данные LOB как на диске, так и в памяти. Поддерживаются режимы MEDIUM (среднее сжатие, используется по умолчанию) и HIGH (высокое сжатие). Высокое сжатие занимает больше времени, но уменьшает размер данных LOB. Простые тесты с файлами PDF показали, что загрузка данных в режиме HIGH занимала примерно на 25% больше времени, чем в режиме MEDIUM.

Чтобы включить режим устранения дубликатов одновременно со сжатием, укажите соответствующие параметры в определении LOB: 

TABLE waterfalls
(
falls_name VARCHAR2 (80)
, falls_photo BLOB
, falls_directions CLOB
, falls_description NCLOB
, falls_web_page BFILE
)
LOB (falls_photo) STORE AS SECUREFILE (COMPRESS DEDUPLICATE)
LOB (falls_directions) STORE AS SECUREFILE (COMPRESS DEDUPLICATE)
LOB (falls_description) STORE AS SECUREFILE (COMPRESS DEDUPLICATE)

При включении обоих режимов сначала выполняется устранение дубликатов, а затем сжатие.

Шифрование

Режим шифрования SecureFiles также включается в разделе LOB команды CREATE TABLE. Также можно выбрать алгоритм шифрования; в Oracle11g поддерживаются алгоритмы 3DES168, AES128, AES192 (по умолчанию) и AES256. Допускается использование любых комбинаций режимов устранения дубликатов, сжатия и шифрования: 

TABLE waterfalls
(
falls_name VARCHAR2 (80)
, falls_photo BLOB
, falls_directions CLOB
, falls_description NCLOB
, falls_web_page BFILE
)
LOB (falls_photo) STORE AS SECUREFILE (COMPRESS DEDUPLICATE)
LOB (falls_directions) STORE AS SECUREFILE (ENCRYPT USING 'AES256')
LOB (falls_description) STORE AS SECUREFILE
(ENCRYPT DEDUPLICATE COMPRESS HIGH
)

Если база данных не была настроена на применение прозрачного шифрования данных (TDE Transparent Data Encryption), шифрование LOB потребует двух подготовительных шагов. Сначала необходимо создать бумажник (wallet) для хранения главного ключа. Если бумажник должен храниться в стандартном каталоге ($ORACLE_BASE/ admin/$ORACLE_SID/wallet), его создание и открытие можно совместить в одном шаге:

ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "My-secret!passc0de";

Если бумажник должен храниться в нестандартном каталоге, вам придется задать этот каталог в файле SQLNET.ORA. Так, чтобы бумажник хранился в каталоге /oracle/wallet, включите в файл SQLNET.ORA следующий фрагмент: 

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=file)
(METHOD_DATA=(DIRECTORY=/oracle/wallet)))

Созданный бумажник необходимо открывать заново после каждого перезапуска экземпляра. Открытие и закрытие бумажника выполняется следующим образом:

ALTER SYSTEM SET ENCRYPTION WALLET OPEN AUTHENTICATED BY "My-secret!passc0de";
-- Закрытие бумажника
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE; 

Временные объекты LOB

До сих пор речь шла о долговременном хранении больших объемов неструктурированных данных за счет использования типов LOB. Применяемые для этого типы LOB называются постоянными. Однако многим приложениям нужны временные LOB, используемые в качестве локальных переменных и не хранящиеся в базе данных. В этом разделе рассказывается о временных объектах LOB и о том, как работать с ними с помощью программ встроенного DBMS_LOB.

В Oracle8i и последующих версиях поддерживается возможность создания, освобождения, чтения и обновления временных объектов LOB через интерфейс OCI (Oracle Call Interface) и вызовы DBMS_LOB. По умолчанию временный объект LOB существует в течение всего сеанса, в котором он создан, но многие объекты явно освобождаются и до окончания сеанса. Временные объекты типа LOB являются удобной рабочей средой для быстрого выполнения различных операций с данными. Более высокая их производительность по сравнению с постоянными объектами LOB достигается за счет того, что эти операции не фиксируются в журнале и для них не генерируются записи отмены. При перезаписи или обновлении объекта LOB Oracle копирует весь объект в новый сегмент данных. Избавляясь от дополнительной нагрузки, приложение, выполняющее множество мелких операций с LOB, может работать значительно быстрее.

Только что созданный временный объект LOB всегда пуст; его локаторы не нужно (да и невозможно) инициализировать вызовами функций EMPTY_CLOB и EMPTY_BLOB. По умолчанию все временные объекты LOB удаляются в конце сеанса, в котором они созданы. Если процесс неожиданно завершится аварийно или произойдет сбой базы данных, временные объекты LOB будут удалены, а занимаемая ими память освободится.

Временные объекты LOB, как и постоянные, хранятся на диске в базе данных. Термин «временные» не должен вводить вас в заблуждение. Временные объекты LOB записываются на диск, но не связываются с определенными столбцами таблиц, располагаясь во временном табличном пространстве сеанса. Следовательно, для их использования нужно обеспечить наличие достаточного объема временного табличного пространства.

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

Создание временного объекта LOB

Чтобы получить экземпляр временного объекта, необходимо сначала создать его. Один из способов создания основан на вызове процедуры DBMS_LOB.CREATETEMPORARY. Процедура создает временный объект типа BLOB или CLOB и соответствующий индекс во временном пространстве. Заголовок процедуры выглядит так: 

DBMS_LOB.CREATETEMPORARY (
lob_loc IN OUT NOCOPY [ BLOB | CLOB CHARACTER SET ANY_CS ],
cache IN BOOLEAN,
dur IN PLS_INTEGER := DBMS_LOB.SESSION);

Параметры DBMS_LOB.CREATETEMPORARY представлены в табл. 1.

Другой способ создания временного объекта LOB основан на объявлении переменной LOB в коде PL/SQL и присваивании ей значения. Например, следующий фрагмент создает временные объекты BLOB и CLOB:

DECLARE
   temp_clob CLOB;
   temp_blob BLOB;
BEGIN
   -- Присваивание значения пустой переменной CLOB или BLOB
   -- заставляет PL/SQL неявно создать временный объект LOB,
   -- существующий на протяжении сеанса.
   temp_clob :=' http://www.nps.gov/piro/';
   temp_blob := HEXTORAW('7A');
END;

Я не могу однозначно порекомендовать тот или иной вариант создания временных LOB, но вызов DBMS_LOB.CREATETEMPORARY более четко выражает намерения программиста.

Освобождение временного объекта LOB

Процедура DBMS_LOB.FREETEMPORARY освобождает временный объект типа BLOB или CLOB в текущем временном табличном пространстве. Заголовок процедуры:

PROCEDURE DBMS_LOB.FREETEMPORARY (
   lob_loc IN OUT NOCOPY
      [ BLOB | CLOB CHARACTER SET ANY_CS ]);

В следующем примере сначала создаются, а затем освобождаются два временных объекта LOB:

DECLARE
   temp_clob CLOB;
   temp_blob BLOB;
BEGIN
   -- В случае присваивания значения неинициализированной
   -- переменной CLOB или BLOB PL/SQL неявно создает временный объект,
   -- существующий только на время текущего сеанса.
   temp_clob :='http://www.exploringthenorth.com/alger/alger.html';
   temp_blob := HEXTORAW('7A');
   DBMS_LOB.FREETEMPORARY(temp_clob);
   DBMS_LOB.FREETEMPORARY(temp_blob);
END; 

После вызова FREETEMPORARY освобожденный локатор LOB (lob_loc) помечается как недействительный. Если присвоить его другому локатору с помощью обычного оператора присваивания PL/SQL, то и этот локатор будет освобожден и помечен как недействительный.

PL/SQL неявно освобождает временные объекты LOB при выходе за пределы области действия блока.

Проверка статуса объекта LOB

Функция ISTEMPORARY позволяет определить, является ли объект с заданным локатором временным или постоянным. Она возвращает целочисленный код: 1 для временного объекта, 0 для постоянного. 

DBMS_LOB.ISTEMPORARY (
   lob_loc IN [ BLOB | CLOB CHARACTER SET ANY_CS ])
RETURN INTEGER;

Обратите внимание: функция возвращает числовой код (1 или 0), а не тип данных BOOLEAN.

Управление временными объектами LOB

Временные большие объекты обрабатываются не так, как обычные постоянные. Для них не поддерживаются транзакции, операции согласованного чтения, откаты и т. д. Такое сокращение функциональности имеет ряд следствий:

• Если при обработке временного объекта LOB произойдет ошибка, этот объект приходится освобождать и начинать все заново.
• Одному временному объекту LOB не следует присваивать несколько локаторов. Отсутствие поддержки операций согласованного чтения и отката при использовании нескольких локаторов может привести к значительному снижению производительности.
• Если пользователь модифицирует временный объект LOB, на который указывает еще один локатор, создается глубокая копия данного объекта. После этого локаторы будут ссылаться на разные объекты. Директива компилятора NOCOPY запрещает глубокое копирование при передаче локаторов LOB в аргументах.
• Чтобы преобразовать временный объект в постоянный, нужно вызвать программу DBMS_LOB.COPY и скопировать временный объект LOB в постоянный. Временные локаторы LOB действуют только в рамках сеанса. Локатор нельзя передать из одного сеанса в другой. Если объект LOB должен использоваться в разных сеансах, сделайте его постоянным.

В Oracle9i Database появилось представление с именем V$TEMPORARY_LOBS, которое содержит информацию о количестве существуюих кэшированных и некэшированных объектов LOB для сеанса. Администратор базы данных может объединить информацию V$TEMPORARY_LOBS с представлением словаря данных DBA_SEGMENTS, чтобы узнать, сколько дискового пространства сеанс использует для хранения временных LOB.

Встроенные операции LOB

С первых дней появления функциональности LOB в Oracle многочисленные пользователи, программисты и разработчики запросов желали использовать LOB как очень большие разновидности обычных скалярных переменных. В частности, пользователи хотели работать с CLOB как с огромными строками, передавать их функциям SQL, использовать в условиях WHERE команд SQL и т. д. К их огорчению, объекты CLOB изначально не могли использоваться вместо VARCHAR2. Например, в Oracle8 Database и Oracle8i Database к столбцу CLOB нельзя было применить символьную функцию: 

SELECT SUBSTR(falls_directions,1,60)
FROM waterfalls

Начиная с Oracle9i Database, объекты CLOB могут заменять VARCHAR2 во многих ситуациях:

• Объекты CLOB могут передаваться многим VARCHAR2-функциям PL/SQL и SQL — эти функции существуют в перегруженных версиях с параметрами VARCHAR2 и CLOB.
• В PL/SQL, но не в SQL, с переменными LOB могут использоваться различные операторы отношений, такие как «меньше» (<), «больше» (>) и «равно» (=).
• Значения CLOB можно присваивать переменным VARCHAR2, и наоборот. Также можно осуществлять выборку значений CLOB в переменные VARCHAR2, и наоборот. Такая возможность существует благодаря тому, что PL/SQL теперь выполняет неявные преобразования между типами CLOB и VARCHAR2.

Семантика SQL

Возможности, упомянутые в предыдущем разделе, Oracle называет «поддержкой семантики SQL для LOB». С точки зрения разработчика PL/SQL это означает, что с LOB можно работать на уровне встроенных операторов (вместо отдельного пакета).

Следующий пример демонстрирует некоторые возможности семантики SQL: 

DECLARE
   name CLOB;
   name_upper CLOB;
   directions CLOB;
   blank_space VARCHAR2(1) := ' ';
BEGIN
   -- чтение VARCHAR2 в CLOB и применение функции к CLOB
   SELECT falls_name, SUBSTR(falls_directions,1,500)
   INTO name, directions
   FROM waterfalls
   WHERE falls_name = 'Munising Falls';
      -- Преобразование CLOB к верхнему регистру
      name_upper := UPPER(name);
   -- Сравнение двух CLOB
   IF name = name_upper THEN
      DBMS_OUTPUT.PUT_LINE('We did not need to uppercase the name.');
   END IF;
   -- Конкатенация CLOB со строками VARCHAR2
   IF INSTR(directions,'Mackinac Bridge') <> 0 THEN
      DBMS_OUTPUT.PUT_LINE('To get to ' || name_upper || blank_space
         || 'you must cross the Mackinac Bridge.');
   END IF;
END;

Результат:

To get to MUNISING FALLS you must cross the Mackinac Bridge. 

Маленький фрагмент кода в этом примере содержит несколько интересных моментов:

• Столбец falls_name имеет тип VARCHAR2, однако он читается в переменную CLOB, что демонстрирует неявное преобразование между типами VARCHAR2 и CLOB.
• Функция SUBSTR ограничивает выборку первыми 500 символами описания, а функция UPPER преобразует название водопада к верхнему регистру. Тем самым демонстрируется применение функций SQL и PL/SQL к LOB.
• Команда IF, сравнивающая name с name_upper, выглядит немного неестественно, но она демонстрирует возможность применения операторов отношения к LOB.
• Название, приведенное к верхнему регистру — CLOB, — объединяется со строковыми константами и одной строкой VARCHAR2 (blank_space). Тем самым демонстрируется возможность конкатенации CLOB.

При использовании этой функциональности приходится учитывать многочисленные ограничения и скрытые ловушки. Например, не каждая функция, получающая значение VARCHAR2, примет вместо него CLOB; есть и исключения. В частности, функции регулярных выражений работают с семантикой SQL, а агрегатные функции — нет; не все операторы отношений поддерживаются для LOB и т. д. Все эти ограничения подробно описываются в разделе «SQL Semantics and LOBs» главы 10 руководства SecureFiles and Large Objects Developer’s Guide для Oracle Database 11g и 12c. Для Oracle Database 10g обращайтесь к главе 9, «SQL Semantics and LOB», руководства Application Developers Guide — Large Objects. Если вы собираетесь использовать семантику SQL, я настоятельно рекомендую ознакомиться с соответствующим разделом руководства для вашей базы данных.

Семантика SQL для LOB действует только для внутренних типов LOB: CLOB, BLOB и NCLOB. Поддержка семантики SQL не относится к BFILE.

Семантика SQL может создавать временные объекты LOB

При применении семантики SQL к LOB необходимо учитывать один важный аспект: она часто приводит к созданию временного объекта LOB. Подумайте, как функция UPPER применяется к CLOB: 

DECLARE
   directions CLOB;
BEGIN
   SELECT UPPER(falls_directions)
   INTO directions
   FROM waterfalls
   WHERE falls_name = 'Munising Falls';
END;

Так как объем данных CLOB очень велик, эти объекты хранятся на диске. База данных не может преобразовать к верхнему регистру читаемый объект CLOB, потому что это означает изменение его значения на диске — по сути это означает изменение читаемого значения. Также база данных не может внести изменения в копию CLOB в памяти, потому что значение может не поместиться в памяти, а в результате выборки программа получает только локатор, который указывает на значение на диске. Единственное, что может сделать база данных, — создать временный объект CLOB во временном пространстве. Функция UPPER копирует данные из исходного объекта CLOB во временный объект CLOB, преобразуя символы к верхнему регистру в процессе копирования. Затем команда SELECT возвращает локатор LOB, указывающий на временный, а не на исходный объект CLOB. У этого факта есть два очень важных следствия:

• Локатор, возвращаемый функцией или выражением, не может использоваться для обновления исходного объекта LOB. Переменная directions в моем примере не может использоваться для обновления постоянного объекта LOB, хранящегося в базе данных, потому что она в действительности указывает на временный объект LOB, возвращаемый функцией UPPER.
• Создание временного объекта LOB требует расхода дискового пространства и вычислительных ресурсов. Эта тема более подробно рассматривается в разделе «Влияние семантики SQL на быстродействие».

Чтобы получить описание местонахождения водопада в верхнем регистре, сохранив возможность обновления исходного описания, необходимо получить два локатора LOB:

DECLARE
   directions_upper CLOB;
   directions_persistent CLOB;
BEGIN
   SELECT UPPER(falls_directions), falls_directions
   INTO directions_upper, directions_persistent
   FROM waterfalls
   WHERE falls_name = 'Munising Falls';
END;

Теперь я могу обратиться к описанию в верхнем регистре через локатор в directions_upper и изменить исходное описание через локатор в directions_persistent. Выборка лишнего локатора в данном случае не отражается на быстродействии приложения — на него влияет преобразование текста к верхнему регистру и его сохранение во временном объекте CLOB. Локатор в directions_persistent просто читается из таблицы базы данных.

В общем случае любая символьно-строковая функция, которая обычно получает VARCHAR2 и возвращает VARCHAR2, при передаче CLOB вернет временный объект CLOB. Аналогичным образом выражения, возвращающие CLOB, почти наверняка будут возвращать временные объекты CLOB. Временные объекты CLOB и BLOB не могут использоваться для обновления данных LOB, использованных в выражении или функции.

Влияние семантики SQL на быстродействие

Используя новую семантику SQL при работе с LOB, стоит учесть ее последствия для быстродействия. Помните, что данные LOB могут занимать до 128 терабайт (4 гигабайта до Oracle Database 10g). Соответственно, при неосмотрительном использовании LOB как любого другого типа переменной или столбца могут возникнуть серьезные проблемы. Взгляните на следующий запрос, который пытается найти все водопады, путь к которым проходит через мост Макинак: 

SELECT falls_name
FROM waterfalls
WHERE INSTR(UPPER(falls_directions),'MACKINAC BRIDGE') <> 0;

Представьте, как должна действовать база данных Oracle при обработке этого запроса. Для каждой строки в таблице waterfalls она должна взять содержимое столбца falls_directions, преобразовать его к верхнему регистру и поместить результаты во временный объект CLOB (находящийся во временном табличном пространстве). Затем ко временному объекту LOB применяется функция INSTR, которая ищет строку «MACKINAC BRIDGE». В моих примерах описания были относительно короткими. Но представьте, что falls_directions содержит действительно большие данные LOB, а средний размер столбца составляет 1 Гбайт. Естественно, выделение пространства для всех временных объектов LOB при преобразовании описаний к верхнему регистру приведет к быстрому расходованию временного табличного пространства.

А теперь представьте, сколько времени потребуется для создания копии каждого объекта CLOB для его преобразования к верхнему регистру, для выделения и освобождения пространства временных объектов CLOB во временном пространстве и для посимвольного поиска данных в CLOB средним объемом в 1 Гбайт. Конечно, такие запросы навлекут на вас гнев администратора базы данных.

Oracle Text и семантика SQL

Если вам понадобится выполнить запросы с эффективным поиском в CLOB, возможно, вам поможет Oracle Text. Допустим, когда-нибудь вам потребуется написать запрос следующего вида: 

SELECT falls_name
FROM waterfalls
WHERE INSTR(UPPER(falls_directions), 'MACKINAC BRIDGE') <> 0;

Если falls_directions является столбцом CLOB, эффективность такого запроса оставляет желать лучшего. Однако при использовании Oracle Text вы можете определить для этого столбца индекс Oracle Text, игнорирующий регистр символов, после чего воспользоваться предикатом CONTAINS для эффективной обработки запроса: 

SELECT falls_name
FROM waterfalls
WHERE
CONTAINS(falls_directions,'mackinac bridge') > 0;

За дополнительной информацией о CONTAINS и индексах Oracle Text, игнорирующих регистр символов, обращайтесь к документации Oracle Text Application Developer’s Guide.

Из-за возможных негативных последствий от применения семантики SQL к LOB в документации Oracle рекомендуется ограничить ее данными LOB, размер которых не превышает 100 Кбайт. Я не готов предложить конкретные рекомендации по размеру; рассмотрите каждую ситуацию в контексте своих обстоятельств. Всегда учитывайте возможные последствия от применения семантики SQL к LOB; возможно, вам стоит провести тестирование для оценки этих последствий, чтобы принять разумное решение в вашей конкретной ситуации.

Функции преобразования объектов LOB

Oracle предоставляет в распоряжение программиста несколько функций преобразования больших объектов. Перечень этих функций приведен в табл. 2.

Функция TO_LOB предназначена для одноразового перемещения данных LONG и LONG RAW в столбцы типа CLOB и BLOB, поскольку типы LONG и LONG RAW теперь считаются устаревшими. Функции TO_CLOB и TO_NCLOB предоставляют удобные средства преобразования символьных данных больших объектов и наборов символов базы данных и национальных языков.

Вас заинтересует / Intresting for you:

Встроенные методы коллекций PL... 14838 просмотров sepia Tue, 29 Oct 2019, 09:54:01

Управление приложениями PL/SQL... 4651 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48

Тип данных RAW в PL/SQL 12331 просмотров Doctor Thu, 12 Jul 2018, 08:41:33

Символьные функции и аргументы... 18586 просмотров Анатолий Wed, 23 May 2018, 18:54:01