Пакет используется для чтения и создания текстовых файлов (не двоичных) в файловой системе сервера баз данных (не клиентского компьютера). Его использование весьма полезно при загрузке и выгрузке данных. Я его очень часто использую для отладки (рекомендую).

• Основные процедуры и функции
• Некоторые замечания
• Примеры использования
• Спецификация пакета

Основные процедуры и функции пакета: fopen (открывает входной или выходной файл, а также возвращает дескриптор, который используется при обращении к другим процедурами пакета), is_open (проверяет, открыт ли файл), fclose (закрывает файл), fclose_all (закрывает все открытые файлы), get_line (читает строку из файла), put (записывает строку в файл), new_line (записывает код конца строки), put_line (записывает строку с символом конца строки), putf (записывает форматированный текст), fflush (вызывает физическую запись буферизированных данных).

Некоторые важные замечания:

1. Физическая запись производится после заполнения буфера ввода/вывода, или вызова FFLUSH, или закрытия файла.
2. Возможен доступ нескольких сеансов к одному и тому же файлу. В этом случае необходимо использовать блокировки (см. соответствующую статью о пакете dbms_lock).
3. Установка параметра инициализации (init.ora) UTL_FILE_DIR. Указывая перечень папок, куда пакет может записывать, мы уберегаем себя от возможной перезаписи файлов системы. Задать список папок можно через запятую. Обратите внимание на то, что завершающая обратная косая (\) в значении этого параметра вызовет ошибку при загрузке сервера. Это вызвано тем, что символ \ является управляющим (позволяет продолжить длинную запись на следующую строку), если стоит последним в строке файла параметра инициализации. Поэтому для того, чтобы избежать конкатенации строк, нужно указывать две обратных косых подряд. И ещё, как папка указана в этом параметре (большие, маленькие буквы), так нужно её использовать и в функции fopen. Начиная с версии 9.0.2, пакет имеет доступ к папкам, которые созданы как объекты directory в базе данных. Например, create directory LOAD_TXT as ‘/tmp/load’.
4. Доступ к сетевым дискам. Для ОС UNIX проблемы доступа к дискам других компьютеров не существует – диски монтируются и становятся атрибутами системы, а не конкретного сеанса. Для Windows все не так просто. У каждого пользовательского сеанса есть свой набор доступных сетевых дисков. Но Oracle этих дисков может и не видеть. А причина в том, что учетная запись localSYSTEM, от имени которой работает сервер ORACLE , по умолчанию не может обращаться к сетевым дискам. Для того, чтобы получить возможность работать с сетевыми ресурсами, зайдите в control panel-administrative tools-services. Выберите OracleService. Откройте properties на закладке Log On . Выберите переключатель This Account и укажите соответствующие данные для регистрации в домене. После этого значение параметра можно указывать в виде \\имя_машины\имя _ресурса\путь или нужно чтобы пользователь, от имени которого запускается оракл, настроил необходимый диск.
5. Обработка исключительных ситуаций – при возникновении ошибок пакет создает исключительную ситуацию. Они определены в спецификации пакета

   file_open charsetmismatch invalid_path invalid_mode invalid_filehandle invalid_operation read_error write_error internal_error invalid_maxlinesize invalid_filename access_denied invalid_offset delete_failed rename_failed 
   

   Если вы не напишете обработчик исключительных ситуаций, перехватывая их по названию, то получите абсолютно неинформативное сообщение об ошибке.

6. ограничение – 1023 байта. Начиная с версии 8.1.7, такое ограничение отсутствует – в процедуре fopen последним аргументом является максимальная длина вводимой строки (до 32 Кбайт).
7. получение списка файлов каталога – это возможность не предусмотрена в пакете, хотя она крайне нужна, если нужно обработать все файлы, которые находятся в вашей папке. В этой ситуации спасает java и Том Кайт. Он предлагает следующий вариант
   • создать табличку DIR_LIST в своей схеме с одним поле filename
   • создать процедуру на языке JAVA . Например такую

     CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "DirList"
     AS import java.io.*;
      import java.sql.*;
     
      public class DirList
      {
      public static void getList(String directory) throws SQLException
      {File path = new File(directory);
      String[] list = path.list();
      String element;
      for(int i = 0; i < list.length; i++)
      {
      element = list[i];
      #sql { INSERT INTO DIR_LIST (FILENAME) VALUES (:element) };
      } 
      }
      }
     /

     создать процедуру сопоставления, для связи pl/sql и java. Например, такую

      create or replace PROCEDURE GET_DIR_LIST(p_directory in varchar2)
     as language java name 'DirList.getList(java.lang.String)'; 
     

     и все. Теперь после вызова exec get_dir_list(‘\tmp’) во временной таблице DIR_LIST мы получаем перечень всех файлов указанной папки.

А теперь немного кода, чтобы было явно видно, как использовать пакет, например, для вывода информации.

Пример 1

ft  utl_file.file_type;
buffer  varchar2(1000);
type ct is ref cursor;
v ct;
BEGIN
ft:=utl_file.fopen('LOAD_TXT’,’MY_OUTPUT.TXT’,'w');
utl_file.put_line(ft,’ПРОБА’);

open v for 'SELECT * FROM PROBA’

   loop
        fetch v into buffer;
        exit when v%notfound;
       utl_file.put_line(ft,buffer);
    end loop;
    close v;
utl_file.fclose(ft);
END 

Думаю, что все ясно. Если этот скрипт не срабатывает, то разберитесь с правами в ос на папку, куда вы хотите писать и все будет ок.

Пример 2.А так я тестирую от имени конкретного юзера, может ли он работать с указанной директорией. Особенно часто этот скрипт использую для сервера, где установлен Linux (у него есть заморочки с правами).

declare
ft  utl_file.file_type;
buffer varchar2(5);
BEGIN
Buffer:=’1456’;
ft:=utl_file.fopen('load_dir','my_file','W');
utl_file.put_line(ft,buffer);
utl_file.fCLOSE(ft);
end;

Спецификация пакета:

FUNCTION fopen(location     IN VARCHAR2,
                 filename     IN VARCHAR2,
                 open_mode    IN VARCHAR2,
                 max_linesize IN BINARY_INTEGER DEFAULT NULL)

FUNCTION is_open(file IN file_type) RETURN BOOLEAN;

PROCEDURE fclose_all;

PROCEDURE get_line(file   IN file_type,
                     buffer OUT VARCHAR2,
                     len    IN BINARY_INTEGER DEFAULT NULL);

PROCEDURE put(file   IN file_type,
                buffer IN VARCHAR2);

PROCEDURE put_line(file   IN file_type,
                     buffer IN VARCHAR2,
                     autoflush IN BOOLEAN DEFAULT FALSE);

procedure putf(file   IN file_type,
                 format IN VARCHAR2,
                 arg1   IN VARCHAR2 DEFAULT NULL,
                 arg2   IN VARCHAR2 DEFAULT NULL,
                 arg3   IN VARCHAR2 DEFAULT NULL,
                 arg4   IN VARCHAR2 DEFAULT NULL,
                 arg5   IN VARCHAR2 DEFAULT NULL);

location - это directory ( алиас папки, где будет находиться файл )

filename – имя файла

open_mode – режим в котором открывается файл: r – чтение, w – запись, a – добавление

buffer – переменная куда считывается или откуда считывается текст