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

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