How To Define an External Table in a Text File in Oracle?

You can use the CREATE TABLE statement to create external tables. But you need to use ORGANIZATION EXTERNAL clause to specify the external file location and the data access driver. The tutorial exercise below shows you how to define an external table as a text file:

>sqlplus /nolog

SQL> connect HR/fyicenter

SQL> CREATE TABLE ext_fyi_links (
    id NUMBER(4),
    url VARCHAR2(16),
    notes VARCHAR2(16),
    counts NUMBER(4),
    created DATE
  ) ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY test_dir
    LOCATION ('ext_fyi_links.txt')
  );
Table created.

SQL> SELECT table_name, tablespace_name, num_rows
  FROM USER_TABLES;
TABLE_NAME            TABLESPACE_NAME          NUM_ROWS
--------------------- ---------------------- ----------
REGIONS               USERS                           4
LOCATIONS             USERS                          23
DEPARTMENTS           USERS                          27
JOBS                  USERS                          19
EMPLOYEES             USERS                         107
JOB_HISTORY           USERS                          10
FYI_LINKS             USERS                           2
EXT_FYI_LINKS
COUNTRIES                                            25

⇒ Run Queries on External Tables in Oracle

⇐ What Is a Directory Object in Oracle

⇑ Loading and Exporting Data in Oracle

⇑⇑ Oracle Database Tutorials