概述
在PL/SQL中，UTL_FILE包提供了文本文件输入和输出互功能。也就是说我们可以通过该包实现从操作系统级别来实现文件读取输入或者是写入到操作系统文件。通过该包也可以将其他系统的数据加载到数据库中。如加载web服务器日志，用户登录数据库日志乃至Oracle日志文件等等。本文主要描述了UTL_FILE的功能以及通过实例演示并理解这个包下相关过程函数的用法。
官网地址为https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm#i1003488
分为以下几个部分进行介绍
Security ModelTypesOperational NotesRules and LimitsExceptionsExamples
1 Security Model
UTL_FILE可用于客户端和服务器端PL/SQL。 客户端（文本I/O）和服务器实现都受服务器端文件系统权限检查的约束。
在过去，使用UTL_FILE_DIR参数在初始化文件中指定了UTL_FILE函数的可访问目录。 但是，不建议使用UTL_FILE_DIR访问。 建议您使用CREATE DIRECTORY功能，该功能取代UTL_FILE_DIR。 目录对象为UTL_FILE应用程序管理员提供了更大的灵活性和精细控制，可以动态维护（即不关闭数据库），并与其他Oracle工具保持一致。 默认情况下，CREATE DIRECTORY权限仅授予SYS和SYSTEM。
使用CREATE DIRECTORY功能代替UTL_FILE_DIR进行目录访问验证。
2 Types
FILE_TYPE的内容对UTL_FILE包是私有的。 不应该引用或更改此记录的组件。
TYPE file_type IS RECORD (
id BINARY_INTEGER,
datatype BINARY_INTEGER);
3 Operational Notes
UTL_FILE隐式地解释读取请求上的行终止符，从而影响GET_LINE调用返回的字节数。 例如，UTL_FILE.GET_LINE的len参数指定所请求的字符数据的字节数。 实际返回给用户的字节数将是以下值中的较小者：
GET_LINE len参数直到下一行终止符字符的字节数由UTL_FILE.FOPEN指定的max_linesize参数
FOPEN max_linesize参数必须是1和32767范围内的数字。如果未指定，则Oracle提供默认值1024. GET_LINE len参数必须是1和32767范围内的数字。如果未指定，则Oracle提供默认值max_linesize。 如果将max_linesize和len定义为不同的值，则较小的值优先。
当读取在一个字符集中编码的数据并且告知全局化支持（例如通过NLS_LANG）它在另一个字符集中编码时，结果是不确定的。 如果设置了NLS_LANG，则它应与数据库字符集相同。
4 Rules and Limits
特定于操作系统的参数（例如UNIX下的C-shell环境变量）不能在文件位置或文件名参数中使用。
UTL_FILE I/O功能类似于标准操作系统流文件I/O（OPEN，GET，PUT，CLOSE）功能，但有一些限制。 例如，您调用FOPEN函数以返回文件句柄，您在后续调用GET_LINE或PUT时使用该句柄来执行文件的流I/O. 完成文件I/O后，调用FCLOSE以完成与该文件关联的任何输出和空闲资源。
5 Exceptions
Exception NameDescription
INVALID_PATHFile location is invalid.INVALID_MODEThe open_mode parameter in FOPEN is invalid.INVALID_FILEHANDLEFile handle is invalid.INVALID_OPERATIONFile could not be opened or operated on as requested.READ_ERROROperating system error occurred during the read operation.WRITE_ERROROperating system error occurred during the write operation.INTERNAL_ERRORUnspecified PL/SQL errorCHARSETMISMATCHA file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE.FILE_OPENThe requested operation failed because the file is open.INVALID_MAXLINESIZEThe MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.INVALID_FILENAMEThe filename parameter is invalid.ACCESS_DENIEDPermission to access to the file location is denied.DELETE_FAILEDThe requested file delete operation failed.RENAME_FAILEDThe requested file rename operation failed.
6 Examples
6.1 Example 1
SQL> CREATE DIRECTORY log_dir AS '/appl/gl/log';
SQL> GRANT READ ON DIRECTORY log_dir TO DBA;
SQL> CREATE DIRECTORY out_dir AS '/appl/gl/user'';
SQL> GRANT READ ON DIRECTORY user_dir TO PUBLIC;
6.2 Example 1
DECLARE
V1 VARCHAR2(32767);
F1 UTL_FILE.FILE_TYPE;
BEGIN
-- In this example MAX_LINESIZE is less than GET_LINE's length request
-- so the number of bytes returned will be 256 or less if a line terminator is seen.
F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R',256);
UTL_FILE.GET_LINE(F1,V1,32767);
UTL_FILE.FCLOSE(F1);
-- In this example, FOPEN's MAX_LINESIZE is NULL and defaults to 1024,
-- so the number of bytes returned will be 1024 or less if a line terminator is seen.
F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R');
UTL_FILE.GET_LINE(F1,V1,32767);
UTL_FILE.FCLOSE(F1);
-- In this example, GET_LINE doesn't specify a number of bytes, so it defaults to
-- the same value as FOPEN's MAX_LINESIZE which is NULL in this case and defaults to 1024.
-- So the number of bytes returned will be 1024 or less if a line terminator is seen.
F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R');
UTL_FILE.GET_LINE(F1,V1);
UTL_FILE.FCLOSE(F1);
END;
其他函数的参数等相关信息查看官方文档
应用
下面我介绍一下大表导出CSV的存储过程
1 创建directory
shell> mkdir -p /data/datadir
shell> chown -R oracle:oinstall /data/datadir
SQL> create or replace directory DATADIR as '/data/datadir';
SQL> grant read,write on directory DATADIR to public;
2 创建存储过程
CREATE OR REPLACE PROCEDURE SQL_TO_CSV(P_QUERY IN VARCHAR2, -- PLSQL文
P_DIR IN VARCHAR2, -- 导出的文件放置目录
P_FILENAME IN VARCHAR2 -- CSV名
) IS
L_OUTPUT UTL_FILE.FILE_TYPE;
L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
L_COLUMNVALUE VARCHAR2(4000);
L_STATUS INTEGER;
L_COLCNT NUMBER := 0;
L_SEPARATOR VARCHAR2(1);
L_DESCTBL DBMS_SQL.DESC_TAB;
P_MAX_LINESIZE NUMBER := 32000;
BEGIN
--OPEN FILE
L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'W', P_MAX_LINESIZE);
--DEFINE DATE FORMAT
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYYMMDDHH24MISS''';
--OPEN CURSOR
DBMS_SQL.PARSE(L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS(L_THECURSOR, L_COLCNT, L_DESCTBL);
--DUMP TABLE COLUMN NAME
FOR I IN 1 .. L_COLCNT LOOP
UTL_FILE.PUT(L_OUTPUT, L_SEPARATOR || L_DESCTBL(I).COL_NAME);
DBMS_SQL.DEFINE_COLUMN(L_THECURSOR, I, L_COLUMNVALUE, 4000);
L_SEPARATOR := ',';
END LOOP;
UTL_FILE.NEW_LINE(L_OUTPUT);
--EXECUTE THE QUERY STATEMENT
L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);
--DUMP TABLE COLUMN VALUE
WHILE (DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0) LOOP
L_SEPARATOR := '';
FOR I IN 1 .. L_COLCNT LOOP
DBMS_SQL.COLUMN_VALUE(L_THECURSOR, I, L_COLUMNVALUE);
UTL_FILE.PUT(L_OUTPUT,
L_SEPARATOR ||
TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE, '', '')) || '');
L_SEPARATOR := ',';
END LOOP;
UTL_FILE.NEW_LINE(L_OUTPUT);
END LOOP;
--CLOSE CURSOR
DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);
--CLOSE FILE
UTL_FILE.FCLOSE(L_OUTPUT);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
3 导出数据
exec SQL_TO_CSV('select * from a where rownum <= 1000','CSVDIR','a.csv');
