在PL/SQL中，UTL_FILE包提供了文本文件输入和输出互功能。也就是说我们可以通过该包实现从操作系统级别来实现文件读取输入或者是写入到操作系统文件。通过该包也可以将其他系统的数据加载到数据库中。如加载web服务器日志，用户登录数据库日志乃至Oracle日志文件等等。本文主要描述了UTL_FILE的功能以及通过实例演示并理解这个包下相关过程函数的用法。
1、UTL_FILE介绍
a、实现基于操作系统级别的读取与写入功能
b、该方式为基于服务器端的文本文件访问模式，不支持二进制文件
c、可以通过设置参数utl_file_dir来设置pl/sql访问操作系统文件的多个路径
d、所有用户可以读写utl_file_dir参数设定的目录，因此应考虑安全问题
e、也可以将参数utl_file_dir置空，而通过创建directory以及授予对directory权限来进行访问os文件(推荐方式)
2、UTL_FILE包中的过程和函数
a、UTL_FILE中定义的file_type为记录类型，如下所示其成员是私有的，不能够被直接引用或改变这个记录的组件。
TYPE file_type IS RECORD (
id BINARY_INTEGER,
datatype BINARY_INTEGER,
byte_mode BOOLEAN);
b、UTL_FILE中相关过程函数的功能说明
FCLOSE Procedure Closes a file
FCLOSE_ALL Procedure Closes all open file handles
FCOPY Procedure Copies a contiguous portion of a file to a newly created file
FFLUSH Procedure Physically writes all pending output to a file
FGETATTR Procedure Reads and returns the attributes of a disk file
FGETPOS Function Returns the current relative offset position within a file, in bytes
FOPEN Function Opens a file for input or output
FOPEN_NCHAR Function Opens a file in Unicode for input or output
FREMOVE Procedure Deletes a disk file, assuming that you have sufficient privileges
FRENAME Procedure Renames an existing file to a new name, similar to the UNIX mv function
FSEEK Procedure Adjusts the file pointer forward or backward within the file by the number of bytes specified
GET_LINE Procedure Reads text from an open file
GET_LINE_NCHAR Procedure Reads text in Unicode from an open file
GET_RAW Procedure Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read
IS_OPEN Function Determines if a file handle refers to an open file
NEW_LINE Procedure Writes one or more operating system-specific line terminators to a file
PUT Procedure Writes a string to a file
PUT_LINE Procedure Writes a line to a file, and so appends an operating system-specific line terminator
PUT_LINE_NCHAR Procedure Writes a Unicode line to a file
PUT_NCHAR Procedure Writes a Unicode string to a file
PUTF Procedure A PUT procedure with formatting
PUTF_NCHAR Procedure A PUT_NCHAR procedure with formatting, and writes a Unicode string to a file, with formatting
PUT_RAW Procedure Accepts as input a RAW data value and writes the value to the output buffer
3、演示ULT_FILE用法
a、使用UTL_FILE的主要步骤(使用directory方式)
--先创建用于存放os文件的目录
scott@USBO> ho mkdir -p /u03/database/usbo/db_utl_dir
--在数据库层面添加directory
scott@USBO> create directory db_utl_dir as '/u03/database/usbo/db_utl_dir';
--权限授予
scott@USBO> grant read,write on directory db_utl_dir to public;
b、从SQL查询写入到数据文件
DECLARE
vsfile UTL_FILE.file_type; --->定义用于接收文件句柄的类型
v_cnt PLS_INTEGER := 0;
BEGIN
vsfile :=
UTL_FILE.fopen ('DB_UTL_DIR', --->使用fopen打开文件，定义了文件路径，文件名，读写方式以及每一行字符的最大长度，缺省为1024
'emp.txt',
'W',
200);
FOR i IN (SELECT t.ename || ',' || t.job AS msg --->使用了一个for循环来读取scott.emp表
FROM scott.emp t WHERE t.sal>2000)
LOOP
UTL_FILE.put_line (vsfile, i.msg); --->将for循环查询的内容使用put_line写入到文件
v_cnt := v_cnt + 1; --->计数器，用于统计写入的记录数
END LOOP;
UTL_FILE.fflush (vsfile);
UTL_FILE.fclose (vsfile);
DBMS_OUTPUT.put_line (v_cnt || ' rows unloaded');
END;
/
6 rows unloaded
PL/SQL procedure successfully completed.
--查看产生的文件
scott@USBO> ho more /u03/database/usbo/db_utl_dir/emp.txt
JONES,MANAGER
BLAKE,MANAGER
CLARK,MANAGER
SCOTT,ANALYST
KING,PRESIDENT
FORD,ANALYST
c、从数据文件读入并写入到表
scott@USBO> create table tb_emp(val varchar2(30), file_name varchar2(10));
scott@USBO> exec read_demo('emp.txt','db_utl_dir'); -->调用过程来实现，代码见文章尾部
PL/SQL procedure successfully completed.
scott@USBO> select * from tb_emp;
VAL FILE_NAME
----------------------------- ---------------------
JONES,MANAGER emp.txt
BLAKE,MANAGER emp.txt
CLARK,MANAGER emp.txt
SCOTT,ANALYST emp.txt
KING,PRESIDENT emp.txt
FORD,ANALYST emp.txt
6 rows selected.
d、读写混合模式示例
scott@USBO> set serveroutput on;
scott@USBO> exec rw_demo; -->调用过程来实现，代码见文章尾部
14
14
28
42
56
71
84
PL/SQL procedure successfully completed.
scott@USBO> ho ls
out.txt x.txt
scott@USBO> ho more out.txt
JONES,MANAGER
JONES,MANAGER
BLAKE,MANAGER
CLARK,MANAGER
SCOTT,ANALYST
KING,PRESIDENT
FORD,ANALYST
e、演示中用到的过程
--下面是读模式的过程代码
CREATE OR REPLACE PROCEDURE read_demo (file_name_in VARCHAR2, utl_dir_in VARCHAR2)
--两个传入参数，一个用于指定文件名，一个用于指定utl_file_dir目录
--Author : Leshami
--Blog : http://blog.csdn.net/leshami
IS
vsfile UTL_FILE.file_type;
vnewline VARCHAR2 (200);
v_utl_dir VARCHAR2 (30);
BEGIN
v_utl_dir := UPPER (utl_dir_in);
vsfile := UTL_FILE.fopen (v_utl_dir, file_name_in, 'r'); --->打开文件
IF UTL_FILE.is_open (vsfile)
THEN
LOOP
BEGIN
UTL_FILE.get_line (vsfile, vnewline); -->从文件读入行
IF vnewline IS NULL
THEN
EXIT;
END IF;
INSERT INTO tb_emp (val, file_name) --->将读入的行插入到表
VALUES (vnewline, file_name_in);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
END LOOP;
COMMIT;
END IF;
UTL_FILE.fclose (vsfile); --->关闭打开的文件
UTL_FILE.frename (v_utl_dir, --->此处进行了重命名
file_name_in,
v_utl_dir,
'x.txt',
TRUE);
EXCEPTION --->定义了相关的异常信息
WHEN UTL_FILE.invalid_mode
THEN
raise_application_error (-20051, 'Invalid Mode Parameter');
WHEN UTL_FILE.invalid_path
THEN
raise_application_error (-20052, 'Invalid File Location');
WHEN UTL_FILE.invalid_filehandle
THEN
raise_application_error (-20053, 'Invalid Filehandle');
WHEN UTL_FILE.invalid_operation
THEN
raise_application_error (-20054, 'Invalid Operation');
WHEN UTL_FILE.read_error
THEN
raise_application_error (-20055, 'Read Error');
WHEN UTL_FILE.internal_error
THEN
raise_application_error (-20057, 'Internal Error');
WHEN UTL_FILE.charsetmismatch
THEN
raise_application_error (-20058, 'Opened With FOPEN_NCHAR
But Later I/O Inconsistent');
WHEN UTL_FILE.file_open
THEN
raise_application_error (-20059, 'File Already Opened');
WHEN UTL_FILE.invalid_maxlinesize
THEN
raise_application_error (-20060, 'Line Size Exceeds 32K');
WHEN UTL_FILE.invalid_filename
THEN
raise_application_error (-20061, 'Invalid File Name');
WHEN UTL_FILE.access_denied
THEN
raise_application_error (-20062, 'File Access Denied By');
WHEN UTL_FILE.invalid_offset
THEN
raise_application_error (-20063, 'FSEEK Param Less Than 0');
WHEN OTHERS
THEN
raise_application_error (-20099, 'Unknown UTL_FILE Error');
END read_demo;
/
--下面是读写模式过程的代码，这个过程实现了从一个数据文件读出并写入到另外一个数据文件
CREATE OR REPLACE PROCEDURE rw_demo
IS
infile UTL_FILE.file_type;
outfile UTL_FILE.file_type;
vnewline VARCHAR2 (4000);
i PLS_INTEGER;
j PLS_INTEGER := 0;
seekflag BOOLEAN := TRUE;
BEGIN
-- open a file to read
infile := UTL_FILE.fopen ('DB_UTL_DIR', 'x.txt', 'r'); -->打开源文件用于读取数据
-- open a file to write
outfile := UTL_FILE.fopen ('DB_UTL_DIR', 'out.txt', 'w'); -->创建目标文件用于存放数据
-- if the file to read was successfully opened
IF UTL_FILE.is_open (infile)
THEN
-- loop through each line in the file
LOOP
BEGIN
UTL_FILE.get_line (infile, vnewline); -->从源文件读取行
i := UTL_FILE.fgetpos (infile); -->将行的位置赋值并输出
DBMS_OUTPUT.put_line (TO_CHAR (i));
UTL_FILE.put_line (outfile, vnewline, FALSE); -->将得到的数据行写出到文件句柄缓冲
UTL_FILE.fflush (outfile); -->将数据行从缓冲区写入到文件
IF seekflag = TRUE
THEN
UTL_FILE.fseek (infile, NULL, -30); -->用于调整文件指针，即偏移量
seekflag := FALSE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
END LOOP;
COMMIT;
END IF;
UTL_FILE.fclose (infile); -->关闭源文件
UTL_FILE.fclose (outfile); -->关闭目标文件
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20099, 'Unknown UTL_FILE Error');
END rw_demo;
/
注意在使用UTL_FILE包用到DIRECTORY数据库对象时，名字一定要大写，否则会遭遇“ORA-29280: invalid directory path”错误
主要参考:
http://psoug.org/reference/utl_file.html http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm#BABGGEDF
更多参考
使用 DBMS_PROFILER 定位 PL/SQL 瓶颈代码
PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录
dbms_xplan之display_cursor函数的使用