Kashif Masood Hashmi Blog

Adopted From Another BLOG of Halim.
SQL> SHOW USER
USER is "SCOTT"
SQL> CREATE OR REPLACE PROCEDURE PR_Oracle_table_to_csv_or_txt (
  2  p_tname IN VARCHAR2,
  3  p_dir IN VARCHAR2,
  4  p_filename IN VARCHAR2
  5  )
  6  IS
  7  l_output UTL_FILE.file_type;
  8  l_thecursor INTEGER DEFAULT DBMS_SQL.open_cursor;
  9  l_columnvalue VARCHAR2 (4000);
 10  l_status INTEGER;
 11  l_query VARCHAR2 (1000) DEFAULT 'select * from ' || p_tname;
 12  l_colcnt NUMBER := 0;
 13  l_separator VARCHAR2 (1);
 14  l_desctbl DBMS_SQL.desc_tab;
 15  BEGIN
 16  l_output := UTL_FILE.fopen (p_dir, p_filename, 'w');
 17
 18  EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
 19
 20  DBMS_SQL.parse (l_thecursor, l_query, DBMS_SQL.native);
 21  DBMS_SQL.describe_columns (l_thecursor, l_colcnt, l_desctbl);
 22
 23  FOR i IN 1 .. l_colcnt
 24  LOOP
 25  UTL_FILE.put (l_output,
 26  l_separator || '"' || l_desctbl (i).col_name || '"'
 27  );
 28  DBMS_SQL.define_column (l_thecursor, i, l_columnvalue, 4000);
 29  l_separator := ',';
 30  END LOOP;
 31
 32  UTL_FILE.new_line (l_output);
 33  l_status := DBMS_SQL.EXECUTE (l_thecursor);
 34
 35  WHILE (DBMS_SQL.fetch_rows (l_thecursor) > 0)
 36  LOOP
 37  l_separator := '';
 38
 39  FOR i IN 1 .. l_colcnt
 40  LOOP
 41  DBMS_SQL.COLUMN_VALUE (l_thecursor, i, l_columnvalue);
 42  UTL_FILE.put (l_output, l_separator || l_columnvalue);
 43  l_separator := ',';
 44  END LOOP;
 45
 46  UTL_FILE.new_line (l_output);
 47  END LOOP;
 48
 49  DBMS_SQL.close_cursor (l_thecursor);
 50  UTL_FILE.fclose (l_output);
 51
 52  EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' ';
 53  EXCEPTION
 54  WHEN OTHERS
 55  THEN
 56  EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' ';
 57
 58  RAISE;
 59  END;
 60  /

Procedure created.

SQL> DESC PR_ORACLE_TABLE_TO_CSV_OR_TXT
PROCEDURE PR_ORACLE_TABLE_TO_CSV_OR_TXT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_TNAME                        VARCHAR2                IN
 P_DIR                          VARCHAR2                IN
 P_FILENAME                     VARCHAR2                IN

SQL> CONN SYS/ORACLE AS SYSDBA
Connected.
USER is "SYS"
trimspool ON
linesize 100
pagesize 100
long 4000
verify OFF
FEEDBACK ON for 1 or more rows
SQL> CREATE DIRECTORY DIR_SCOTT AS 'F:\emp_image';

Directory created.

SQL> GRANT READ,WRITE ON DIR_SCOTT TO SCOTT;
GRANT READ,WRITE ON DIR_SCOTT TO SCOTT
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> GRANT READ,WRITE ON DIRECTORY DIR_SCOTT TO SCOTT;

Grant succeeded.

SQL> CONN SCOTT/TIGER
Connected.
USER is "SCOTT"
trimspool ON
linesize 100
pagesize 100
long 4000
verify OFF
FEEDBACK ON for 1 or more rows

SQL> exec PR_ORACLE_TABLE_TO_CSV_OR_TXT( 'emp', 'DIR_SCOTT', 'emp_in_csv.csv' );

PL/SQL procedure successfully completed.

SQL> SPOOL OFF