<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
CREATE OR REPLACE PROCEDURE dump_table_to_csv (p_tname IN VARCHAR2,\r\np_dir IN VARCHAR2,\r\np_filename IN VARCHAR2,\r\np_condition IN VARCHAR2)\r\nIS\r\nl_output UTL_FILE.file_type;\r\nl_theCursor INTEGER DEFAULT DBMS_SQL.open_cursor;\r\nl_columnValue VARCHAR2 (4000);\r\nl_status INTEGER;\r\nl_query VARCHAR2 (1000) DEFAULT 'select \/*+ PARALLEL 16*\/ * from ' || p_tname || ' ' || p_condition;\r\nl_colCnt NUMBER := 0;\r\nl_separator VARCHAR2 (1);\r\nl_descTbl DBMS_SQL.desc_tab;\r\nBEGIN\r\nl_output := UTL_FILE.fopen_nchar (p_dir, p_filename, 'w');\r\n\r\nEXECUTE IMMEDIATE\r\n'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';\r\n\r\nDBMS_SQL.parse (l_theCursor, l_query, DBMS_SQL.native);\r\nDBMS_SQL.describe_columns (l_theCursor, l_colCnt, l_descTbl);\r\n\r\nFOR i IN 1 .. l_colCnt\r\nLOOP\r\nUTL_FILE.put_nchar (l_output,\r\nl_separator || l_descTbl (i).col_name );\r\nDBMS_SQL.define_column (l_theCursor,\r\ni,\r\nl_columnValue,\r\n4000);\r\nl_separator := '|';\r\nEND LOOP;\r\n\r\nUTL_FILE.new_line (l_output);\r\n\r\nl_status := DBMS_SQL.execute (l_theCursor);\r\n\r\nWHILE (DBMS_SQL.fetch_rows (l_theCursor) > 0)\r\nLOOP\r\nl_separator := '';\r\n\r\nFOR i IN 1 .. l_colCnt\r\nLOOP\r\nDBMS_SQL.COLUMN_VALUE (l_theCursor, i, l_columnValue);\r\nUTL_FILE.put_nchar (l_output, l_separator || l_columnValue);\r\nl_separator := ';';\r\nEND LOOP;\r\n\r\nUTL_FILE.new_line (l_output);\r\nEND LOOP;\r\n\r\nDBMS_SQL.close_cursor (l_theCursor);\r\nUTL_FILE.fclose (l_output);\r\n\r\nEXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' ';\r\nEXCEPTION\r\nWHEN OTHERS\r\nTHEN\r\nEXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' ';\r\n\r\nRAISE;\r\nEND;\r\n\/<\/pre>\nProcedure Usage:<\/strong><\/p>\n
First, the virtual directory must be created as follows.<\/p>\n
For example, a virtual directory named CSV_DUMP_DIR is created that represents the \/tmp directory.<\/p>\n
CREATE OR REPLACE DIRECTORY \r\nCSV_DUMP_DIR AS \r\n'\/tmp';<\/pre>\nThe user who will execute the procedure must be authorized to read and write in the virtual directory created.<\/p>\n
GRANT READ, WRITE ON DIRECTORY CSV_DUMP_DIR TO USERNAME;<\/pre>\nWe 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.<\/p>\n
Example with Condition:<\/strong><\/p>\n
SQL> exec dump_table_to_csv( 'USER.TABLE', 'CSV_DUMP_DIR', 'TABLE.CSV','where to_char(MYDATE,''yyyy'')=''2017''');\r\n\r\nPL\/SQL procedure successfully completed.<\/pre>\nExample without Condition:<\/strong><\/p>\n
SQL> exec dump_table_to_csv( 'USER.TABLE', 'CSV_DUMP_DIR', 'TABLE.CSV','');\r\n\r\nPL\/SQL procedure successfully completed.<\/pre>\n\n<\/div>\n