Created
November 28, 2017 03:47
-
-
Save tqyq/34caca278fe7c6921b449e7fa293670f to your computer and use it in GitHub Desktop.
oracle export to csv
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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=''YYYY-MM-DD HH24:MI:SS'''; | |
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TIMESTAMP_FORMAT=''YYYY-MM-DD HH24:MI:SS'''; | |
--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; | |
/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment