Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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. |
