External Tables in Oracle

External Tables:
---------------
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.


Next we create a directory object which points to the location of the files:

create or replace directory dir_Test as 'D:\'


Next we create the metadata for the external table using the CREATE TABLE..ORGANIZATION EXTERNAL syntax:



SQl> drop table external_temp;

SQl> create  table external_temp
     (empno number(3),ename varchar2(10),deptno number,sal number(7,2))
      organization external
        (type oracle_loader
           default directory dir_test
            access parameters(
             records delimited by newline
               fields terminated by ','
               missing field values are null
     (empno,ename,deptno,sal))
        location  ('data1.txt'))
          reject limit unlimited;

SQl> select * from external_temp;


External Tables Containing LOB Data:
-----------------------------------

The foolowing is the file name having two records.

lob_test_data.txt:
----------------

1,one,01-JAN-2006,clob_test1.txt,blob_test1.doc
2,two,02-JAN-2006,clob_test2.txt,blob_test2.doc

The first two documents are plain text files, while the second two are Microsoft Word documents.
 The documents contain the CLOB and BLOB data to be read by the external table

DROP TABLE lob_tab;

CREATE TABLE lob_tab (
  number_content    NUMBER(10),
  varchar2_content  VARCHAR2(100),
  date_content      DATE,
  clob_content      CLOB,
  blob_content      BLOB
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY temp_dir
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    BADFILE temp_dir:'lob_tab_%a_%p.bad'
    LOGFILE temp_dir:'lob_tab_%a_%p.log'
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      number_content    CHAR(10),
      varchar2_content  CHAR(100),
      date_content      CHAR(11) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      clob_filename     CHAR(100),
      blob_filename     CHAR(100)
    )
    COLUMN TRANSFORMS (clob_content FROM LOBFILE (clob_filename) FROM (temp_dir) CLOB,
                       blob_content FROM LOBFILE (blob_filename) FROM (temp_dir) BLOB)
  )
  LOCATION ('lob_test_data.txt')
)
PARALLEL 2
REJECT LIMIT UNLIMITED
/


There are two things to note:
------------------------------

The field names of the LOB data (clob_filename, blob_filename) do not match the column names (clob_content, blob_content).

 This emphasizes these fields in the datafile do not contain the actual data, just filenames where the data can be found.

The COLUMN TRANSFORMS clause explains how the CLOB and BLOB data should be loaded.
 In this case we are using a field from the datafile to identify the filename and a constant for the directory name.
 To load from multiple directories use an additional field in the datafile to identify directory object.

Ur's

AmarAlam