Export Table Data as csv or text file

First Needed a Procedure:

CREATE OR REPLACE PROCEDURE PR_Oracle_table_to_csv_or_txt (
p_tname IN VARCHAR2,
p_dir IN VARCHAR2,
p_filename 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 * from ' || p_tname;
l_colcnt NUMBER := 0;
l_separator VARCHAR2 (1);
l_desctbl DBMS_SQL.desc_tab;
BEGIN
l_output := UTL_FILE.fopen (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 (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 (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;

---

conn sys/password@db_name as sysdba

grant create any directory to hr;

create directory DIR_HR as 'F:\DIR';

exec PR_Oracle_table_to_csv_or_txt( 'EMPLOYEES', 'DIR_HR', 'emp_in_csv.csv' );


exec PR_Oracle_table_to_csv_or_tx( 'EMPLOYEES', 'DIR_HR', 'emp_in_csv.txt' );

External File: 


create or replace directory dir as 'd:/';

grant read,write on directory dir to hr;

create table test( EMPLOYEE_ID   NUMBER(6),
 FIRST_NAME                          VARCHAR2(20),
 LAST_NAME                          VARCHAR2(25),
 EMAIL                                    VARCHAR2(25),
 PHONE_NUMBER                 VARCHAR2(20),
HIRE_DATE                             VARCHAR2(20),
JOB_ID                                    VARCHAR2(10),
SALARY                                             NUMBER(8,2),
COMMISSION_PCT                                     NUMBER(2,2),
MANAGER_ID                                         NUMBER(6)
)
organization external
( default directory dir
access parameters
( records delimited by newline
 fields terminated by ','
 )
  location ('employee.txt') 
 );



exec PR_Oracle_table_to_csv_or_txt( 'EMPLOYEES', 'DIR', 'employee.txt' );