Batch export table data from Oracle to CSV files

Batch export table data from Oracle 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.

Generate and view Oracle AWR reports

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Steps for generating an Oracle AWR report

Export and Import csv files from Oracle database tables
 
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
P_DIR IN VARCHAR2, -- directory of the exported file
P_FILENAME IN VARCHAR2 -- CSV name
)
IS
Rochelle output UTL_FILE.FILE_TYPE;
Rochelle thecursor integer default DBMS_ SQL .OPEN_CURSOR;
Rochelle columnvalue VARCHAR2 (4000 );
Rochelle status INTEGER;
Rochelle colcnt NUMBER: = 0;
Rochelle separator VARCHAR2 (1 );
Rochelle 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 );
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 | '"'); -- output table Field
DBMS_ SQL .DEFINE_COLUMN (L_THECURSOR, I, L_COLUMNVALUE, 4000 );
Rochelle 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
Rochelle separator: = '';
For I IN 1 .. L_COLCNT LOOP
DBMS_ SQL .COLUMN_VALUE (L_THECURSOR, I, L_COLUMNVALUE );
UTL_FILE.PUT (L_OUTPUT,
Rochelle separator | '"' |
TRIM (BOTH ''from replace (L_COLUMNVALUE, '"', '"') | '"');
Rochelle 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;
 
/