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
