Table to CSV utility and Oracle external table with ORACLE_LOADER Access Driver

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;