External Tables

 

External tables are created using the SQL CREATE TABLE...ORGANIZATION EXTERNAL statement.

TYPE — specifies the type of external table. Each type of external table is supported by its own access driver.

ORACLE_LOADER — this is the default access driver. It loads data from external tables to internal tables. The data must come from text data files.

ORACLE_DATAPUMP — this access driver can perform both loads and unloads. The data must come from binary dump files.

• ORACLE_HIVE — extracts data stored in Apache HIVE.

SQL> CREATE TABLE emp_load

  2    (employee_number      CHAR(5),

  3     employee_dob         CHAR(20),

  4     employee_last_name   CHAR(20),

  5     employee_first_name  CHAR(15),

  6     employee_middle_name CHAR(15),

  7     employee_hire_date   DATE)

  8  ORGANIZATION EXTERNAL

  9    (TYPE ORACLE_LOADER

 10     DEFAULT DIRECTORY def_dir1

 11     ACCESS PARAMETERS

 12       (RECORDS DELIMITED BY NEWLINE

 13        FIELDS (employee_number      CHAR(2),

 14                employee_dob         CHAR(20),

 15                employee_last_name   CHAR(18),

 16                employee_first_name  CHAR(11),

 17                employee_middle_name CHAR(11),

 18                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"

 19               )

 20       )

 21     LOCATION ('info.dat')

 22    );

 

Table created.

SQL> CREATE TABLE inventories_EXT

2 ORGANIZATION EXTERNAL

3 (

4 TYPE ORACLE_DATAPUMP

5 DEFAULT DIRECTORY def_dir1

6 LOCATION ('inv_xt.dmp')

7 )

8 AS SELECT * FROM inventories;

Table created.