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:
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
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).
|Glossary of Terms|