Thursday, March 3, 2011

Oracle: External Tables

External Tables - Oracle



Source :

From Oracle FAQ

Jump to: navigation, search

An external table is a table that is NOT stored within the Oracle database. Data is loaded from a file via an access driver (normally ORACLE_LOADER) when the table is accessed. One can think of an external table as a view that allows running SQL queries against files on a filesystem without the need to first loaded the data into the database.


Prepare test data. For our example we need to create a file called report.csv with the following data:

1, Yes

Create a database directory to match your already existing OS directory and grant your Oracle user READ and WRITE access to it:

SQL> CREATE OR REPLACE DIRECTORY my_data_dir as '/my/data/dir/';

Directory created.


SQL> GRANT read, write ON DIRECTORY my_data_dir TO scott;

Grant succeeded.

Create the external table definition:


( c1 NUMBER,

  c2 VARCHAR2(30)



( default directory my_data_dir

  access parameters

  ( records delimited by newline

    fields terminated by ','


  location ('report.csv') 


Select from the external table will invoke a load of the data on filesystem:

SQL> select * from s1;

     C1 C2

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

      1 Yes


No comments:

Post a Comment