SQL*Loader之CASE9
CASE9
1. SQL脚本
[oracle@node3 ulcase]$ cat ulcase9.sql
set termout off
rem host write sys$output "Building case 9 demonstration tables. Please wait"
drop table emp;
create table emp
(empno number(4) not null,
ename char(10),
job char(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2),
resume clob);
exit
2. 控制文件
[oracle@node3 ulcase]$ cat ulcase9.ctl
-- Copyright (c) 1991, 2004 Oracle. All rights reserved. -- NAME -- ulcase9.ctl - SQL*Loader Case Study 9: Loading LOBFILEs (CLOBs) -- -- DESCRIPTION -- This case study demonstrates the following: -- Adding a CLOB column called resume to table emp. -- -- Using a filler field (res_file). -- -- Loading multiple LOBFILEs into the emp table. -- -- TO RUN THIS CASE STUDY: -- 1. Before executing this control file, log in to SQL*Plus as -- scott/tiger. Enter @ulcase9 to execute the SQL script for -- this case study. This prepares and populates tables and -- then returns you to the system prompt. -- -- 2. At the system prompt, invoke the case study as follows: -- sqlldr USERID=scott/tiger CONTROL=ulcase9.ctl LOG=ulcase9.log -- -- NOTES ABOUT THIS CONTROL FILE -- This is an example of using SQL Loader to load LOBs from -- secondary data file. -- -- There is one file per resume (the "TERMINATED BY EOF" clause -- indicates this) and the name of the file containing the resume -- is in field res_file. -- -- res_file is a filler field. The filler field is assigned values -- from the data field to which it is mapped. This means that the -- file name stored in the field is not loaded into any field in -- the table. -- -- The resume column is loaded as a CLOB. The LOBFILE function specifies -- the field name in which the name of the file that contains data for -- LOB field is provided. -- -- The field name for column RESUME is in quotation marks because -- RESUME is also a keyword for SQL*Loader. The quotation marks force -- SQL*Loader to treat it as a column name instead. -- 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, RES_FILE FILLER CHAR, "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
3. 数据文件
在这个案例中，最后一列RESUME是CLOB类型，它的内容以ulcase91.dat等文件存在。在这里，我们只列出其中一个数据文件，其它类似。
[oracle@node3 ulcase]$ 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
执行后结果：
[oracle@node3 ulcase]$ sqlplus scott/tiger @ulcase9.sql
[oracle@node3 ulcase]$ sqlldr userid=scott/tiger control=ulcase9.ctl
SQL> set long 9999
SQL> select empno,resume from emp where rownum=1;
EMPNO
-----
RESUME
--------------------------------------------------------------------------------
7782
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.
EMPNO
-----
RESUME
--------------------------------------------------------------------------------
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
查看日志文件：
[oracle@node3 ulcase]$ cat ulcase9.log
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Sep 19 03:48:24 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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 256000 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 * EOF 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: 132096 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Fri Sep 19 03:48:24 2014
Run ended on Fri Sep 19 03:48:25 2014
Elapsed time was: 00:00:00.45
CPU time was: 00:00:00.11
浙公网安备 33010602011771号