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

Использование UTL_FILE

В пакете UTL_FILE используется следующая последовательность действий с файлами – сначала файл открывается в заданном режиме, затем производятся действия с его содержимым, по окончании которых файл закрывается. После открытия файла во все процедуры и функции пакета UTL_FILE передается его идентификатор, который представляет собой переменную-запись PL/SQL объявленного в пакете UTL_FILE типа.

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

Программа

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

FOPEN (функция)

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

IS_OPEN (функция)

проверяет, открыт ли файл

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

закрывает открытый файл

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

закрывает все открытые файлы (a – all)

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

считывает строку из файла

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

записывает в файл строку без символа конца строки

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

записывает в файл строку с символом конца строки

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

записывает в файл форматированный текст

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

вызывает физическую запись буферизированных данных

Файл может быть открыт процедурой UTL_FILE.FOPEN в одном из трех режимов:

для чтения (read) – содержимое файла не будет изменяться;

для записи (write) – содержимое файла будет перезаписано;

для добавления данных (append) – запись будет осуществляться в конец файла без перезаписи имевшегося содержимого.

В качестве примера работы с пакетом UTL_FILE приведем код процедуры table_copy, которая построчно сохраняет выборку SQL-запроса к таблице tab1 в файл. У table_copy имеется параметр p_mode, определяющий режим открытия файла.

SQL> CREATE OR REPLACE PROCEDURE table_copy(p_mode IN VARCHAR2) IS

2 fid UTL_FILE.FILE_TYPE;

3 BEGIN

4 fid := UTL_FILE.FOPEN (location=> 'C:\Dir1',

filename => 'f-name.txt',

open_mode=> p_mode);

5 FOR rec IN (SELECT at1,at2 FROM tab1) LOOP

6 UTL_FILE.PUT_LINE (fid, rec.at1||' '||rec.at2);

7 END LOOP;

8 UTL_FILE.FCLOSE (fid);

9 EXCEPTION

10 WHEN UTL_FILE.INVALID_PATH

11 THEN DBMS_OUTPUT.PUT_LINE('Неверный каталог');

12 WHEN UTL_FILE.INVALID_MODE

13 THEN DBMS_OUTPUT.PUT_LINE('Неверный режим работы с файлом');

14 WHEN UTL_FILE.INVALID_FILEHANDLE

15 THEN DBMS_OUTPUT.PUT_LINE('Ошибочный дескриптор файла');

16 WHEN UTL_FILE.READ_ERROR

17 THEN DBMS_OUTPUT.PUT_LINE('Ошибка при чтении файла');

18 WHEN UTL_FILE.WRITE_ERROR

19 THEN DBMS_OUTPUT.PUT_LINE('Ошибка при записи в файл');

20 WHEN UTL_FILE.INTERNAL_ERROR

21 THEN DBMS_OUTPUT.PUT_LINE('Произошла внутренняя ошибка');

22 WHEN OTHERS

25 THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);

26 END;

27 /

Procedure created.

SQL> BEGIN

2 table_copy('A');

3 END;

4 /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM tab1;

AT1 A

– -

1 A

2 B

3 C

– команда HOST утилиты SQL*Plus позволяет выполнять команды ОС

– выполняем прямо из SQL*Plus команду type ОС Windows

SQL> HOST type C:\Dir1\f-name.txt

1 A

2 B

3 C

В ходе выполнения процедуры table_copy с параметром 'A' (append) в конец файла fname.txt, находящийся в каталоге C:\Dir1, будут записаны все строки таблицы tab1. Обратите внимание – если вызвать процедуру table_copy с параметром 'W' (write), то существующее содержимое файла будет перезаписано на содержимое таблицы.

В старших версиях сервера 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 произошла нормально.