CREATE DIRECTORY sales_98 AS '/u01/External/sales_1998';
CREATE OR REPLACE PROCEDURE table_to_csv (
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
||' where 1=1';
l_colcnt NUMBER := 0;
l_separator VARCHAR2(1);
l_desctbl dbms_sql.desc_tab;
BEGIN
--create an empty file on disk
l_output := utl_file.fopen(p_dir, p_filename, 'w', '32760');
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);
--write column names into the new file
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);
--write data into the new file and close
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);
END table_to_csv;
/
exec table_to_csv('EMPLOYEES','SALES_98','EMPLOYEES.csv');
DROP TABLE employees_external;
CREATE TABLE employees_external (
"EMPLOYEE_ID" NUMBER(6, 0),
"FIRST_NAME" VARCHAR2(20 BYTE),
"LAST_NAME" VARCHAR2(25 BYTE),
"EMAIL" VARCHAR2(25 BYTE),
"PHONE_NUMBER" VARCHAR2(20 BYTE),
"HIRE_DATE" DATE,
"JOB_ID" VARCHAR2(10 BYTE),
"SALARY" NUMBER(8, 2),
"COMMISSION_PCT" NUMBER(2, 2),
"MANAGER_ID" NUMBER(6, 0),
"DEPARTMENT_ID" NUMBER(4, 0)
)
organization EXTERNAL
(
DEFAULT DIRECTORY sales_98
ACCESS PARAMETERS (
-- optional parameters can added
-- NOBADFILE NODISCARDFILE NOLOGFILE
-- load when ( 1:1 ) != '#'
-- MISSING FIELD VALUES ARE NULL
RECORDS DELIMITED BY NEWLINE SKIP 1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
date_format DATE mask "dd-mon-yyyy hh24:mi:ss"
)
location('EMPLOYEES.csv')
);
select * from EMPLOYEES_EXTERNAL;