SQLLDR Case Study 9: Loading LOBFILEs (CLOBs)

一 描述

Case Study 9: Loading LOBFILEs (CLOBs): Adds a CLOB column called resume to the table emp, uses a FILLER field (res_file), and loads multiple LOBFILEs into the emp table.

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

二 操作环境

OS info

windows server2003 32bit

DB info

连接到:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> set lines 150
SQL> COL PRODUCT FORMAT A55
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                                 VERSION         STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL                                                  9.0.1.1.1       Production
Oracle9i Enterprise Edition                             9.0.1.1.1       Production
PL/SQL                                                  9.0.1.1.1       Production
TNS for 32-bit Windows:                                 9.0.1.1.0       Production

SQL>

other

目录结构:C:\oracle\product\9.2.0\db_1\rdbms\demo

三 过程设计

1.准备的源flat数据

>>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 - 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

>>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<<

                      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

2.相关文件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
rem

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

3.相关文件ulcase9.ctl
-- This is an example of using SQL Loader to load LOBs from secondaray data
-- file.  In this example, we are loading the RESUME filed in the EMP table.
-- We have 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.
--
-- Note that since RES_FILE is a FILLER field, the file name stored in the
-- field is not loaded into any field in the table.
-- Also note that the field name for column RESUME is quoted since RESUME is
-- also a keyword for SQL Loader.  The quotes 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

4.以scott用户连接数据库
5.scott用户运行ulcase9.sql初始化环境
6.在cmd命令下执行sqlldr加载ulcase9.ctl控制文件命令
7.查看sqlldr日志信息
8.查看sqlldr bad文件信息
9.查看数据库加载成功的数据

四 详细步骤操作

1.以scott用户连接数据库
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on Wed Aug 8 11:25:01 2012

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL>

2.scott用户运行ulcase9.sql初始化环境
SQL> @ulcase9
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

C:\oracle\product\9.2.0\db_1\rdbms\demo>

3.在cmd命令下执行sqlldr加载ulcase9.ctl控制文件命令
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlldr "scott/tiger" control=ulcase9.ctl

SQL*Loader: Release 9.0.1.1.1 - Production on Wed Aug 8 11:25:55 2012

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 7

C:\oracle\product\9.2.0\db_1\rdbms\demo>

4.查看sqlldr日志信息

SQL*Loader: Release 9.0.1.1.1 - Production on Wed Aug 8 11:25:55 2012

(c) Copyright 2001 Oracle Corporation.  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 Wed Aug 08 11:25:55 2012
Run ended on Wed Aug 08 11:25:56 2012

Elapsed time was:     00:00:01.00
CPU time was:         00:00:00.06    

5.查看sqlldr bad文件信息
(注:无)
6.查看数据库加载成功的数据
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on Wed Aug 8 11:27:02 2012

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> set lines 120
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
RESUME
--------------------------------------------------------------------------------
      7782 CLARK      MANAGER         7839               2572.5                    10
                          Resume for Mary Clark

Career Objective: Manage a sa

      7839 KING       PRESIDENT                            5500                    10
                        Resume for Monica King

Career Objective: President of

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
RESUME
--------------------------------------------------------------------------------

      7934 MILLER     CLERK           7782                  920                    10
                         Resume for Dan Miller

Career Objective: Work as a sa

      7566 JONES      MANAGER         7839              3123.75                    20
                      Resume for Alyson Jones


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
RESUME
--------------------------------------------------------------------------------
Career Objective: Work in senio

      7499 ALLEN      SALESMAN        7698                 1600        300         30
                          Resume for David Allen

Career Objective: Senior Sal

      7654 MARTIN     SALESMAN        7698               1312.5       1400         30
                          Resume for Tom Martin

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
RESUME
--------------------------------------------------------------------------------

Career Objective: Salesman fo

      7658 CHAN       ANALYST         7566                 3450                    20



7 rows selected.

SQL> select count(*) from emp;

  COUNT(*)
----------
         7

SQL>

五 个人总结

六 资料参考引用

Oracle9i Database Utilities
Release 1 (9.0.1)
Part Number A90192-01

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11780477/viewspace-740340/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11780477/viewspace-740340/