Using SQL*Loader to create an external table
Using SQL*Loader to create an external table
下面的实验是有一个txt的文本文件，根据此文本文件，使用SQL*Loader创建一个External Table.
1,创建控制文件
[oracle@vmoel5u4 ~]$ vi car.control
load data
infile 'car.txt'
badfile 'car.bad'
discardfile 'car.discard'
append
into table car_info_test
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
maker,
model,
no_cyl,
first_built_date date 'yyyy/mm/dd',
engine,
hp,
price
)
2,然后根据控制文件创建一个外部表
[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, 2005, Oracle. All rights reserved.
3,通过cardata.log文件来查看创建external table的语法：
[oracle@vmoel5u4 ~]$ vi cardata.log
"PRICE" CHAR(255)
TERMINATED BY ","
)
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 BY ",",
"MODEL" CHAR(255)
TERMINATED BY ",",
"NO_CYL" CHAR(255)
TERMINATED BY ",",
"FIRST_BUILT_DATE" CHAR(255)
TERMINATED BY ","
DATE_FORMAT DATE MASK "yyyy/mm/dd",
"ENGINE" CHAR(255)
TERMINATED BY ",",
"HP" CHAR(255)
TERMINATED BY ",",
"PRICE" CHAR(255)
TERMINATED BY ","
)
)
location
(
'car.txt'
)
)REJECT LIMIT UNLIMITED
INSERT statements used to load internal tables:
------------------------------------------------------------------------
[oracle@vmoel5u4 ~]$ vi cardata.log
TERMINATED BY ",",
"PRICE" CHAR(255)
TERMINATED BY ","
)
)
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"
由上面cardata.log文件的信息，可以看出，创建external table的语法都完整的给出了，只要稍做修改就可以创建外部表了
4, 创建外部表
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&
