External table

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

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

Also see

• Heap-organized table
• Index-organized table

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	#