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.

[edit] 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

[edit] Common mistakes

Please guard against the following common mistakes:

  • The directory must be on the same system as the database server (or accessible to it at least).
  • Ensure that the OS user that runs the Oracle software can write to this directory (required for the log file).

[edit] Also see

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #