Total Pageviews

May 16, 2015

5/16/2015 12:45:00 AM
Oracle External Tables

External tables 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.
External tables can read flat files (that follow some rules) as though they were ordinary (although read-only) Oracle tables. Therefore, it is convinient to use external tables to load flat files into the DB.

The following tow examples show how to 'import' a file with externals table.
The first file consists of 4 records whose fields (attributes) are seperated by commas:
1,one,abc
2,two,def
3,three,ghi
4,four,jkl

The second file consists of 4 records whose first field is a fixed length field of 4 characters.
B000sekhar
B001anuradha
B002vanaja
B00CCharles

In order to reference these files, a directory must be created.
create or replace directory ext_dir as '/home/rene/ext_dir';
Of course, /home/rene/ext_dir must point to the directory where the file actually resides.
In order to prevent a ORA-29913, someone must grant read and write on the directory to the user that uses the directory:
grant read, write on directory ext_dir to cbyna;
It should be self explanatory that you cannot (in most cases) grant this privilege to yourself.
Comma delimited
Now, we're ready to create the table. We assume that the file name is file.csv.
create table ext_table_csv (
  empno   Number,
  ename   Varchar2(20),
  job   Varchar2(20)
)
organization external (
  type              oracle_loader
  default directory ext_dir
  access parameters (
    records delimited  by newline
    fields  terminated by ','
    missing field values are null
  )
  location ('file.csv')
)
reject limit unlimited;
Fixed field length
create table ext_table_fixed (
   field_1 char(4),
   field_2 char(30)
)
organization external (
   type       oracle_loader
   default directory ext_dir
   access parameters (
     records delimited by newline
     fields (
       field_1 position(1: 4) char( 4),
       field_2 position(5:30) char(30)
    )
  )
  location ('file')
)
reject limit unlimited;

It's now possible to select from ext_table:
select * from ext_table;
In the directory specified with ext_dir, a log file will as well be written upon selecting from the external table.

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 emp_ext (
  emp_code      VARCHAR2(5),
  emp_name      VARCHAR2(50),
  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
    (
      emp_code      CHAR(5),
      emp_name      CHAR(50),
     language  CHAR(50)
    )
  )
  LOCATION ('Countries1.txt.gz','Countries2.txt.gz')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

0 comments:

Post a Comment