一、说明 UTL_FILE是oracle提供的一个标准的工具包，用来读写文件使用。 二、路径 包UTL_FILE用于读写操作系统的文件，前提是首先创建路径(Directory)并授权。ORACLE目录的作用就是让ORACLE数据库和操作系统之前进行文件的交互。 为了创建目录，必须具有DBA角色或者赋予了CREATE ANY DIRECTORY权限。如果普通用户被赋予了CREATE ANY DIRECTORY权限，那么用户就自动具备目录的READ和WRITE权限 创建：CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name' ;
create or replace directory DB_OUT as '/usr/local/xilen/DB_OUT'; 赋权：GRANT READ[,WRITE] ON DIRECTORY directory_namne TO user_name;
--路径授权，添加对路径读、写权限 grant read,write on directory DB_OUT to xilen; --utl_file包授权，添加执行权限 GRANT EXECUTE ON utl_file TO xilen; 查看（具有READ和WRITE权限的目录）： -- 限制单列的显示长度 COL DIRECTORY_PATH format a60; -- 查询路径 SELECT DIRECTORY_NAME, DIRECTORY_PATH, GRANTEE, PRIVILEGE FROM USER_TAB_PRIVS T, ALL_DIRECTORIES D WHERE T.TABLE_NAME(+)=D.DIRECTORY_NAME; 三、详解 1、FILE_TYPE 该类型是UTL_FILE包中定义的记录类型，其成员是私有的，不能够被直接引用。该类型的定义如下： TYPE file_type IS RECORD( id BINARY_INTEGER, datatype BINARY_INTEGER ); 2、FOPEN 该函数用于打开文件。使用这个函数最多可以打开50个文件，语法如下： UTL_FILE.FOPEN( location IN VARCHAR2, -- 文件路径（必须要使用DIRECTORY对象，并且其名称必须为大写） filename IN VARCHAR2, -- 文件名 open_mode IN VARCHAR2, -- 打开模式 max_linesize IN BINARY_INTEGER -- 指定文件每行存放的最大字符数 ) RETURN file_type; -- 预操作文件的指针(句柄) 注：文件的打开模式包含'r', 'w', 'a' 'rb', 'wb', 'ab'六种： --'r'：读文件(文本)，一定要保证有该文件，不然会报UTL_FILE.INVALID_PATH异常 --'w'：写文件(文本)，没有该文件的话会自动添加；有的话会覆盖 --'a'：追加文件(文本)，如果文件不存在，则会以write模式创建此文件 --带有'b'后缀的为使用byte(字节)模式，BLOB与VARCHAR2不一样，BLOB打开时一定要用带有'b'后缀的模式
3、IS_OPEN 该函数用于确定文件是否已经被打开，语法如下：
UTL_FILE.IS_OPEN( file IN FILE_TYPE -- 指定文件的句柄 ) RETURN BOOLEAN; -- 如果已经打开则返回TRUE，否则返回FALSE 该函数的示例如下： DECLARE HANDLE UTL_FILE.FILE_TYPE; BEGIN IF NOT UTL_FILE.is_open(HANDLE) THEN HANDLE := UTL_FILE.fopen('CZW','DYWT.TXT','R',1000); END IF; DBMS_OUTPUT.PUT_LINE('D:\DYWT.TXT已经被打开'); END; 4、FCLOSE 该过程用于关闭已经打开的文件，语法如下： UTL_FILE.FCLOSE(FILE IN OUT FILE_TYPE); 5、FCLOSE_ALL 该过程用于关闭当前打开的所有文件。语法如下： UTL_FILE.FCLOSE_ALL; 6、GET_LINE 该过程用于从已经打开的文件中读取行内容，行内容会被读取到输出缓冲区。语法如下： UTL_FILE.GET_LINE( file IN FILE_TYPE, -- 文件句柄 buffer OUT VARCHAR2, -- 存储读取的Buff linesize IN NUMBER, -- 读取的最大字节数 len IN PLS_INTEGER DEFAULT NULL -- 实际读取长度 ); 使用该过程的示例如下： DECLARE HANDLE UTL_FILE.FILE_TYPE; BUFFER VARCHAR2(100); BEGIN IF NOT UTL_FILE.is_open(HANDLE) THEN HANDLE := UTL_FILE.fopen('CZW','DYWT.TXT','R',1000); END IF; UTL_FILE.GET_LINE(HANDLE,BUFFER,100); DBMS_OUTPUT.PUT_LINE(BUFFER); UTL_FILE.fclose(HANDLE); END; 7、GET_RAW 该过程用于从文件中读取RAW字符串，并调节文件指针到读取位置。语法如下： UTL_FILE.GET_RAW( fid IN UTL_FILE.TYPE, -- 文件句柄 r OUT NOCOPY RAW, -- 读取的缓存Buff len IN PLS_INTEGER DEFAULT NULL -- 读取长度 ); 上面所示：fid用于指定文件的句柄，r用于取得读取信息，示例如下： DECLARE HANDLE UTL_FILE.FILE_TYPE; BUFFER VARCHAR2(2000); BEGIN IF NOT UTL_FILE.is_open(HANDLE) THEN HANDLE := UTL_FILE.fopen('CZW','DYWT.TXT','R',1000); END IF; UTL_FILE.GET_RAW(HANDLE,BUFFER,1000); DBMS_OUTPUT.PUT_LINE(BUFFER); UTL_FILE.fclose(HANDLE); END; 8、PUT该过程用于将缓冲区内容写入到文件中。当使用PUT过程的时候，文件必须以写方式打开，在写入缓冲区之后，如果要结束行，那么可以使用NEW_LINE过程。语法如下：
UTL_FILE.PUT( file IN FILE_TYPE, buffer IN VARCHAR2 ); 使用该过程的示例如下： DECLARE HANDLE UTL_FILE.FILE_TYPE; BUFFER VARCHAR2(2000); BEGIN IF NOT UTL_FILE.IS_OPEN(HANDLE) THEN HANDLE := UTL_FILE.FOPEN('CZW','NEW.TXT','W',1000); END IF; BUFFER:='&CONTENT1'; UTL_FILE.PUT(HANDLE,BUFFER); UTL_FILE.NEW_LINE(HANDLE); BUFFER:='&CONTENT2'; UTL_FILE.PUT(HANDLE,BUFFER); UTL_FILE.NEW_LINE(HANDLE); UTL_FILE.FCLOSE(HANDLE); END; 9、PUT_RAW 该过程用于将RAW缓冲区中的数据写入文件中。语法如下： UTL_FILE.PUT_RAW( fid IN UTL_FILE.FILE_TYPE, R IN RAW, autoflush IN BOOLEAN DEFAULT FALSE ); 上述,fid用于指定文件句柄,r用于指定存放RAW数据的缓冲区,autoflush用于指定是否自动刷新缓冲区数据.该过程的示例如下: DECLARE HANDLE UTL_FILE.FILE_TYPE; BUFFER VARCHAR2(2000); BEGIN IF NOT UTL_FILE.IS_OPEN(HANDLE) THEN HANDLE := UTL_FILE.FOPEN('CZW','NEW.TXT','W',1000); END IF; BUFFER:='&CONTENT1'; UTL_FILE.PUT_RAW(HANDLE,BUFFER); UTL_FILE.NEW_LINE(HANDLE); UTL_FILE.FCLOSE(HANDLE); END; 10、NEW_LINE 该过程用于为文件增加行终止符，语法如下： UTL_FILE.NEW_LINE( file IN FILE_TYPE, lines IN NATURAL :=1 -- 指定在文件中增加的行终止符的个数 ); 11、PUT_LINE
该过程用于将文本缓冲区内容写入到文件中。当使用该过程为文件追加内容时，会自动在文件的尾部追加行终止符。
UTL_FILE.PUT_LINE ( file IN FILE_TYPE, buffer IN VARCHAR2, autoflush IN BOOLEAN DEFAULT FALSE ); 12、FFLUSH 该过程用于将数据强制性写入到文件中，正常情况下，当给文件写入数据的时候，数据会被暂时的放到缓存中。过程FFLUSH用于强制将数据写入到文件中。语法如下： UTL_FILE.FFLUSH(file IN FILE_TYPE):
13、FREMOVE
该过程用于删除磁盘文件。语法如下： UTL_FILE.FREMOVE( location IN VARCHAR2, -- 指定DIRECTORY，注意，这里也必须要大写 filename IN VARCHAR2 -- 指定要删除的文件名 ); 14、FCOPY 该过程用于将源文件的全部或者部分内容复制到目标文件中。当使用该过程的时候，如果不设起始行和结束行，则将复制文件所有的内容。语法如下： UTL_FILE.FCOPY( location IN VARCHAR2, -- 指定源DIRECTORY路径对象 filename IN VARCHAR2, -- 指定文件名 dest_dir IN VARCHAR2, -- 希望复制到的Directory路径对象 dest_file IN VARCHAR2, -- 复制后的文件名 start_line IN PLS_INTEGER DEFAULT 1, -- 指定起始行号 end_line IN PLS_INTEGER DEFALUT NULL -- 指定结束行号 ); 15、FRENAME 该过程用于修改已经存在的文件名字，其作用于UNIX的MV命令完全相同，在修改文件名字的时候，通过指定overwrite参数可以覆盖已经存在的文件。语法如下： UTL_FILE.FRENAME( location IN VARCHAR2， filename IN VARCHAR2， dest_dir IN VARCHAR2， dest_file IN VARCHAR2， overwrite IN BOOLEAN DEFAULT FALSE -- 是否覆盖已经存在的文件 ); 四、示例 导出表数据示例： DECLARE HANDLE UTL_FILE.FILE_TYPE; BEGIN HANDLE := UTL_FILE.FOPEN('CZW_DIR','DEPT.TXT','W',1000); FOR I IN (SELECT T.DEPTNO||','||T.DNAME||','||T.LOC AS MSG FROM SCOTT.DEPT T) LOOP UTL_FILE.PUT_LINE(HANDLE,I.MSG); END LOOP; UTL_FILE.FFLUSH(HANDLE); UTL_FILE.FCLOSE(HANDLE); END;
五、参考
http://blog.csdn.net/bisal/article/details/24667609
http://zhangzhongjie.iteye.com/blog/1903024
http://www.2cto.com/database/201301/181232.html
http://blog.csdn.net/john2522/article/details/8124087
