On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Monday, June 30, 2008

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

1 comment: