Стандартные пакеты. UTL_FILE

dbstalker, 20 июня

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

Основные процедуры и функции пакета: 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 – переменная куда считывается или откуда считывается текст

ЦИТАТА:

Есть еще одна установка параметра utl_file_dir, замечательная для хакеров. В этом параметре можно устанавливать "*", и мы видели такую установку несколько раз (сделанную либо по причине лени, либо во время тестирования без последующего изменения). Данное значение показывает, что пакет UTL_FILE может использоваться для записи в любой каталог системы, в который пользователь oracle имеет право записи, – ВЕЛИКОЛЕПНО!!! Достаточно часто в базах данных значение параметра utl_file_dir совпадает со значением параметра user_dump_dest. В таком случае вы получаете возможность чтения трассировочных файлов, к которым обычно не имеете доступа. Ниже приведен простой фрагмент кода, который может быть изменен для поиска конкретных предложений и слов.

6 комментариев

Прокоментировать

Anonymous
4 февраля 2010 г. в 15:30

Господи, какие Вы все умные, а я не могу заполнить поле "Имя", чтобы зарегистрироваться на ФОРУМ "Налоги и бизнес". Пишу свой email - не правильно, login - тоже неправильно. Может, мне на этом форуме делать нечего? А я так хотела узнать про подоходный налог!

dbstalker
4 февраля 2010 г. в 17:25

спасибо, постараемся как можно скорее найти ошибку.
Кстати, на форуме forum.dobusiness.com.ua задавать вопросы можно без регистрации

ladjack
4 марта 2010 г. в 17:52

Dbstalker, спасибо за метод с java процедурой помогающий определить файлы в каталоге. Ощутимо полезная вещь.
Все сделал по предложенной методе. Но к сожалению, при вызове процедуры получаю ошибку на отсутствие класса (ORA-29540: class DIRLIST does not exist -> details *class string does not exist*).
Причина может быть в настройках для java на сервере?

ladjack
4 марта 2010 г. в 18:51

Отзываю вопрос, вроде разобрался - пересоздал процедуру вызова скрипта, вылезли запреты в java.io.FilePermission на чтение из папки на которую у меня грант (хотя разрешены и w/r). Тут уже все понятно, прямая дорога к DBA...

Goga
29 сентября 2011 г. в 16:26

скажи а с помощью этой штуки можно сформироваться txt файл с позиционированием значений, полученных из БД oracle, или лучше какую-то другую софтину использовать?

dbstalker
4 октября 2011 г. в 16:56

Можно. Дерзайте.

 

Новый комментарий

Я не спамер: введите суму 0+4



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Нужен поставщик Дропшиппинг
10 декабря, 1 ответа
КИНО КАФЕ!!!!!!!!!!!!!!!!
10 декабря, 1 ответа