1.ORACLE通过UTL_FILE包导出CSV文件

概述

在PL/SQL中，UTL_FILE包提供了文本文件输入和输出互功能。也就是说我们可以通过该包实现从操作系统级别来实现文件读取输入或者是写入到操作系统文件。通过该包也可以将其他系统的数据加载到数据库中。如加载web服务器日志，用户登录数据库日志乃至Oracle日志文件等等。本文主要描述了UTL_FILE的功能以及通过实例演示并理解这个包下相关过程函数的用法。

官网地址为https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm#i1003488

分为以下几个部分进行介绍

• Security Model
• Types
• Operational Notes
• Rules and Limits
• Exceptions
• Examples

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 Name	Description
INVALID_PATH	File location is invalid.
INVALID_MODE	The open_mode parameter in FOPEN is invalid.
INVALID_FILEHANDLE	File handle is invalid.
INVALID_OPERATION	File could not be opened or operated on as requested.
READ_ERROR	Operating system error occurred during the read operation.
WRITE_ERROR	Operating system error occurred during the write operation.
INTERNAL_ERROR	Unspecified PL/SQL error
CHARSETMISMATCH	A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE.
FILE_OPEN	The requested operation failed because the file is open.
INVALID_MAXLINESIZE	The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.
INVALID_FILENAME	The filename parameter is invalid.
ACCESS_DENIED	Permission to access to the file location is denied.
DELETE_FAILED	The requested file delete operation failed.
RENAME_FAILED	The 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');