Using SQL * loader to create an external table

The following experiment shows a TXT text file. Based on this text file, use SQL * loader to create an external table.

1. Create a control file

[Oracle @ vmoel5u4 ~] $ VI car. Control
Load data
Infile 'car.txt'
Badfile 'car. bad'
Discardfile 'car. Discard'
Append
Into Table car_info_test
Fields terminated ","
Trailing nullcols
(
Maker,
Model,
No_cyl,
First_built_date 'yyyy/MM/dd ',
Engine,
HP,
Price
)

 

2. Create an External table based on the control file.

[Oracle @ vmoel5u4 ~] $ Sqlldr oltp_usr/Oracle control = car. Control external_table = generate_only log = cardata. Log

SQL * Loader: Release 10.2.0.1.0-production on Sun Mar 31 19:05:06 2013

Copyright (c) 1982,200 5, Oracle. All rights reserved.

 

 

3. Use the cardata. Log File to view the syntax for creating an external table:

[Oracle @ vmoel5u4 ~] $ VI cardata. Log
& Quot; Price & quot; char (255)
Terminated ","
)
Data File: car.txt
Bad file: car. Bad
Discard file: car. Discard
(Allow all discards)

Number to load: All
Number to skip: 0
Errors allowed: 50
Continuation: None specified
Path used: External table

Table car, loaded from every logical record.
Insert option in effect for this table: append
Trailing nullcols option in effect

Column name Position Len term encl datatype
--------------------------------------------------------------------------
Maker first *, Character
Model next *, Character
No_cyl next *, Character
First_built_date next *, date yyyy/mm/dd
Engine next *, Character
HP next *, Character
Price next *, Character

 

Create table statement for External table:
------------------------------------------------------------------------
Create Table "sys_sqlldr_x_ext_car"
(
"Maker" varchar2 (20 ),
"Model" varchar2 (20 ),
"No_cyl" number,
"First_built_date" date,
"Engine" varchar2 (20 ),
"HP" number (10, 1 ),
"Price" number (10, 2)
)
Organization external
(
Type oracle_loader
Default directory Test
Access Parameters
(
Records delimited by newline characterset us7ascii
Badfile 'test': 'Car. bad'
Discardfile 'test': 'Car. Discard'
Logfile 'cardata. log_xt'
Readsize 1048576
Fields terminated by "," ldrtrim
Missing field values are null
Reject rows with all null Fields
(
"Maker" char (255)
Terminated ",",
"Model" char (255)
Terminated ",",
"No_cyl" char (255)
Terminated ",",
"First_built_date" char (255)
Terminated ","
Date_format date mask "yyyy/mm/DD ",
"Engine" char (255)
Terminated ",",
"HP" char (255)
Terminated ",",
& Quot; Price & quot; char (255)
Terminated ","
)
)
Location
(
'Car.txt'
)
) Reject limit Unlimited

Insert statements used to load internal tables:
------------------------------------------------------------------------
[Oracle @ vmoel5u4 ~] $ VI cardata. Log
Terminated ",",
& Quot; Price & quot; char (255)
Terminated ","
)
)
Location
(
'Car.txt'
)
) Reject limit Unlimited

Insert statements used to load internal tables:
------------------------------------------------------------------------
Insert/* + append */into car
(
Maker,
Model,
No_cyl,
First_built_date,
Engine,
HP,
Price
)
Select
"Maker ",
"Model ",
"No_cyl ",
"First_built_date ",
"Engine ",
"HP ",
"Price"
From "sys_sqlldr_x_ext_car"

Statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
Drop table "sys_sqlldr_x_ext_car"

 

From the information in the cardata. Log File above, we can see that the syntax for creating an external table is provided completely. You can create an external table with slight modifications.

4. Create an External table

SQL> conn HR/hr
Connected.
Create Table HR. car_info_test
(
"Maker" varchar2 (20 ),
"Model" varchar2 (20 ),
"No_cyl" number,
"First_built_date" date,
"Engine" varchar2 (20 ),
"HP" number (10, 1 ),
"Price" number (10, 2)
)
Organization external
(
Type oracle_loader
Default directory Test
Access Parameters
(
Records delimited by newline characterset us7ascii
Badfile 'test': 'Car. bad'
Discardfile 'test': 'Car. Discard'
Logfile 'cardata. log_xt'
Readsize 1048576
Fields terminated by "," ldrtrim
Missing field values are null
Reject rows with all null Fields
(
"Maker" char (255)
Terminated ",",
"Model" char (255)
Terminated ",",
"No_cyl" char (255)
Terminated ",",
"First_built_date" char (255)
Terminated ","
Date_format date mask "yyyy/mm/DD ",
"Engine" char (255)
Terminated ",",
"HP" char (255)
Terminated ",",
& Quot; Price & quot; char (255)
Terminated ","
)
)
Location
(
'Car.txt'
)
) Reject limit unlimited;

Table created.

5. Check whether the External table is successfully created.

SQL> select count (*) from car_info_test;

Count (*)
----------
17