Hi Dongmei,
Please read the case study below. You have to unload all text field to a
unique file for each row.
Regards
SQL*Loader Case Studies 4-39
Case 9: Loading LOBFILEs (CLOBs)
Case 9 demonstrates
Adding a CLOB column called RESUME to the table emp.
Using a FILLER field (RES_FILE).
Loading multiple LOBFILEs into the emp table.
Control File
The control file is ULCASE9.CTL. It loads new emp records with the resume
for
each employee coming from a different file.
LOAD DATA
INFILE *
INTO TABLE EMP
REPLACE
FIELDS TERMINATED BY ¡¯,¡¯
( EMPNO INTEGER EXTERNAL,
ENAME CHAR,
JOB CHAR,
MGR INTEGER EXTERNAL,
SAL DECIMAL EXTERNAL,
COMM DECIMAL EXTERNAL,
DEPTNO INTEGER EXTERNAL,
1) RES_FILE FILLER CHAR,
2) "RESUME" LOBFILE (RES_FILE) TERMINATED BY EOF NULLIF RES_FILE = ¡¯NONE¡¯
)
BEGINDATA
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
7566,JONES,MANAGER,7839,3123.75,,20,ulcase94.dat
7499,ALLEN,SALESMAN,7698,1600.00,300.00,30,ulcase95.dat
7654,MARTIN,SALESMAN,7698,1312.50,1400.00,30,ulcase96.dat
7658,CHAN,ANALYST,7566,3450.00,,20,NONE
Notes:
1. This is a filler field. The filler field is assigned values from the
datafield to which
it is mapped. See Secondary Data Files (SDFs) and LOBFILES on page 3-20 for
more information.?Case 9: Loading LOBFILEs (CLOBs)
4-40 Oracle8i Utilities
2. RESUME is loaded as a CLOB. The LOBFILE function is used to specify the
name of the field that specifies name of the file which contains the data
for the
LOB field. See Loading LOB Data Using LOBFILEs on page 5-101 for more
information.
Input Data Files
>>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
>>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 - Saleman 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
>>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
>>ulcase94.dat<<?Case 9: Loading LOBFILEs (CLOBs)
SQL*Loader Case Studies 4-41
Resume for Alyson Jones
Career Objective: Work in senior sales management for a vibrant and
growing company
Education: BA Philosophy Howard Univerity 1993
Experience: 1993 - Sales Support for New World Services
1994-1995 - Salesman for New World Services. Led in
US sales in both 1994 and 1995.
1996 - present - Sales Manager New World Services. My
sales team has beat its quota by at least 15% each
year.
>>ulcase95.dat<<
Resume for David Allen
Career Objective: Senior Sales man for agressive Services company
Education: BS Business Administration, Weber State 1994
Experience: 1993-1994 - Sales Support New World Services
1994-present - Salesman at New World Service. Won sales
award for exceeding sales quota by over 20%
in 1995, 1996.
>>ulcase96.dat<<
Resume for Tom Martin
Career Objective: Salesman for a computing service company
Education: 1988 - BA Mathematics, University of the North
Experience: 1988-1992 Sales Support, New World Services
1993-present Salesman New World Services
Invoking SQL*Loader
Invoke SQL*Loader with a command such as:
sqlldr sqlldr/test control=ulcase9.ctl data=ulcase9.dat
Additional Information: The command "sqlldr" is a UNIX-specific invocation.
To
invoke SQL*Loader, see the Oracle operating system-specific
documentation.?Case 9: Loading LOBFILEs (CLOBs)
4-42 Oracle8i Utilities
Log File
The following shows a portion of the log file:
Control File: ulcase9.ctl
Data File: ulcase9.ctl
Bad File: ulcase9.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 65536 bytes
Continuation: none specified
Path used: Conventional
Table EMP, 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 * WHT CHARACTER
Dynamic LOBFILE. Filename in field RES_FILE
NULL if RES_FILE = 0X4e4f4e45(character ¡¯NONE¡¯)
Table EMP:
7 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: 63984 bytes(31 rows)
Space allocated for memory besides bind array: 0 bytes?Case 9: Loading
LOBFILEs (CLOBs)
Total logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Sun Nov 08 11:31:11 1998
Run ended on Sun Nov 08 11:31:19 1998
Elapsed time was: 00:00:08.14
CPU time was: 00:00:00.09
Received on Thu Nov 09 2000 - 00:34:13 CST
