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 произошла нормально.
Для загрузки файлов в базу данных как 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.
В данном случае сначала строка таблицы с пустым LOB-объектом блокируется с помощью команды SELECT FOR UPDATE, а затем пакетная процедура DBMS_LOB.LOADFROMFILE осуществляет в него загрузку из файла.
Семантика SQL для LOB-объектов
Начиная с версии Oracle 9i, реализована поддержка семантики SQL для LOB-объектов. Это означает, что с BLOB и CLOB могут работать обычные встроенные функции как со значениями типов VARCHAR2 и CHAR (используются перегруженные версии встроенных функций):
SQL> CREATE TABLE clob_table (at1 CLOB);
Table created.
SQL> INSERT INTO clob_table VALUES ('I say :');
1 row created.
SQL> UPDATE clob_table SET at1 = 'Hello, world'||rpad(at1, 1000000, '!');
1 row updated.
SQL> SELECT LENGTH (at1) AS len, TO_CHAR (SUBSTR (at1, 1, 12)) AS words
2 FROM clob_table;
LEN WORDS
1000012 Hello, world
Столбец at1 типа CLOB при выполнении предложений UPDATE и SELECT передавался как параметр встроенным функциям определения длины строки LENGTH, выделения подстроки в строке SUBSTR и дополнения строки до заданной длинны RPAD.
Динамический SQL
Предложения SQL, которые не изменяются с момента компиляции программы PL/SQL, называются статическими. Статические предложения SQL формируются компилятором PL/SQL по объявлениям явных курсоров, по командам SELECT INTO и остальным DML-командам языка PL/SQL. После формирования они сохраняются в байт-коде хранимых программ PL/SQL и больше не изменяются.
Термином «динамический SQL» (dynamic SQL) называются предложения SQL, которые динамически формируются как символьные строки непосредственно во время выполнения программ PL/SQL. Эти предложения SQL в байт-коде отсутствуют, поэтому для из выполнения используются специальные механизмы PL/SQL, рассматриваемые далее.
Динамический SQL в PL/SQL в основном применяется для решения следующих задач:
выполнение DDL-команд (CREATE, ALTER, DROP);
поддержка нерегламентированных SQL-запросов (SQL ad hoc queries).
«Создать табличку» в программе PL/SQL нельзя:
SQL> BEGIN
2 CREATE TABLE tab1(at1 INTEGER);
3 END;
4 /
CREATE TABLE tab1(at1 INTEGER);
*
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00103: Encountered the symbol "CREATE" when expecting
one of the following: (begin case declare exit for goto if loop pragma
По префиксу PLS видно, что ошибку выдал компилятор PL/SQL, а из текста сообщения следует, что она произошла на этапе синтаксического анализа кода программы. Даже в грамматике языка PL/SQL не предусмотрено наличие в коде PL/SQL команд, похожих на DDL-команды CREATE.
«, но если очень хочется, то можно» для это следует использовать динамический SQL, передавая DDL-команду как символьную строку: