External Table

 

1. External tables allow Oracle to query data that is stored outside the database in flat files. The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader. No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external table
2. Create oracle directory name like DATA_PUMP_DIR.

 Create table structure like below

Example:

CREATE TABLE countries_ext (

  country_code      VARCHAR2(5),

  country_name      VARCHAR2(50),

  country_language  VARCHAR2(50)

)

ORGANIZATION EXTERNAL (

  TYPE ORACLE_LOADER

  DEFAULT DIRECTORY DATA_PUMP_DIR

  ACCESS PARAMETERS (

    RECORDS DELIMITED BY NEWLINE

    FIELDS TERMINATED BY ','

    MISSING FIELD VALUES ARE NULL

    (

      country_code      CHAR(5),

      country_name      CHAR(50),

      country_language  CHAR(50)

    )

  )

  LOCATION ('Countries1.txt','Countries2.txt')

)

PARALLEL 5

REJECT LIMIT UNLIMITED;

If the load files have not been saved in the appropriate directory the following result will be displayed.

ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-29400: data cartridge error

KUP-04040: file Countries1.txt in DATA_PUMP_DIR not found

 First text file with data

Countries1.txt 

ENG,England,English

SCO,Scotland,English

IRE,Ireland,English

WAL,Wales,Welsh

Second text file with data

 Countries2.txt

FRA,France,French

GER,Germany,German

USA,Unites States of America,English

By default, a log of load operations is created in the same directory as the load files, but this can be changed using the LOGFILE parameter.

retrieve data from external table

select * from  countries_ext

CREATE OR REPLACE VIEW english_speaking_countries AS

  SELECT *

  FROM   countries_ext

  WHERE  country_language = 'English'

  ORDER BY country_name;