Wednesday 28 September 2016

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;

No comments:

Post a Comment