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

использовать параметр базы данных utl_file_dir;

создать специальные объекты баз данных – директории (DIRECTORY) и предоставить пользователям привилегии доступа к ним.

В параметре utl_file_dir задается список каталогов, с файлами из которых может осуществлять операции UTL_FILE. Для просмотра текущего значения параметра utl_file_dir можно использовать представление словаря данных V$PARAMETER или команду SHOW PARAMETER утилиты SQL*Plus:

SQL> SELECT value dir FROM V$PARAMETER

2 WHERE name='utl_file_dir';

DIR

–

C:\Dir1

SQL> SHOW PARAMETER utl_file_dir

NAME TYPE VALUE

– – –

utl_file_dir string C:\Dir1

Значением параметра utl_file_dir может быть и символ *, что означает, что с помощью пакета UTL_FILE можно получить доступ к файлам в любых каталогах, к которым есть доступ у пользователя oracle операционной системы сервера. Выставление этого параметра таким способом обычно практикуется в тестовых инсталляциях и должно быть совершенно исключено в production по требованиям безопасности.

Директория (directory) – объект баз данных Oracle, являющийся псевдонимом каталога в файловой системе сервера. Директории создаются DDL-командами следующего вида:

SQL> CREATE OR REPLACE DIRECTORY dir1 AS 'C:\Dir1';

Directory created.

По сравнению с использованием параметра utl_file_dir, директории дают более гибкие возможности по управлению доступом к файлам. Так, после того как директория создана, администратор базы данных может предоставить конкретным пользователям привилегии только на чтение из нее:

SQL> GRANT READ ON DIRECTORY dir1 TO user1;

Grant succeeded.

Параметр utl_file_dir задает список каталогов сразу для всех пользователей, которые имеют привилегии на выполнение программ пакета UTL_FILE, причем настроить конкретные действия (чтение или запись) с файлами из этих каталогов с его помощью нельзя.

Использование 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), то существующее содержимое файла будет перезаписано на содержимое таблицы.