How to Import data From Oracle table to CSV file or txt file?

1) Write a procedure like below......

connect as scott user and create below 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;

-------------------------------------------------------

2) Grant create directory privileze to scott


SQL> grant CREATE ANY DIRECTORY to scott ;


3) Create a directory

create directory DIR_SCOTT as 'G:\DUMP'


4) then execute the procedure as desire file extension.


exec dump_table_to_csv( 'emp', 'DIR_SCOTT', 'emp_in_csv.csv' );


exec dump_table_to_csv( 'emp', 'DIR_SCOTT', 'emp_in_csv.txt' );