Oracle: External Tables

External Tables - Oracle

 

 

Source : http://www.orafaq.com/wiki/External_table

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.

Example

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:

CREATE TABLE t1

( c1 NUMBER,

  c2 VARCHAR2(30)

)

ORGANIZATION EXTERNAL

( 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