oracle external table log bad,Using SQL*Loader to create an external table-Oracle

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” 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