External Tables

C:\Oracle\SQL>type shopping.txt
TJ|baking soda||Baking|07/15/08
TJ|crackers|3.3|Bread|08/15/08
VS|chocolate|2|Cashier|07/15/08
WF|honey|4.0/can|Baking|07/30/08
WH|kefir|2.7/quart|Refridgerated|07/01/08

To write queries against a file as if it were part of the Oracle database, several steps are needed:

---

The system must grant you access to a pre-existing directory:

SQL> CONNECT SYSTEM/SYSTEM

SQL> CREATE DIRECTORY ext AS 'c:\oracle\sql';

SQL> GRANT READ  ON DIRECTORY ext TO scott;
SQL> GRANT WRITE ON DIRECTORY ext TO scott;

SQL> CONNECT SCOTT/TIGER

---

You then create an external table layout.

Drop Table Ext_Shopping Purge;

Create Table Ext_Shopping
(  Store_Code   Varchar2(2)
 , Cost_Per     Varchar2(20)
 , Item_Name    Varchar2(35)
 , Category     Varchar2(20)
 , Needed_By    Date
)
Organization External
(
   Type Oracle_Loader
   Default Directory Ext
   Access Parameters
      (
         Records Delimited By Newline
         Badfile 'Shop.Bad'
         Logfile 'Shop.Log'
         Fields Terminated By '|'
         Missing Field Values Are Null
            (
               Store_Code,
               Item_Name,
               Cost_Per,
               Category,
               Needed_By Char Date_Format Date Mask "Mm/Dd/Yy"
            )
      )
   Location (Ext:'Shopping.Txt')
)
Reject Limit Unlimited;

Notes:

• To denote a tab-delimited file, code:

  FIELDS TERMINATED BY "\t"

• Code the input fields as all 'CHAR'; in the table definitions, set up NUMBER, etc.
• If your alpha fields have double quotes, use:

  Fields Terminated By ',' OPTIONALLY ENCLOSED BY '"'

  
  

---

When that is successfully created, you can write any select against it. If there are errors, look in the log file. Any rejected records are in the bad file.

Select *
From Ext_Shopping;

---

You can't manipulate the data or create an index on an external table. If you need to do either, load the data to an actual table in Oracle by creating a table and then performing an Insert Into.

Drop Table Int_Shopping Purge;
Create Table Int_Shopping
(  Store_Code   Varchar2(2)
 , Cost_Per     Varchar2(20)
 , Item_Name    Varchar2(35)
 , Category     Varchar2(20)
 , Needed_By    Date);
Insert Into Int_Shopping
   (  Store_Code ,
      Cost_Per   ,
      Item_Name  ,
      Category   ,
      Needed_By
   )
   Select * 
     From Ext_Shopping;
or
Create Table x
 As
    Select *
      From Ext_Table;

Select *
  From Int_Shopping;

---

This is an example of creating an external table on Unix where the first record will be skipped as it contains column headings:

CREATE TABLE ext_client
( client      VARCHAR2(3),
  client_desc VARCHAR2(100)
)
 ORGANIZATION EXTERNAL
  ( TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext
    ACCESS PARAMETERS
        --------------------------------------------------
        -- view record delimiter by using "od -c"
        -- skip first record which contains column names
        --------------------------------------------------
       ( RECORDS DELIMITED BY "\r\n"
                 CHARACTERSET US7ASCII
                 SKIP=1
         FIELDS TERMINATED BY ','
       )
    LOCATION
    ( 'client.csv','OTHER.csv')        -- the file name(s) as on Unix
  )
REJECT LIMIT UNLIMITED

Note: Research "EXTERNAL_TABLE=GENERATE_ONLY" to see how to generate the code for an external table from SQL Loader.

See Oracle documentation for further detail:
External Tables Concepts