Querying Data from Flat Files in Oracle | External Tables

External tables allow Oracle to query data that is stored outside the database in flat files. The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader. No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables. They are useful in the ETL process of data warehouses since the data doesn't need to be staged and can be queried in parallel. They should not be used for frequently queried tables.

 

1. Create the directory, MY_DATA which will contain the data file.

CREATE OR REPLACE DIRECTORY my_data AS ‘D:\DATA_FILE’;

2. The data file, emp.csv contains the employee number, employee name, job, manager number, salary, commission and department number in the following format:

7369,SMITH,CLERK,7902,800,,20

7499,ALLEN,SALESMAN,7698,1600,300,30

7521,WARD,SALESMAN,7698,1250,500,30

7566,JONES,MANAGER,7839,2975,,20

7654,MARTIN,SALESMAN,7698,1250,1400,30

7698,BLAKE,MANAGER,7839,2850,,30

7782,CLARK,MANAGER,7839,2450,,10

7788,SCOTT,ANALYST,7566,3000,,20

7839,KING,PRESIDENT,,5000,,10

7844,TURNER,SALESMAN,7698,1500,0,30

7876,ADAMS,CLERK,7788,1100,,20

7900,JAMES,CLERK,7698,950,,30

7902,FORD,ANALYST,7566,3000,,20

7934,MILLER,CLERK,7782,1300,,10

3. Create the external table, emp_external.

CREATE TABLE emp_external

(

  EMPNO NUMBER(4),

  ENAME VARCHAR2(10),

  JOB VARCHAR2(9),

  MGR NUMBER(4),

  SAL NUMBER(7,2),

  COMM NUMBER(7,2),

  DEPTNO NUMBER(2)

)

ORGANIZATION EXTERNAL

    (TYPE ORACLE_LOADER

     DEFAULT DIRECTORY my_data

     ACCESS PARAMETERS

          (RECORDS DELIMITED BY NEWLINE

           LOGFILE my_data:'data.log'

           BADFILE my_data:'data.bad'

           DISCARDFILE my_data:'data.disc'

           fields terminated by ','

           MISSING FIELD VALUES ARE NULL)

    LOCATION (my_data:'emp.csv')

    );

The DEFAULT DIRECTORY points to the directory which contains the data file. LOGFILE, BADFILE and DISCARDFILE are the files which will be created when the data is loaded into the database. LOCATION indicates the name of the data file in the directory.

4. You can now access the data in the file by querying the external table.

SQL> select * from emp_external;

Output:

Query output of External Table