Иван Задворьев - Язык PL/SQL

В старших версиях сервера Oracle с помощью пакета UTL_FILE можно копировать и удалять файлы. Также для работы с файлами на сервере можно использовать хранимые программы на Java.

В Oracle имеются специальные типы данных для хранения больших объектов (Large Objects, LOB), размеры которых могут измеряться в террабайтах:

BLOB – тип для представления бинарных данных (значение содержит локатор на большой бинарный объект, хранящийся в базе данных);

CLOB – тип для представления символьных данных (значение содержит локатор на большой символьный объект, хранящийся в базе данных);

BFILE – тип данных для описания файлов (значение содержит указатель на файл, который находится вне базы данных Oracle).

Локатором называется хранящийся в базе данных указатель на данные большого объекта. Значение типа BLOB или CLOB может характеризоваться одним из трех состояний:

содержит NULL (не содержит локатор);

содержит локатор, указывающий данные большого объекта;

содержит локатор, не указывающий ни на какие данные.

Про последнее состояние говорят, что это «пустой» (empty) LOB-объект. «Пустые» LOB-объекты инициализируются встроенными функциями EMPTY_BLOB() и EMPTY_CLOB(). Для определения текущего состояния значения из трех возможных используется следующая логика:

IF some_clob IS NULL THEN

– нет ни данных, ни локатора

ELSIF DBMS_LOB.GETLENGTH(some_CLOB)=0 THEN

– пустой (empty) LOB-объект

ELSE

– данные в LOB-объекте есть

END IF

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

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

Таблица 10.Программы пакета DBMS_LOB.

Программа

Описание программы

APPEND (процедура)

записывает данные в конец LOB-объекта

WRITE (процедура)

записывает данные в LOB-объект по смещению

COMPARE (функция)

сравнивает два LOB-объекта одного типа

GETLENGTH (функция)

возвращает длину LOB-объекта

INSTR (функция)

возвращает позицию вхождения строки в объект

READ (процедура)

считывает часть LOB-объекта

SUBSTR (функция)

возвращает часть LOB-объекта по смещению

FILECLOSE (процедура)

закрывает файл по указателю-значению BFILE

FILEEXISTS (функция)

проверяет наличие файла по указателю

FILEOPEN (процедура)

открывает файл для значения BFILE

COPY (процедура)

копирует LOB-объекты

ERASE (процедура)

удаляет LOB-объект полностью или частично

Работа с файлами с помощью пакета DBMS_LOB

В качестве примера использования пакета DBMS_LOB приведем процедуру f_compare, которая сравнивает файлы в каталоге dir1. Имена файлов передаются как параметры:

SQL> CREATE DIRECTORY dir1 AS 'C:\WORK';

Directory created.

SQL> CREATE OR REPLACE PROCEDURE f_compare

2 (fname1 IN VARCHAR2, fname2 IN VARCHAR2) IS

3 file_1 BFILE;

4 file_2 BFILE;

5 result INTEGER;

6 BEGIN

7 file_1 := BFILENAME('DIR1',fname1);

8 file_2 := BFILENAME('DIR1',fname2);

9 DBMS_LOB.FILEOPEN(file_1);

10 DBMS_LOB.FILEOPEN(file_2);

11 result := DBMS_LOB.COMPARE(file_1,file_2,

12 DBMS_LOB.LOBMAXSIZE,1,1);

13 IF (result != 0) THEN

14 DBMS_OUTPUT.PUT_LINE('Файлы различные');

15 ELSE

16 DBMS_OUTPUT.PUT_LINE('Файлы одинаковые');

17 END IF;

18 DBMS_LOB.FILECLOSE(file_1);

19 DBMS_LOB.FILECLOSE(file_2);

20 END;

21 /

Procedure created.

SQL> BEGIN

2 f_compare('fname.txt','fname.txt');

3 END;

4 /

Файлы одинаковые

SQL> BEGIN

2 f_compare('fname.txt','fname2.txt');

3 END;

4 /

Файлы различные

SQL> BEGIN

2 f_compare('fname.txt','fname3.txt');

3 END;

4 /

BEGIN

*

ERROR at line 1:

ORA-22288: file or LOB operation FILEOPEN failed

The system cannot find the path specified

ORA-06512: at "SYS.DBMS_LOB", line 475

ORA-06512: at "SYSTEM.F_COMPARE", line 9

ORA-06512: at line 2

При последнем вызове процедуры f_compare не удалось открыть указанный файл. Обратите внимание, ошибка произошла при попытке открыть файл, установка указателя BFILE произошла нормально.

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

SQL> CREATE TABLE tab1 (at1 NUMBER, at2 BLOB, at3 BFILE);

Table created.

SQL> INSERT INTO tab1 VALUES (2,EMPTY_BLOB(),NULL);

1 row created.

SQL> DECLARE

2 l_BLOB BLOB;

3 file_1 BFILE;

4 BEGIN

5 SELECT at2 INTO l_BLOB FROM tab1

6 WHERE at1=2 FOR UPDATE;

7 file_1 := BFILENAME('DIR1','fname.txt');

8 DBMS_LOB.FILEOPEN(file_1);

10 DBMS_LOB.LOADFROMFILE(l_BLOB,file_1,

11 DBMS_LOB.GETLENGTH(file_1));

12 COMMIT;

13 END;

14 /

PL/SQL procedure successfully completed.