Oracle External Tables

Oracle SQL*Loader engine allows you to query external tables that are stored on flat files. When I say flat files, I literally mean a file that is stored on OS level. Yes, you can query a flat file that is stored outside of the database at OS level. The ORACLE_LOADER drive is used to query the external tables that is stored in any format on an external file.

• Create flat files

• Create directory object

• Create external table

• Query external table

• Create view on external table

• Load operation log

Note: Any format means all the formats that SQL*Loader can read.

Note: you can only query external table. No DML operation is allowed.

Create flat files

For our example, let us create one file and save it as .txt format on the database server. Create my_regions.txt file and copy paste below contents

1,US
2,UK
3,AUS
4,IND
5,UAE

Save and close the file.

Create directory object

We need to create a directory inside Oracle database that points to the directory pointing to the location of my_regions.txt file

CREATE OR REPLACE DIRECTORY my_ext_tab AS '/home/oracle';

Create external table

Inside Oracle database, we need to create an external table that will query data from the above file

CREATE TABLE my_regions (
  region_id  number(1),
  region_name varchar2(20)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY my_ext_tab
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      region_id  CHAR(1),
      region_name CHAR(5)
    )
  )
  LOCATION ('my_regions.txt')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

Query external table

Once the external table is created, you can query it like a normal table

SELECT * FROM my_regions;

Create view on external table

Once you are able to query the external table, you can even create view on it

CREATE OR REPLACE VIEW my_regions_view AS
SELECT * FROM my_regions
WHERE region_name LIKE 'U%';

SELECT * FROM my_regions_view;

Load operation log

By default, a log of load operations is created in the same directory as the load files. In the same location where you saved my_regions.txt file, there will a log file created for the load operation

 LOG file opened at 01/02/16 07:17:59

Field Definitions for table MY_REGIONS
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    REGION_ID                       CHAR (1)
      Terminated by ","
      Trim whitespace same as SQL Loader
    REGION_NAME                     CHAR (5)
      Terminated by ","
      Trim whitespace same as SQL Loader


 LOG file opened at 01/02/16 07:20:12

Field Definitions for table MY_REGIONS
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    REGION_ID                       CHAR (1)
      Terminated by ","
      Trim whitespace same as SQL Loader
    REGION_NAME                     CHAR (5)
      Terminated by ","
      Trim whitespace same as SQL Loader