http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_concepts.htm#i1006494 -- ulcase9.sql rem rem $Header: ulcase9.sql 14-jul-99.14:28:05 mjaeger Exp $ rem rem ulcase9.sql rem rem Copyright (c) 1998, 1999, Oracle Corporation. All rights reserved. rem rem NAME rem ulcase9.sql - setup for SQL Loader example 9 rem rem DESCRIPTION rem Add RESUME column to EMP for example of using SQL Loader to load LOBs rem rem NOTES rem Assumes an EMP table already exists rem rem MODIFIED (MM/DD/YY) rem mjaeger 07/14/99 - bug 808870: OCCS: convert tabs, no long lines rem jstenois 06/17/99 - cleanup tables before load and show feedback rem jstenois 10/26/98 - demo of 8.1 features for sqlldr rem jstenois 10/26/98 - Created remset termout offrem 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-- ulcase.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 s because -- RESUME is also a keyword for SQL*Loader. The quotation s 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-- ulcase91.dat Resume for Mary ClarkCareer Objective: Manage a sales team with consistent record breaking performance.Education: BA Business University of Iowa 1992Experience: 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,93,...與91相似
-- ulcase9.sql
rem
rem $Header: ulcase9.sql 14-jul-99.14:28:05 mjaeger Exp $
rem
rem ulcase9.sql
rem
rem Copyright (c) 1998, 1999, Oracle Corporation. All rights reserved.
rem
rem NAME
rem ulcase9.sql - setup for SQL Loader example 9
rem
rem DESCRIPTION
rem Add RESUME column to EMP for example of using SQL Loader to load LOBs
rem
rem NOTES
rem Assumes an EMP table already exists
rem
rem MODIFIED (MM/DD/YY)
rem mjaeger 07/14/99 - bug 808870: OCCS: convert tabs, no long lines
rem jstenois 06/17/99 - cleanup tables before load and show feedback
rem jstenois 10/26/98 - demo of 8.1 features for sqlldr
rem jstenois 10/26/98 - Created
remset termout offrem 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-- ulcase.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 s because
-- RESUME is also a keyword for SQL*Loader. The quotation s 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-- ulcase91.dat
Resume for Mary ClarkCareer Objective: Manage a sales team with consistent record breaking
performance.Education: BA Business University of Iowa 1992Experience: 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,93,...與91相似
LOAD DATA
INFILE data.dat
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,
"RESUME" CHAR(20000)
)-- data.dat
7782,CLARK,MANAGER,7839,2572.50,,10,012...{19998個字符}我試過可以成功load進去