Oracle 通过存储过程来生成CSV文件

创建directory

create directory REPORT_DIR as '/u01/app/oracle/report';

grant all on directory REPORT_DIR to TEST ;

创建存储过程
CREATE OR REPLACE PROCEDURE SQL_TO_CSV(
                                       P_DIR      IN VARCHAR2, -- output directory
                                       P_FILENAME IN VARCHAR2 -- csv file name
                                       ) IS
  L_OUTPUT       UTL_FILE.FILE_TYPE;
  L_SEPARATOR    VARCHAR2(1);
  L_DESCTBL      DBMS_SQL.DESC_TAB;
  P_MAX_LINESIZE NUMBER := 32000;
  --DEFINE A CURSOR
  CURSOR c1 IS SELECT D_START_DATE FROM OSMSR_VIEW_OPERATION_HISTORY WHERE ROWNUM <= 10;
  v_start_date  OSMSR_VIEW_OPERATION_HISTORY.D_START_DATE%TYPE;
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=''YYYY-MM-DD HH24:MI:SS''';
  --OPEN CURSOR
    OPEN c1;
  LOOP  -- Fetches columns into variables
    FETCH c1 INTO v_start_date;
    EXIT WHEN c1%NOTFOUND;
  --DEFINE THE SEPARATOR ,
    L_SEPARATOR := '';
    UTL_FILE.PUT(L_OUTPUT, L_SEPARATOR || v_start_date);
    L_SEPARATOR := ',';
   --CHANGE THE LINE
    UTL_FILE.NEW_LINE(L_OUTPUT);
  END LOOP;
  --CLOSE CURSOR
  CLOSE c1;
  --CLOSE FILE
  UTL_FILE.FCLOSE(L_OUTPUT);
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;

调用存储过程，REPORT_DIR是directory的名称，02.csv是生成的CSV文件名
BEGIN
  SQL_TO_CSV('REPORT_DIR', '02.csv');
END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2125200/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2125200/