EXTERNAL
TABLES &
SQL LOADER.
SQL Loader is a bulk loader utility used for moving data from external files
into the Oracle database. The external tables feature is a complement to
existing SQL Loader functionality which helps you to access data from
external sources.
EXTERNAL TABLES AND SQL LOADER.
What are External Tables?
External tables allow Oracle to query data that is stored outside the database in flat files. The
ORACLE_LOADER driver can be used to access any data stored in any format that can be
loaded by SQL*Loader. No DML can be performed on external tables but they can be used for
query, join and sort operations.
First create below oracle directory in database:
SQL> create directory oradata as ‘/u01/app/oracle/oradata’;
SQL> grant all on directory oradata to public;
External table syntax:
create table emp_load
(employee_number char(5),
employee_dob char(20),
employee_last_name char(20),
employee_first_name char(15),
employee_middle_name char(15),
employee_hire_date date)
organization external
(type oracle_loader
default directory oradata
access parameters
(records delimited by newline
fields (employee_number char(2),
employee_dob char(20),
employee_last_name char(18),
employee_first_name char(11),
employee_middle_name char(11),
employee_hire_date char(10) date_format date mask "mm/dd/yyyy"
)
)
location ('[Link]')
);
Create [Link] text file in oradata directory. ( /u01/app/oracle/oradata )
[Link] file contents:
56 november, 15, 1980 baker mary alice 09/01/2004
87 december, 20, 1970 roper lisa marie 01/01/1999
EXTERNAL TABLES AND SQL LOADER.
External table syntax:
create table clsext
(x varchar2(6))
organization external
(type oracle_loader
default directory oradata
access parameters
(records delimited by newline
fields (x varchar2(6)
)
)
location ('[Link]'));
[Link] file contents:
John
Alice
Philip
Russel
EXTERNAL TABLES AND SQL LOADER.
What is SQL loader?
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle
database.
Records read from the input file might not be inserted into the database. Such records are
placed in either a bad file or a discard file.
Bad file:
Datafile records are rejected by SQL Loader when the input format is invalid. The bad file
contains rows that were rejected because of errors. These errors might include bad datatypes
or referential integrity constraints. For example, if the second enclosure delimiter is missing,
or if a delimited field exceeds its maximum length, SQL Loader rejects the record. Rejected
records are placed in the bad file.
Discard file:
The discard file contains rows that were discarded because they were filtered out because of a
statement in the SQL Loader control file.
EXTERNAL TABLES AND SQL LOADER.
SQL LOADER:
CREATE TABLE test_ldr (
EMP_ID NUMBER(5),
EMP_NAME VARCHAR2(30),
EMP_DEPT VARCHAR2(20));
###########Control File######
LOAD DATA
INFILE *
INTO TABLE chito_ldr
REPLACE
FIELDS TERMINATED BY ','
EMP_ID ,
EMP_NAME ,
EMP_DEPT )
##########[Link]##########
1,Chivas,Grant
2,Scott,Tiger
3,Peter,Russel
##########################
sqlldr username/password control=[Link] data=[Link]
