oracle clob表导入,用SQLLOAD导入CLOB数据

10G,SUSE 9,SQLPLUS[@more@]

1,创建表结构

create table sqlload_test

( EMPNO number,

ENAME varCHAR2(20),

JOB varCHAR2(20),

MGR number,

SAL number,

COMM number,

DEPTNO number,

resume clob)

2,建立数据

:~> cat > ulcase91.dat <

Resume for Mary Clark

Career Objective: Manage a sales team with consistent record breaking

performance.

Education: BA Business University of Iowa 1992

Experience: 1992-1994 - Sales Support at MicroSales Inc.

Won "Best Sales Support" award in 1993 and 1994

1994-Present - Sales Manager at MicroSales Inc.

Most sales in mid-South division for 2 years

:~> cat > ulcase92.dat <

> Resume for Monica King

> Career Objective: President of large computer services company

> Education: BA English Literature Bennington, 1985

> Experience: 1985-1986 - Mailroom at New World Services

> 1986-1987 - Secretary for sales management at

> New World Services

> 1988-1989 - Sales support at New World Services

> 1990-1992 - Salesman at New World Services

> 1993-1994 - Sales Manager at New World Services

> 1995 - Vice President of Sales and Marketing at

> New World Services

> 1996-Present - President of New World Services

> EOF

:~> cat > ulcase93.dat <

>Resume for Dan Miller

>Career Objective: Work as a sales support specialist for a services

>company

>Education: Plainview High School, 1996

>Experience: 1996 - Present: Mail room clerk at New World Services

>EOF

:~> cat sqldata.dat

7782,CLARK,MANAGER,7839,2572.50,,10,ulcase91.dat

7839,KING,PRESIDENT,,5500.00,,10,ulcase92.dat

7934,MILLER,CLERK,7782,920.00,,10,ulcase93.dat

3,建立控制文件

:~> cat >sld.ctl <

> LOAD DATA

> INFILE 'sqldata'

> INTO TABLE sqlload_test

> REPLACE

> FIELDS TERMINATED BY ','

> ( EMPNO INTEGER EXTERNAL,

> ENAME CHAR,

> JOB CHAR,

> MGR INTEGER EXTERNAL,

> SAL DECIMAL EXTERNAL,

> COMM DECIMAL EXTERNAL,

> DEPTNO INTEGER EXTERNAL,

> RES_FILE FILLER CHAR,

> "RESUME" LOBFILE (RES_FILE) TERMINATED BY EOF NULLIF RES_FILE = 'NONE'

> )

> EOF

:~>

4,导入

:~> sqlldr hyf/hyf control=sld.ctl data=sqldata

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Mar 28 13:30:23 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 3

5,查看日志

:~> cat sld.log

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Mar 28 13:30:23 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: sld.ctl

Data File: sqldata.dat

Bad File: sqldata.bad

Discard File: none specified

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array: 64 rows, maximum of 256000 bytes

Continuation: none specified

Path used: Conventional

Table SQLLOAD_TEST, loaded from every logical record.

Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl Datatype

------------------------------ ---------- ----- ---- ---- ---------------------

EMPNO FIRST * , CHARACTER

ENAME NEXT * , CHARACTER

JOB NEXT * , CHARACTER

MGR NEXT * , CHARACTER

SAL NEXT * , CHARACTER

COMM NEXT * , CHARACTER

DEPTNO NEXT * , CHARACTER

RES_FILE NEXT * , CHARACTER

(FILLER FIELD)

"RESUME" DERIVED * EOF CHARACTER

Dynamic LOBFILE. Filename in field RES_FILE

NULL if RES_FILE = 0X4e4f4e45(character 'NONE')Table SQLLOAD_TEST:

3 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array: 132096 bytes(64 rows)

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 3

Total logical records rejected: 0

Total logical records discarded: 0

Run began on Fri Mar 28 13:30:23 2008

Run ended on Fri Mar 28 13:30:24 2008

Elapsed time was: 00:00:00.28

CPU time was: 00:00:00.03

6,SQLPLUS登录进行数据验证

SQL> select count(*) from sqlload_test;

COUNT(*)

----------

3

SQL> set long 2000

SQL> SQL> select resume from sqlload_test;

RESUME

--------------------------------------------------------------------------------

Resume for Mary Clark

Career Objective: Manage a sales team with consistent record breaking

performance.

Education: BA Business University of Iowa 1992

Experience: 1992-1994 - Sales Support at MicroSales Inc.

Won "Best Sales Support" award in 1993 and 1994

1994-Present - Sales Manager at MicroSales Inc.

Most sales in mid-South division for 2 years

esume for Monica King

RESUME

--------------------------------------------------------------------------------

Career Objective: President of large computer services company

Education: BA English Literature Bennington, 1985

Experience: 1985-1986 - Mailroom at New World Services

1986-1987 - Secretary for sales management at

New World Services

1988-1989 - Sales support at New World Services

1990-1992 - Salesman at New World Services

1993-1994 - Sales Manager at New World Services

1995 - Vice President of Sales and Marketing at

New World Services

1996-Present - President of New World Services

RESUME

--------------------------------------------------------------------------------

Resume for Dan Miller

Career Objective: Work as a sales support specialist for a services

company

Education: Plainview High School, 1996

Experience: 1996 - Present: Mail room clerk at New World Services