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

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

                          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

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

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