Shulou(Shulou.com)06/01 Report--

1. Create a stored procedure

CREATE OR REPLACE PROCEDURE SQL_TO_CSV (P_QUERY IN VARCHAR2,-- PLSQL text P_DIR IN VARCHAR2,-- exported file placement directory P_FILENAME IN VARCHAR2-- CSV name) 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, 'Waring, P_MAX_LINESIZE);-- DEFINE DATE FORMAT EXECUTE IMMEDIATE' ALTER SESSION SET NLS_DATE_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_DESCTBL OUTPUTSCEPARATOR) | |'"| | EXECUTE THE QUERY STATEMENT L_STATUS (I). COL_NAME | |'");-- output table field DBMS_SQL.DEFINE_COLUMN (L_THECURSOR, I, L_COLUMNVALUE, 4000); L_SEPARATOR: =','; END LOOP; UTL_FILE.NEW_LINE (L_OUTPUT);-- output table field-- EXECUTE THE QUERY STATEMENT L_STATUS: = output table (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; /

2. Create a directory directory

Create or replace directory OUT_PATH as'/ expdp_dir'

3. Stitching the exported statements

SELECT 'EXEC sql_to_csv (' 'select * from' | | T.TABLE_NAME | |'',''OUT_PATH''' | |',''ODS_MDS.' | | T.TABLE_NAME | |' .csv'); 'FROM user_TABLES T where t.TABLEName name'

If a complex sql statement can create a temporary table, the temporary table can be exported

For example: create table an as select * from bb, cc where bb.id=cc.id

Just export the temporary table a directly.

4. Execute the statement that exports csv

EXEC sql_to_csv ('select * from A.csv')

Successfully completed endgame!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.