External Tables : Querying Data From Flat Files in Oracle

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 tables. They are useful in the ETL process of data warehouses since the data doesn't need to be staged and can be queried in parallel. They should not be used for frequently queried tables.

• Basic Usage
• Querying the Alert Log
• 11gR2 Updates
• Views

Basic Usage

Download the files (Countries1.txt, Countries2.txt) containing the data to be queried. In this example the data is split across two files which should be saved to a filesystem available to the Oracle server.

Create a directory object pointing to the location of the files.

CREATE OR REPLACE DIRECTORY ext_tab_data AS '/data';

Create the external table using the CREATE TABLE..ORGANIZATION EXTERNAL syntax. This defines the metadata for the table describing how it should appear and how the data is loaded.

CREATE TABLE countries_ext (
  country_code      VARCHAR2(5),
  country_name      VARCHAR2(50),
  country_language  VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_tab_data
  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;

Once the external table created, it can be queried like a regular table.

SQL> SELECT *
  2  FROM   countries_ext
  3  ORDER BY country_name;

COUNT COUNTRY_NAME                 COUNTRY_LANGUAGE
----- ---------------------------- -----------------------------
ENG   England                      English
FRA   France                       French
GER   Germany                      German
IRE   Ireland                      English
SCO   Scotland                     English
USA   Unites States of America     English
WAL   Wales                        Welsh

7 rows selected.

SQL>

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

SQL> SELECT *
  2  FROM   countries_ext
  3  ORDER BY country_name;
SELECT *
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file Countries1.txt in EXT_TAB_DATA not found
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

SQL>

Once the table is functioning correctly we can create views against it.

CREATE OR REPLACE VIEW english_speaking_countries AS
  SELECT *
  FROM   countries_ext
  WHERE  country_language = 'English'
  ORDER BY country_name;

SELECT *
FROM   english_speaking_countries;

COUNT COUNTRY_NAME                 LANGUAGE
----- ---------------------------- ----------------------------
ENG   England                      English
IRE   Ireland                      English
SCO   Scotland                     English
USA   Unites States of America     English

4 rows selected.

SQL>

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.

 LOG file opened at 10/15/02 14:06:44

Field Definitions for table COUNTRIES_EXT         
  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:                          
                                                  
    COUNTRY_CODE                    CHAR (5)      
      Terminated by ","                           
      Trim whitespace same as SQL Loader          
    COUNTRY_NAME                    CHAR (50)     
      Terminated by ","                           
      Trim whitespace same as SQL Loader          
    COUNTRY_LANGUAGE                CHAR (50)     
      Terminated by ","                           
      Trim whitespace same as SQL Loader

Any rows that fail to load are written to a bad file. By default, the bad file is created in the same directory as the load files, but this can be changed using the BADFILE parameter.

Remember, every query of the external table causes the file(s) to be read again, so try to make as few passes over the external table as possible.

Querying the Alert Log

The following example shows how an external table can be used to query the contents of the alert log.

CREATE OR REPLACE DIRECTORY bdump AS '/u01/app/oracle/admin/SID/bdump/';

DROP TABLE alert_log;

CREATE TABLE alert_log (
  line  VARCHAR2(4000)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY bdump
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    BADFILE bdump:'read_alert_%a_%p.bad'
    LOGFILE bdump:'read_alert_%a_%p.log'
    FIELDS TERMINATED BY '~'
    MISSING FIELD VALUES ARE NULL
    (
      line  CHAR(4000)
    )
  )
  LOCATION ('alert_SID.log')
)
PARALLEL 1
REJECT LIMIT UNLIMITED
/

SET LINESIZE 1000
SELECT * FROM alert_log;

11gR2 Updates

Oracle 11g Release 2 introduced the PREPROCESSOR clause to identify a directory object and script used to process the files before they are read by the external table. This feature was backported to 11gR1 (11.1.0.7). The PREPROCESSOR clause is especially useful for reading compressed files, since they are unzipped and piped straight into the external table process without ever having to be unzipped on the file system.

CREATE OR REPLACE DIRECTORY exec_dir AS '/bin';

CREATE TABLE countries_ext (
  country_code      VARCHAR2(5),
  country_name      VARCHAR2(50),
  country_language  VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_tab_data
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    PREPROCESSOR exec_dir:'zcat'
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      country_code      CHAR(5),
      country_name      CHAR(50),
      country_language  CHAR(50)
    )
  )
  LOCATION ('Countries1.txt.gz','Countries2.txt.gz')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

The EXECUTE privilege must be granted on the directory containing the executable or script.

If you need to specify command line parameters you should create a script to perform the action, then call that in the PREPROCESSOR clause. For example, assuming we had an executable file in the executable directory called "my_unzip.sh" with the following contents.

#!/bin/bash
/bin/gunzip -c $1

We could use the following in the PREPROCESSOR clause.

PREPROCESSOR exec_dir:'my_unzip.sh'

Views

The [DBA|ALL|USER]_EXTERNAL_TABLES views display information about external tables.

SELECT COUNT(*) FROM dba_external_tables;

  COUNT(*)
----------
       194

1 row selected.

SQL>

• 100% Practical Training

• Hands
  On
  Training

• 100%
  Job
  Gurantee

Interested to be a trainer?

jobs@greenstech.in

For corporate Training

corp@greenstech.in

Copyright © 2018 Greens Technologys. All Rights Reserved. Privacy Policy