Oracle External Table

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 data file and executes INSERT statements to insert the data from the data file 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 data files in parallel. If a data file 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.

Disadvantages of External Table.
Till R11g there is no option to execute DML against an external table. External tables supports SELECT only.

Steps for External table.
1. Create database directory and map it with your local PC directory. You need to have Admin Privileged to create Database directory.
2.Grant read/write access of database directory to user.
3.Create External table with DEFAULT DIRECTORY as Database directory.

Step #1
CREATE OR REPLACE DIRECTORY load_dir AS 'C:\myexternaltable';

GRANT CREATE ANY DIRECTORY to user_name
Grant DROP ANY DIRECTORY to user_name

Step #2
GRANT READ ON DIRECTORY load_dir TO user_name;
GRANT WRITE ON DIRECTORY load_dir TO user_name;


CREATE TABLE BG_STATEMENT_LINES_EXT
(
BG0 VARCHAR2(150),
BG1 VARCHAR(150),
BG2 VARCHAR2(150),
BG3 VARCHAR2(150),
BG4 VARCHAR2(150),
BG5 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: 'file_name.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 have added the clause "DEFAULT DIRECTORY LOAD_DIR" to map External Table with database directory.

Data Processing

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 , make sure filename is same as defined "LOAD_DIR: 'file_name.csv'"
On Save, data from file will Import to External table.