sqlloader Versus External Table-

• External Table is first introduced in 9i Version of Database.

Steps-1) Create Directory 2) Grant Access on Directory 3) Create External Table 4) Place physical file at location 5) Access Table to Load the data.

External Table uses Oracle Loader Drivers internally.

Example:
CREATE OR REPLACE DIRECTORY ext AS 'c:\external';
GRANT READ ON DIRECTORY ext TO uwclass;
GRANT WRITE ON DIRECTORY ext TO uwclass;

CREATE TABLE ext_tab (
empno  CHAR(4),
ename  CHAR(20),
job    CHAR(20),
deptno CHAR(2))
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY ext
    ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    BADFILE 'bad_%a_%p.bad'
    LOGFILE 'log_%a_%p.log'
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (empno, ename, job, deptno))
    LOCATION ('demo1.dat')
  )
PARALLEL
REJECT LIMIT 0
NOMONITORING;

External table can take file name dynamically:
Approach: alter table ext_table location ('new_filename');

Advantage/DisAdv External Table:
The big advantage of external tables is that we can query them from inside the database using SQL
No support for DML. Oracle external tables are read-only.
Poor response for high-volume queries.
No index creation is allowed on an external tables.
An external table cannot load data into a LONG column
You can, although, create views and synonyms for external tables
The external table can be queried and joined directly, in parallel using the SQL statement SELECT
The actual data resides outside the database in OS files, hence the clause ORGANIZATION EXTERNAL organization.
No supported: Use of CONTINUEIF or CONCATENATE to combine multiple physical records into a single logical record
Use of the following database column types: LONGs, nested tables, VARRAYs, REFs, primary key REFs, and SIDs
If there are multiple primary input datafiles with SQL*Loader loads, a bad file and a discard file are created for each input datafile. With external table loads, there is only one bad file and one discard file for all input datafiles. If parallel access drivers are used for the external table load, each access driver has its own bad file and discard file.

External Table to Extract Data
Oracle 10g lets you create a new external table from data in your database, which goes into a flat file pushed from the database using the ORACLE_DATAPUMP access driver.This flat file is in an Oracle-proprietary format that can be read by DataPump.The key difference is that you must specify the access driver, ORACLE_DATAPUMP, since the access driver defaults to ORACLE_LOADER.

Example:SQL> create table export_empl_info
 organization external
 ( type oracle_datapump
 default directory xtern_data_dir
 location ('empl_info_rpt.dmp')
 ) as select * from empl_info;


tables for external table definition:
DBA_EXTERNAL_TABLES
DBA_EXTERNAL_LOCATIONS