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

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 t1;
     C1 C2
------- ----------
      1 Yes

Common mistakes[edit]

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).

Also see[edit]

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 #