ad 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" 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;
Table created.
5,确认是否创建外部表成功
SQL> select count(*) from car_info_test;
COUNT(*)
----------
17