Saturday, May 30, 2020

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(60),
    "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(82),
    "COMMISSION_PCT"  NUMBER(22),
    "MANAGER_ID"      NUMBER(60),
    "DEPARTMENT_ID"   NUMBER(40)
)
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;