Oracle内建包UTL_FILE使用说明

在数据库的开发中，当PL/SQL语句很复杂的时候，我想写一些Log，就像java的Log4j那样。这样就可以很好的跟踪程序的运行情况。在网上找到的文章不是很好用，修改了一下。

1：在Oracle服务器上建立一个目录并指定权限（我这个是unix的。windows用c:/tmp之类的就行）

CREATE DIRECTORY TEMP AS '/tmp';
GRANT READ,WRITE ON DIRECTORY TEMP TO PUBLIC;
GRANT   EXECUTE ON SYS.UTL_FILE TO YOU_USER_NAME;

如果出现权限错误的话，注意自己登陆的方式，我是用oralce用户TelNet到Unix上的，然后connect / as sysdba。（windows 用户应该是：用sys用户登录到数据库(ora9i):conn sys/password@oraclesid as sysdba ，sys用户的缺省密码和system一样，都是manager）

2：写Log文件的SQL语句如下：

declare
file_handle utl_file.file_type;
buff varchar2(20);
cursor c1 is select acloumn from t_atable;
begin
file_handle := utl_file.fopen('TEMP',log.txt','w');
open c1;
loop
    fetch c1 into buff;
    exit when c1%notfound;
    utl_file.put_line(file_handle,buff);
   end loop;
   close c1;
   utl_file.fclose(file_handle);
end;

这样，就应该可以了，file_handle := utl_file.fopen('TEMP',log.txt','w');中的'w'是指write,也可以是'a',append.

封装后是这样的：

procedure writeLog(logMessage in Varchar2) is
begin
    IF logSwitch THEN
      file_handle := utl_file.fopen('TEMP','x.txt','w');
      utl_file.put_line(file_handle,logMessage);
      utl_file.fclose(file_handle);
    END IF;
end;

其中logSwitch 是boolean型，我定义在包中的。如果直接用的话可以是procedure writeLog(logMessage in Varchar2, logSwitch in boolean )

/************************************************************************************/

最近用到了Oracle的包UTL_FILE，网上却没找到关于它的函数，过程使用说明，虽然都不是很难的东西，但简单列出来，也能提高些效率。

于是有了这篇文。
以下翻译来自《Oracle Built-in Packages》的第六章，只翻译了部分，想了解的更详细，请参考原文。http://www.oreilly.com/catalog/oraclebip/chapter/ch06.html

FOPEN
IS_OPEN
GET_LINE
PUT
NEW_LINE
PUT_LINE
PUTF
FFLUSH
FCLOSE
FCLOSE_ALL

UTL_FILE.FOPEN 用法
FOPEN会打开指定文件并返回一个文件句柄用于操作文件。
所有PL/SQL版本:                 Oracle 8.0版及以上:
FUNCTION UTL_FILE.FOPEN (      FUNCTION UTL_FILE.FOPEN (
    location     IN VARCHAR2,      location     IN VARCHAR2,
    filename     IN VARCHAR2,      filename     IN VARCHAR2,
    open_mode    IN VARCHAR2)      open_mode    IN VARCHAR2,
RETURN file_type;                 max_linesize IN BINARY_INTEGER)
                                RETURN file_type;

参数

location
文件地址

filename
文件名

openmode
打开文件的模式（参见下面说明）

max_linesize
文件每行最大的字符数，包括换行符。最小为1，最大为32767

3种文件打开模式：
R 只读模式。一般配合UTL_FILE的GET_LINE来读文件。
W 写（替换）模式。文件的所有行会被删除。PUT, PUT_LINE, NEW_LINE, PUTF和FFLUSH都可使用
A 写（附加）模式。原文件的所有行会被保留。在最末尾行附加新行。PUT, PUT_LINE, NEW_LINE, PUTF和FFLUSH都可使用

打开文件时注意以下几点：
文件路径和文件名合起来必须表示操作系统中一个合法的文件。
文件路径必须存在并可访问；FOPEN并不会新建一个文件夹。
如果你想打开文件进行读操作，文件必须存在；如果你想打开文件进行写操作，文件不存在时，会新建一个文件。
如果你想打开文件进行附加操作，文件必须存在。A模式不同于W模式。文件不存在时，会抛出INVALID_OPERATION异常。

注: 我用A模式时文件不存在的话会自动创建, 当目录不存在的时候抛出UTL_FILE.INVALID_OPERATION异常 (linux系统, oracle 9i)

FOPEN 会抛出以下异常
UTL_FILE.INVALID_MODE
UTL_FILE.INVALID_OPERATION
UTL_FILE.INVALID_PATH
UTL_FILE.INVALID_MAXLINESIZE

UTL_FILE.IS_OPEN用法
如果文件句柄指定的文件已打开，返回TRUE，否则FALSE

FUNCTION UTL_FILE.IS_OPEN (file IN UTL_FILE.FILE_TYPE) RETURN BOOLEAN;

UTL_FILE只提供一个方法去读取数据：GET_LINE

UTL_FILE.GET_LINE用法
读取指定文件的一行到提供的缓存。
PROCEDURE UTL_FILE.GET_LINE
   (file IN UTL_FILE.FILE_TYPE,
    buffer OUT VARCHAR2);

file
由FOPEN返回的文件句柄

buffer
读取的一行数据的存放缓存

buffer必须足够大。否则，会抛出VALUE_ERROR 异常。行终止符不会被传进buffer。

异常
NO_DATA_FOUND
VALUE_ERROR
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.READ_ERROR

UTL_FILE.PUT用法
在当前行输出数据
PROCEDURE UTL_FILE.PUT
    (file IN UTL_FILE.FILE_TYPE,
    buffer OUT VARCHAR2);
file
由FOPEN返回的文件句柄
buffer
包含要写入文件的数据缓存；Oracle8.0.3及以上最大允许32kB，早期版本只有1023B

UTL_FILE.PUT输出数据时不会附加行终止符。

UTL_FILE.PUT会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR

UTL_FILE.NEW_LINE
在当前位置输出新行或行终止符，必须使用NEW_LINE来结束当前行，或者使用PUT_LINE输出带有行终止符的完整行数据。

PROCEDURE UTL_FILE.NEW_LINE
   (file IN UTL_FILE.FILE_TYPE,
    lines IN NATURAL := 1);
file
由FOPEN返回的文件句柄
lines
要插入的行数

如果不指定lines参数，NEW_LINE会使用默认值1，在当前行尾换行。如果要插入一个空白行，可以使用以下语句：
UTL_FILE.NEW_LINE (my_file, 2);
如果lines参数为0或负数，什么都不会写入文件。

NEW_LINE会产生以下异常
VALUE_ERROR
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR
例子
如果要在UTL_FILE.PUT后立刻换行，可以如下例所示：
PROCEDURE add_line (file_in IN UTL_FILE.FILE_TYPE, line_in IN VARCHAR2)
IS
BEGIN
   UTL_FILE.PUT (file_in, line_in);
   UTL_FILE.NEW_LINE (file_in);
END;

UTL_FILE.PUT_LINE
输出一个字符串以及一个与系统有关的行终止符
PROCEDURE UTL_FILE.PUT_LINE
    (file IN UTL_FILE.FILE_TYPE,
    buffer IN VARCHAR2);
file
由FOPEN返回的文件句柄
buffer
包含要写入文件的数据缓存；Oracle8.0.3及以上最大允许32kB，早期版本只有1023B
在调用UTL_FILE.PUT_LINE前，必须先打开文件。
UTL_FILE.PUT_LINE会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR

例子
这里利用UTL_FILE.PUT_LINE从表emp读取数据到文件：
PROCEDURE emp2file
IS
   fileID UTL_FILE.FILE_TYPE;
BEGIN
   fileID := UTL_FILE.FOPEN ('/tmp', 'emp.dat', 'W');

   /* Quick and dirty construction here! */
   FOR emprec IN (SELECT * FROM emp)
   LOOP
      UTL_FILE.PUT_LINE
         (TO_CHAR (emprec.empno) || ',' ||
          emprec.ename || ',' ||
          ...
          TO_CHAR (emprec.deptno));
   END LOOP;

   UTL_FILE.FCLOSE (fileID);
END;
PUT_LINE相当于PUT后加上NEW_LINE；也相当于PUTF的格式串"%s/n"。

UTL_FILE.PUTF
以一个模版样式输出至多5个字符串，类似C中的printf

PROCEDURE UTL_FILE.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);
file
由FOPEN返回的文件句柄
format
决定格式的格式串
argN
可选的5个参数，最多5个

格式串可使用以下样式
%s
在格式串中可以使用最多5个%s，与后面的5个参数一一对应
/n
换行符。在格式串中没有个数限制
%s会被后面的参数依次填充，如果没有足够的参数，%s会被忽视，不被写入文件

UTL_FILE.PUTF会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR

UTL_FILE.FFLUSH
确保所有数据写入文件。
PROCEDURE UTL_FILE.FFLUSH (file IN UTL_FILE.FILE_TYPE);
file
由FOPEN返回的文件句柄

操作系统可能会缓存数据来提高性能。因此可能调用put后，打开文件却看不到写入的数据。在关闭文件前要读取数据的话可以使用UTL_FILE.FFLUSH。
典型的使用方法包括分析执行进度和调试纪录。
UTL_FILE.FFLUSH会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR

UTL_FILE.FCLOSE
关闭文件
PROCEDURE UTL_FILE.FCLOSE (file IN OUT FILE_TYPE);
file
由FOPEN返回的文件句柄

注意file是一个IN OUT参数，因为在关闭文件后会设置为NULL
当试图关闭文件时有缓存数据未写入文件，会抛出WRITE_ERROR异常

UTL_FILE.FCLOSE会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.WRITE_ERROR

UTL_FILE.FCLOSE_ALL
关闭所有已打开的文件
PROCEDURE UTL_FILE.FCLOSE_ALL;

在结束程序时要确保所有打开的文件已关闭，可使用FCLOSE_ALL
也可以在EXCEPTION使用，当异常退出时，文件也会被关闭。
EXCEPTION
   WHEN OTHERS
  
THEN
      UTL_FILE.FCLOSE_ALL;
      ... other clean up activities ...
END;

注意：当使用FCLOSE_ALL关闭所有文件时，文件句柄并不会标记为NULL，使用IS_OPEN会返回TRUE。但是，那些关闭的文件不能执行读写操作（除非你再次打开文件）。
UTL_FILE.FCLOSE_ALL会产生以下异常
UTL_FILE.WRITE_ERROR

//***************************************************************************************************************

---------------------------------------------------------------------------------------------------写日志文件
--利用utl_file包，在此之前，要注意设置好utl_file_dir初始化参数

1. 以oracle用户登录，在oracle目录(或其子目录)下创建目录：$mkdir tgm_test
2. 创建数据库中的目录对象：create or replace directory TGM_LOG_DIR as '/home/oracle/tgm_test';

3. 以管理员用户登陆，如：sqlplus "/as sysdba";
4. 设置可操作目录，alter system set utl_file_dir='/home/oracle/tgm_test' scope=spfile;
5. 授权给指定用户，以便执行utl_file： GRANT EXECUTE ON utl_file TO train;

6. conn train/train 就可以正常使用utl_file了
7. exec write_log('this is a test!');

----3，4，5 似乎可以省略掉

------------------------------------write_log存储过程

CREATE OR REPLACE PROCEDURE write_log(text_context VARCHAR2) IS
file_handle utl_file.file_type;
write_content VARCHAR2(1024);
file_name VARCHAR2(50);
BEGIN
--open file
file_name := 'tgm.log';
file_handle := utl_file.fopen('TGM_LOG_DIR', file_name, 'w'); --写模式
write_content := to_char(SYSDATE, 'yyyy-mm-dd hh34:mi:ss') || '--->' || text_context;
--write file
IF utl_file.is_open(file_handle)
THEN
    dbms_output.put_line('is open!');
   utl_file.put_line(file_handle, write_content);
END IF;
--close file
utl_file.fclose(file_handle);
EXCEPTION
WHEN OTHERS THEN
   BEGIN
    IF utl_file.is_open(file_handle)
    THEN
     utl_file.fclose(file_handle);
    END IF;
   EXCEPTION
    WHEN OTHERS THEN
     NULL;
   END;
END write_log;