CREATE
OR
REPLACE
PROCEDURE
sql_TO_CSV
(
P_QUERY
IN
VARCHAR2,
P_DIR
IN
VARCHAR2,
P_FILENAME
IN
VARCHAR2
)
IS
L_OUTPUTUTL_FILE.FILE_TYPE;
L_THECURSOR
INTEGER
DEFAULT
DBMS_sql.OPEN_CURSOR;
L_COLUMNVALUEVARCHAR2(4000);
L_STATUS
INTEGER
;
L_COLCNTNUMBER:=0;
L_SEPARATORVARCHAR2(1);
L_DESCTBLDBMS_sql.DESC_TAB;
P_MAX_LINESIZENUMBER:=32000;
BEGIN
L_OUTPUT:=UTL_FILE.FOPEN(P_DIR,P_FILENAME,‘W’,P_MAX_LINESIZE);
EXECUTE
IMMEDIATE‘
ALTER
SESSION
SET
NLS_DATE_FORMAT=’’YYYY-MM-DDHH24:MI:SS’’’;
DBMS_sql.PARSE(L_THECURSOR,P_QUERY,DBMS_sql.NATIVE);
DBMS_sql.DESCRIBE_COLUMNS(L_THECURSOR,L_COLCNT,L_DESCTBL);
FOR
I
IN
1..L_COLCNTLOOP
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);
L_STATUS:=DBMS_sql.
EXECUTE
(L_THECURSOR);
WHILE(DBMS_sql.FETCH_ROWS(L_THECURSOR)>0)LOOP
L_SEPARATOR:=‘’;
FOR
I
IN
1..L_COLCNTLOOP
DBMS_sql.COLUMN_VALUE(L_THECURSOR,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;
DBMS_sql.CLOSE_CURSOR(L_THECURSOR);
UTL_FILE.FCLOSE(L_OUTPUT);
EXCEPTION
WHEN
OTHERS
THEN
RAISE;
END
;
