Batch export table data to CSV files

Requirement: Import multiple NTS that meet the conditions in the oracledata database into a csv1_file and store them with the table name. CSV as the file name.

Implementation: It is implemented through the UTL_FILE function in the stored procedure. The exported csv file is placed in the directory created in advance.

Usage: Use the following command to pre-execute the SQL script

SELECT 'exec SQL _to_csv (''select * from' | T. TABLE_NAME |

''', ''Out _ PUT_CSV ''' | ', ''ods _ MDS.' | T. TABLE_NAME |
'.Csv '');'
FROM user_TABLES T

Script Description: SQL _to_csv stored procedure name; out_put_csv database directory name; ODS_MDS pre-defined schema name;

The Stored Procedure Code is as follows:

Create or replace procedure chenqy. SQL _TO_CSV (P_QUERY IN VARCHAR2, -- PLSQL file P_DIR IN VARCHAR2, -- export file 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, 'w', P_MAX_LINESIZE); -- define date format execute immediate 'alter session set NLS_DATE_FORMAT = 'yyyy-MM-DD HH24: MI: s '''; -- open cursor DBMS_ SQL .PARSE (L_THECURSOR, P_QUERY, DBMS_ SQL .NATIVE); merge (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 | '"'); -- 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: = 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 ;/