创建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博客

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

最新文章