Search 800 + Posts

May 9, 2009

Oracle External Tables provide great deal to Upload data into Oracle table from a file without executing the Sql Loader.What is External Table - Definition of External table from Oracle Database manual

An external table load creates an external table for data in a datafile and executes INSERT statements to insert the data from the datafile into the target table.
There are two advantages of using external table loads over conventional path and direct path loads:
An external table load attempts to load datafiles in parallel. If a datafile is big enough, it will attempt to load that file in parallel.
An external table load allows modification of the data being loaded by using SQL functions and PL/SQL functions as part of the INSERT statement that is used to create the external table.Steps that Required for External table.
Create database directory and map it with your local PC directory. You need to have Admin Privileged to create Database directory.
Grant read/write access of database directory to your user who want to use External table.
Create External table with DEFAULT DIRECTORY as Database directory.Step #1CREATE OR REPLACE DIRECTORY load_dir AS 'C:\myexternaltable'; GRANT CREATE ANY DIRECTORY to user_nameGrant DROP ANY DIRECTORY to user_nameStep #2GRANT READ ON DIRECTORY load_dir TO user_name; GRANT WRITE ON DIRECTORY load_dir TO user_name; Step #3CREATE TABLE BG_STATEMENT_LINES_EXT( BG0 VARCHAR2(150), BG1 VARCHAR(150), BG2 VARCHAR2(150), BG3 VARCHAR2(150), BG4 VARCHAR2(150) )ORGANIZATION EXTERNAL( TYPE ORACLE_LOADER DEFAULT DIRECTORY LOAD_DIR ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE badfile load_dir:'bst_lns.bad' logfile load_dir:'bst_lns.log' FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL ( BG0, BG1, BG2, BG3, BG4, BG5 ) ) LOCATION ( LOAD_DIR: 'Invoices.csv' ))REJECT LIMIT UNLIMITED;In My example I have Mapped my PC's directory 'C:\myexternaltable' to Database directory load_dir.While defining the external Table , I added the clause "DEFAULT DIRECTORY LOAD_DIR" to Map External Table with Database Directory.How to Process data with External table.
Create and Grant Database directory and External Table as explain Above.
Get your data file in the same format as of External table.
Save your data file into your PC's directory that has mapped to Database directory.
On Save , Data from file will Import to External table.Advantages -Very Easy Friendly , will till Training even we can engage business user to handle it's Processing.Less Maintenance Cost .Disadvantages of External Table.
Till R11g there is no option to execute DML against an external table.External tables supports SELECT only.

No comments:

Post a Comment