SQL Reference - EXTERNAL

EXTERNAL

EXTERNAL is a keyword in SQL that specifies a table as an external table. This means that the table data is stored outside the SQL server, typically in a flat file or data file. The SQL server does not control the data or the file format; it just interacts with it for data retrieval or data modification operations. It allows SQL to access data in a database-independent way.

Example

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
       LOCATION = 'https://myaccount.blob.core.windows.net',
       CREDENTIAL = MyAzureBlobStorageCredential
     );

Output

Command(s) completed successfully.

Explanation

The example SQL query uses the CREATE EXTERNAL DATA SOURCE statement to create a data source named MyAzureBlobStorage that connect to an Azure Blob Storage. The TYPE is set to BLOB_STORAGE, LOCATION to the URL of the Blob Storage account, and CREDENTIAL to a pre-existing credential. The output indicates the command executed successfully.

Example

CREATE TABLE employees_ext
    (employee_id NUMBER(6),
     first_name VARCHAR2(20),
     last_name VARCHAR2(25),
     hire_date DATE)
ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
     DEFAULT DIRECTORY def_dir
     ACCESS PARAMETERS
     (RECORDS DELIMITED BY NEWLINE
      FIELDS (employee_id char(3),
              first_name  char(15),
              last_name   char(20),
              hire_date char(20) date_format DATE mask "dd-mon-yyyy"))
     LOCATION ('employees.txt'))
REJECT LIMIT UNLIMITED;

Output

No output is produced as CREATE TABLE...ORGANIZATION EXTERNAL doesn’t produce any output. But it let oracle know to treat the data in the specified file as table ‘employees_ext’.

Explanation

The above SQL creates an external table named ‘employees_ext’. It links to the file ‘employees.txt’ in the directory ‘def_dir’. The ORGANIZATION EXTERNAL clause indicates that it’s an external table. The ACCESS PARAMETERS clause indicates how oracle should interpret the data in the file. The REJECT LIMIT UNLIMITED allows all rows that have format errors to be skipped.