It is possible to export the data in tables in the database in csv format depending on a specific condition. We can use the UTL_FILE package for this operation.
With the following procedure, we can create a csv format file in a directory on the server that we specify with the virtual directory, with or without specifying the data in a table.
In the procedure, the “|” sign is used to separate column names. The “;” sign is used to separate the data. You can use any separator with the l_separator variable.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
CREATE OR REPLACE PROCEDURE dump_table_to_csv (p_tname IN VARCHAR2, p_dir IN VARCHAR2, p_filename IN VARCHAR2, p_condition IN VARCHAR2) IS l_output UTL_FILE.file_type; l_theCursor INTEGER DEFAULT DBMS_SQL.open_cursor; l_columnValue VARCHAR2 (4000); l_status INTEGER; l_query VARCHAR2 (1000) DEFAULT 'select /*+ PARALLEL 16*/ * from ' || p_tname || ' ' || p_condition; l_colCnt NUMBER := 0; l_separator VARCHAR2 (1); l_descTbl DBMS_SQL.desc_tab; BEGIN l_output := UTL_FILE.fopen_nchar (p_dir, p_filename, 'w'); EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' '; DBMS_SQL.parse (l_theCursor, l_query, DBMS_SQL.native); DBMS_SQL.describe_columns (l_theCursor, l_colCnt, l_descTbl); FOR i IN 1 .. l_colCnt LOOP UTL_FILE.put_nchar (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_colCnt LOOP DBMS_SQL.COLUMN_VALUE (l_theCursor, i, l_columnValue); UTL_FILE.put_nchar (l_output, l_separator || 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); EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' '; EXCEPTION WHEN OTHERS THEN EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' '; RAISE; END; / |
Procedure Usage:
First, the virtual directory must be created as follows.
For example, a virtual directory named CSV_DUMP_DIR is created that represents the /tmp directory.
|
1 2 3 |
CREATE OR REPLACE DIRECTORY CSV_DUMP_DIR AS '/tmp'; |
The user who will execute the procedure must be authorized to read and write in the virtual directory created.
|
1 |
GRANT READ, WRITE ON DIRECTORY CSV_DUMP_DIR TO USERNAME; |
We can execute the procedure as follows. When specifying a condition, you must use double quotes, as in the following example. If we do not specify a condition, the parameter p_condition can be sent empty. The user who will run the procedure must be able to query the table he is trying to export.
Example with Condition:
|
1 2 3 |
SQL> exec dump_table_to_csv( 'USER.TABLE', 'CSV_DUMP_DIR', 'TABLE.CSV','where to_char(MYDATE,''yyyy'')=''2017'''); PL/SQL procedure successfully completed. |
Example without Condition:
|
1 2 3 |
SQL> exec dump_table_to_csv( 'USER.TABLE', 'CSV_DUMP_DIR', 'TABLE.CSV',''); PL/SQL procedure successfully completed. |
