Запись данных из программы в файл и обратно

Для этой цели можно воспользоваться системным пакетом UTL_FILE. Его интерфейсная (внешняя описательная) часть включает описания

 

- процедур и функций (открытие, закрытие, чтение, запись)

- типа FILE_TYPE для файловой переменной (file handle).

- исключительных состояний

INVALID_PATH

INVALID_MODE (неправильно указан режим открытия в FOPEN)

INVALID_FILEHANDLE

INVALID_OPERATION

READ_ERROR

WRITE_ERROR

INTERNAL_ERROR (внутренняя ошибка PL/SQL)

 

Средствами пакета можно работать только с файлами, находящимися на сервере, и расположенными в каталоге, указанном параметром СУБД UTL_FILE_DIR (если этот параметр не выставлен, пакет не сможет ничего сделать). Значение UTL_FILE_DIR = * в INIT.ORA позволить иметь доступ из пакета к любому каталогу на сервере.

 

Пример выдачи в файл перечня сотрудников с зарплатой:

 

DECLARE

           myfile UTL_FILE.FILE_TYPE;

BEGIN

           myfile:= UTL_FILE.FOPEN ('c:\crs', 'utlfile.lst', 'w');

 

           FOR dname_rec IN (SELECT ename, sal FROM emp)

           LOOP

                           UTL_FILE.PUT (myfile, RPAD (dname_rec.ename, 15));

                           UTL_FILE.PUT (myfile, LPAD (dname_rec.sal, 6));

                           UTL_FILE.NEW_LINE (myfile);

           END LOOP;

 

           UTL_FILE.FCLOSE (myfile);

 

EXCEPTION

           WHEN UTL_FILE.INVALID_PATH THEN

                           DBMS_OUTPUT.PUT_LINE ('Invalid file name or path');

           WHEN OTHERS THEN

                           DBMS_OUTPUT.PUT_LINE ('Unrecognized error');

END;

/

 

Другие применения пакета, помимо записи в файл, включают возможность программно

 

(а) создавать файлы

(б) проверять наличие файла

(в) читать из файла в базу. Однако, если такое чтение не обязательно выполнять в рамках программы, то эффективнее его можно выполнять штатным загрузчиком SQL*Loader.

 

Ограничения пакета:

 

- Невозможно записывать или читать строки длиною более 32767 байтов (версия 8.1.5)

- Пакетом невозможно удалить файл – его можно только обнулить [*]

- Невозможно переименовать файл – его можно только скопировать в другой [*]

- Обеспечивается только последовательный доступ к файлу

- Нельзя работать со ссылками на файл – только с реальными файлами [*]

 

Ограничения, помеченные [*], справедливы только до версии 9.2, в которой функциональность пакета получила заметное расширение.

 

Начиная с версии 9.2 введен другой контроль доступа к файлам на сервере, посредством DIRECTORY. Вот как можно организовать работу с файлами без установки UTL_FILE_DIR:

 

CONNECT / as sysdba
CREATE OR REPLACE DIRECTORY courses_dir AS 'c:\crs';
GRANT READ ON DIRECTORY courses_dir TO scott;

 

CONNECT scott/tiger

SET SERVEROUTPUT ON

DECLARE fexists BOOLEAN; flength NUMBER; fblocksize NUMBER;
BEGIN
UTL_FILE.FGETATTR ('COURSES_DIR', 'plsql.doc', fexists, flength, fblocksize);
IF fexists THEN
DBMS_OUTPUT.PUT_LINE ('File length is: ' || flength);
ELSE
DBMS_OUTPUT.PUT_LINE ('File not found.');
END IF;
END;
/

 

 

Шифрование данных

Несмотря на механизм разграничения доступа к данным в Oracle, пользователь SYS обладает достаточными полномочиями, чтобы суметь обратиться к любым данным любого пользователя в БД. Для того, чтобы обезопаситься от этого, а также случайного доступа со стороны других пользователей, особо важные данные можно перед помещением в базу шифровать.

 

 

19.2.2.1. Пакет DBMS_OBFUSCATION_TOOLKIT

Начиная с версии 8.1.6 с Oracle поставляется пакет для шифрования и расшифровки методом DES под названием DBMS_OBFUSCATION_TOOLKIT. Процедурой DESENCRYPT этого пакета можно с помощью ключа зашифровать текстовую строку, а процедурой DESDECRYPT расшифровать.

 

Пример в SQL*Plus:

 

DECLARE

x VARCHAR2 (255):= ' Morgen, morgen, nur nicht heute, '

               || 'sagen alles faulen Leute ';

my_data VARCHAR2 (255);

BEGIN

DBMS_OUTPUT.PUT_LINE (x);

 

my_data:= RPAD (x, (TRUNC(LENGTH(x) / 8) + 1) * 8, CHR (0));

DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT

      (

        input_string => my_data

        , key_string  => 'MagicKey'

     , encrypted_string => x

     );

DBMS_OUTPUT.PUT_LINE (x);

 

DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT

      (

        input_string => x

     , key_string  => 'MagicKey'

        , decrypted_string => my_data

     );

x:= RTRIM (my_data, CHR(0));

DBMS_OUTPUT.PUT_LINE (x);

END;

/

 

Сценарий заведения пакета в БД находится в rdbms\ admin\ catobtk. sql.

 

 

19.2.2.2. Пакет DBMS_CRYPTO

В версии 10 в состав системных пакетов включен (в перспективе – на замену DBMS_OBFUSCATION_TOOLKIT) более функциональный пакет DBMS_CRYPTO, позволяющий шифровать данные других типов (не RAW и VARCHAR2, а RAW, CLOB и BLOB) и другими алгоритмами (не только DES, 3DES, но еще и AES, и RC4, и 3DES_2KEY плюс алгоритмы хеширования плюс параметризация этих алгоритмов). Он построен по иной технике, когда для шифрования используются всего две общие функции ENCRYPT и DECRYPT, а ссылка на алгоритм шифрования передается параметром. Вот как с помощью DBMS_CRYPTO может выглядеть DES-шифрование той же строки тем же ключом, что и выше:

 

DECLARE

input_string VARCHAR2(255):= 'Morgen, morgen, nur nicht heute';

raw_input   RAW(4000);

 

key_string  VARCHAR2(8):= 'MagicKey';

raw_key     RAW(16);

 

encrypted_raw RAW(4000);

encrypted_string VARCHAR2(4000);

 

decrypted_raw RAW(4000);

decrypted_string VARCHAR2(4000);

 

BEGIN

DBMS_OUTPUT.PUT_LINE(input_string);

 

raw_input:= UTL_I18N.STRING_TO_RAW (input_string, 'AL32UTF8');

 

raw_key:= UTL_RAW.CAST_TO_RAW(CONVERT(key_string, 'AL32UTF8'));

 

encrypted_raw:= DBMS_CRYPTO.ENCRYPT

            (

              TYP => DBMS_CRYPTO.DES_CBC_PKCS5

           , SRC => raw_input

           , KEY => raw_key

           );

 

decrypted_raw:= DBMS_CRYPTO.DECRYPT

            (

              TYP => DBMS_CRYPTO.DES_CBC_PKCS5

           , SRC => encrypted_raw

           , KEY => raw_key

           );

 

decrypted_string:= UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');

 

DBMS_OUTPUT.PUT_LINE(decrypted_string);

END;

/

 

(Чтобы пример проработал, возможно потребуется выполнить от имени SYS:

 

GRANT EXECUTE ON DBMS_CRYPTO TO PUBLIC;)

 

Здесь технология не требует искусственного удлиннения строки до кратности 8 символам, но зато необходимо предъявлять параметры в формате RAW и в кодировке AL32UTF8.

 

Константа DBMS_CRYPTO.DES_CBC_PKCS5 выше есть сумма трех констант ENCRYPT_DES (алгоритм шифрования), CHAIN_CBC (размер блоков, на которые в процессе шифрования будет разбиваться исходная строка) и PAD_PKCS5 (схема дополнения строки до требуемой кратности). Чтобы в точности воспроизвести пример с DBMS_OBFUSCATION_TOOLKIT, нужно будет дополнять шифруемую строку нулями, и тогда вместо

 

TYP => DBMS_CRYPTO.DES_CBC_ PKCS5

 

указать

 

TYP => DBMS_CRYPTO.ENCRYPT_DES

+ DBMS_CRYPTO.CHAIN_CBC

+ DBMS_CRYPTO. PAD_ZERO

 

Упражнение. Проверить это, зашифровав строку процедурой DESENCRYPT пакета DBMS_OBFUSCATION_TOOLKIT, а расшифровав функцией DECRYPT пакета DBMS_CRYPTO.

 

Замечание. Современная технология не рекомендует на практике дополнять строку нулями, а пользоваться схемой PKCS5.

 

Пример указания алгоритма шифрования AES ключом в 128 разрядов:

 

TYP =>DBMS_CRYPTO.ENCRYPT_ AES128

+ DBMS_CRYPTO.CHAIN_CBC

+ DBMS_CRYPTO.PAD_PKCS5

 

Сценарий заведения пакета в БД находится в rdbms\admin\cato ctk.sql.