用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