The Case Studies of Sql*loader

The Case Studies

This chapter contains the following case studies:

Case 1: Loading Variable-Length Data

Loads stream format records in which the fields are delimited by commas and may be enclosed
by quotation marks. The data is found at the end of the control file.

Case 2: Loading Fixed-Format Fields:

Loads a datafile with fixed-length fields, stream-format records, all records the same length.

Case 3: Loading a Delimited, Free-Format File

Loads data from stream format records with delimited fields and sequence numbers. The data is
found at the end of the control file.

Case 4: Loading Combined Physical Records

Combines multiple physical records into one logical record corresponding to one database row

Case 5: Loading Data into Multiple Tables

Loads data into multiple tables in one run

Case 6: Loading Using the Direct Path Load Method

Loads data using the direct path load method

Case 7: Extracting Data from a Formatted Report

Extracts data from a formatted report

Case 8: Loading Partitioned Tables

Loads partitioned tables.

Case 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 10: Loading REF Fields and VARRAYs

Loads a customer table, which has a primary key as its OID and which stores order items in a
VARRAY and loads an order table which has a REF to the customer table and the order times in
a VARRAY.

Case Study Files

The distribution media for SQL*Loader contains files for each case:

• control files (for example, ULCASE1.CTL)

• data files (for example, ULCASE2.DAT)
• setup files (for example, ULCASE3.SQL)
If the sample data for the case study is contained in the control file, then there will be no .DAT
file for that case.

If there are no special setup steps for a case study, there may be no .SQL file for that case.
Starting (setup) and ending (cleanup) scripts are denoted by an S or E after the case number.

Table 4-1 lists the files associated with each case:

Table 4-1 Case Studies and Their Related Files

CAS
E .CTL .DAT .SQL
1 x x
2 x x
3 x x
4 x x x
5 x x x
6 x x x
7 x x x S, E


8 x x x
9 x x x
10 x x

Additional Information: The actual names of the case study files are operating system-dependent.
See your Oracle operating system-specific documentation for the exact names.

Tables Used in the Case Studies

The case studies are based upon the standard Oracle demonstration database tables EMP and
DEPT owned by SCOTT/TIGER. (In some of the case studies, additional columns have been
added.)

Contents of Table EMP

(empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2))

Contents of Table DEPT

(deptno NUMBER(2) NOT NULL,
dname VARCHAR2(14),
loc VARCHAR2(13))

References and Notes

The summary at the beginning of each case study contains page number references, directing you
to the sections of this guide that discuss the SQL*Loader feature being demonstrated in more
detail.

In the control file fragment and log file listing shown for each case study, the numbers that appear
to the left are not actually in the file; they are keyed to the numbered notes following the listing.
Do not use these numbers when you write your control files.


Running the Case Study SQL Scripts

You should run the SQL scripts ULCASE1.SQL and ULCASE3.SQL through ULCASE10.SQL
to prepare and populate the tables. Note that there is no ULCASE2.SQL as Case 2 is handled by
ULCASE1.SQL.

Case 1: Loading Variable-Length Data

Case 1 demonstrates

•
A simple control file identifying one table and three columns to be loaded. See
Identifying Data in the Control File with BEGINDATA.
•
Including data to be loaded from the control file itself, so there is no separate datafile. See
Identifying Data in the Control File with BEGINDATA.
•
Loading data in stream format, with both types of delimited fields -- terminated and
enclosed. See Delimited Fields.
Control File

The control file is ULCASE1.CTL:

1) LOAD DATA

2) INFILE *

3) INTO TABLE dept

4) FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

5) (deptno, dname, loc)

6) BEGINDATA
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND11,"ART",SALEM13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER42,"INT'L","SAN FRAN"

Notes:

The LOAD DATA statement is required at the beginning of the control file.

INFILE * specifies that the data is found in the control file and not in an external file.

The INTO TABLE statement is required to identify the table to be loaded (DEPT) into. By
default, SQL*Loader requires the table to be empty before it inserts any records.

FIELDS TERMINATED BY specifies that the data is terminated by commas, but may also
be enclosed by quotation marks. Datatypes for all fields default to CHAR.

Specifies that the names of columns to load are enclosed in parentheses. Since no datatype is
specified, the default is a character of length 255.


BEGINDATA specifies the beginning of the data.

Invoking SQL*Loader

To run this example, invoke SQL*Loader with the command:

sqlldr userid=scott/tiger control=ulcase1.ctl log=ulcase1.log

SQL*Loader loads the DEPT table and creates the log file.

Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke
SQL*Loader on your operating system, refer to your Oracle operating system-specific
documentation.

Log File

The following shows a portion of the log file:

Control File: ulcase1.ctl

Data File: ulcase1.ctl
Bad File: ulcase1.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytesContinuation: none specifiedPath used: Conventional

Table DEPT, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name Position Len Term Encl
Datatype

1) DEPTNO FIRST * , O(")
CHARACTER

DNAME NEXT * , O(")
CHARACTER
2) LOC NEXT * , O(")
CHARACTER

Table DEPT:
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: 65016 bytes(84rows)
Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Sun Nov 08 11:08:19 1998Run ended on Sun Nov 08 11:08:20 1998

Elapsed time was: 00:00:01.16
CPU time was: 00:00:00.10

Notes:

Position and length for each field are determined for each record, based on delimiters in the
input file.

WHT signifies that field LOC is terminated by WHITESPACE. The notation O(") signifies
optional enclosure by quotation marks.

Case 2: Loading Fixed-Format Fields

Case 2 demonstrates

• A separate datafile. See INFILE: Specifying Datafiles.
• Data conversions. See Datatype Conversions.
In this case, the field positions and datatypes are specified explicitly.

Control File

The control file is ULCASE2.CTL.

1) LOAD DATA
2) INFILE 'ulcase2.dat'
3) INTO TABLE emp
4) (empno POSITION(01:04) INTEGER EXTERNAL,


ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR,
mgr POSITION(27:30) INTEGER EXTERNAL,
sal POSITION(32:39) DECIMAL EXTERNAL,
comm POSITION(41:48) DECIMAL EXTERNAL,


5) deptno POSITION(50:51) INTEGER EXTERNAL)

Notes:


The LOAD DATA statement is required at the beginning of the control file.
The name of the file containing data follows the keyword INFILE.
The INTO TABLE statement is required to identify the table to be loaded into.
Lines 4 and 5 identify a column name and the location of the data in the datafile to be loaded


into that column. EMPNO, ENAME, JOB, and so on are names of columns in table EMP.
The datatypes (INTEGER EXTERNAL, CHAR, DECIMAL EXTERNAL) identify the
datatype of data fields in the file, not of corresponding columns in the EMP table.

Note that the set of column specifications is enclosed in parentheses.

Datafile

Below are a few sample data lines from the file ULCASE2.DAT. Blank fields are set to null

automatically.
7782 CLARK MANAGER 7839 2572.50 10
7839 KING PRESIDENT 5500.00 10
7934 MILLER CLERK 7782 920.00 10
7566 JONES MANAGER 7839 3123.75 20
7499 ALLEN SALESMAN 7698 1600.00 300.00 30
7654 MARTIN SALESMAN 7698 1312.50 1400.00 30

Invoking SQL*Loader

Invoke SQL*Loader with a command such as:

sqlldr userid=scott/tiger control=ulcase2.ctl log=ulcase2.log

EMP records loaded in this example contain department numbers. Unless the DEPT table is
loaded first, referential integrity checking rejects these records (if referential integrity constraints
are enabled for the EMP table).

Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke
SQL*Loader on your operating system, refer to your Oracle operating system-specific
documentation.

Log File

The following shows a portion of the log file:

Control File: ulcase2.ctl

Data File: ulcase2.dat

Bad File: ulcase2.bad

Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0Errors allowed: 50


Bind array: 64 rows, maximum of 65536 bytesContinuation: none specifiedPath used: Conventional

Table EMP, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name Position Len Term Encl
Datatype

--------------
EMPNO 1:4 4
CHARACTER
ENAME 6:15 10
CHARACTER
JOB 17:25 9
CHARACTER
MGR 27:30 4
CHARACTER
SAL 32:39 8
CHARACTER
COMM 41:48 8
CHARACTER
DEPTNO 50:51 2
CHARACTER

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: 65520 bytes(1092rows)
Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Sun Nov 08 11:09:31 1998Run ended on Sun Nov 08 11:09:32 1998

Elapsed time was: 00:00:00.63
CPU time was: 00:00:00.16


Case 3: Loading a Delimited, Free-Format File

Case 3 demonstrates

•
Loading data (enclosed and terminated) in stream format. See Delimited Fields.
•
Loading dates using the datatype DATE. See DATE.
•
Using SEQUENCE numbers to generate unique keys for loaded data. See Setting a
Column to a Unique Sequence Number.
•
Using APPEND to indicate that the table need not be empty before inserting new records.
See Loading into Empty and Non-Empty Tables.
•
Using Comments in the control file set off by double dashes. See Control File Basics.
•
Overriding general specifications with declarations for individual fields. See Specifying
Field Conditions.
Control File

This control file loads the same table as in Case 2, but it loads three additional columns
(HIREDATE, PROJNO, LOADSEQ). The demonstration table EMP does not have columns
PROJNO and LOADSEQ. So if you want to test this control file, add these columns to the EMP
table with the command:

ALTER TABLE EMP ADD (PROJNO NUMBER, LOADSEQ NUMBER)

The data is in a different format than in Case 2. Some data is enclosed in quotation marks, some
is set off by commas, and the values for DEPTNO and PROJNO are separated by a colon.

1) -- Variable-length, delimited and enclosed data format

LOAD DATA
2) INFILE *
3) APPEND

INTO TABLE emp4) FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr,

5) hiredate DATE(20) "DD-Month-YYYY",
sal, comm, deptno CHAR TERMINATED BY ':',
projno,

6) loadseq SEQUENCE(MAX,1))
7) BEGINDATA
8) 7782, "Clark", "Manager", 7839, 09-June-1981, 2572.50,,


10:101
7839, "King", "President", , 17-November-1981,5500.00,,10:1027934, "Miller", "Clerk", 7782, 23-January-1982, 920.00,,
10:102

7566, "Jones", "Manager", 7839, 02-April-1981, 3123.75,,

20:101
7499, "Allen", "Salesman", 7698, 20-February-1981, 1600.00,
(same line continued) 300.00, 30:1037654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50,
(same line continued) 1400.00, 3:1037658, "Chan", "Analyst", 7566, 03-May-1982, 3450,, 20:101
Notes:

Comments may appear anywhere in the command lines of the file, but they should not appear
in data. They are preceded with a double dash that may appear anywhere on a line.

INFILE * specifies that the data is found at the end of the control file.

Specifies that the data can be loaded even if the table already contains rows. That is, the table
need not be empty.

The default terminator for the data fields is a comma, and some fields may be enclosed by
double quotation marks (").

The data to be loaded into column HIREDATE appears in the format DD-Month-YYYY. The
length of the date field is a maximum of 20. If a length is not specified, the length is a
maximum of 20. If a length is not specified, then the length depends on the length of the
date mask.

The SEQUENCE function generates a unique value in the column LOADSEQ. This function
finds the current maximum value in column LOADSEQ and adds the increment (1) to it
to obtain the value for LOADSEQ for each row inserted.

BEGINDATA specifies the end of the control information and the beginning of the data.

Although each physical record equals one logical record, the fields vary in length so that
some records are longer than others. Note also that several rows have null values for
COMM.

Invoking SQL*Loader

Invoke SQL*Loader with a command such as:

sqlldr userid=scott/tiger control=ulcase3.ctl log=ulcase3.log

Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke
SQL*Loader on your operating system, see your Oracle operating system-specific
documentation.

Log File

The following shows a portion of the log file:

Control File: ulcase3.ctl
Data File: ulcase3.ctl


Bad File: ulcase3.bad
Discard File: none specified


(Allow all discards)

Number to load: ALL
Number to skip: 0Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytesContinuation: none specifiedPath used: Conventional

Table EMP, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl
Datatype

EMPNO FIRST * , O(")
CHARACTER
ENAME NEXT * , O(")
CHARACTER
JOB NEXT * , O(")
CHARACTER
MGR NEXT * , O(")
CHARACTER
HIREDATE NEXT 20 , O(") DATEDD-Month-YYYY
SAL NEXT * , O(")
CHARACTER
COMM NEXT * , O(")
CHARACTER
DEPTNO NEXT * : O(")
CHARACTER
PROJNO NEXT * , O(")
CHARACTER
LOADSEQSEQUENCE (MAX, 1)

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: 65379 bytes(31rows)
Space allocated for memory besides bind array: 0 bytes


Total logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Sun Nov 08 11:13:41 1998Run ended on Sun Nov 08 11:13:46 1998

Elapsed time was: 00:00:04.83
CPU time was: 00:00:00.09

Case 4: Loading Combined Physical Records

Case 4 demonstrates:

•
Combining multiple physical records to form one logical record with CONTINUEIF; see
Assembling Logical Records from Physical Records.
•
Inserting negative numbers.
•
Indicating with REPLACE that the table should be emptied before the new data is
inserted; see Loading into Empty and Non-Empty Tables.
•
Specifying a discard file in the control file using DISCARDFILE; see Specifying the
Discard File.
•
Specifying a maximum number of discards using DISCARDMAX; see Specifying the
Discard File.
•
Rejecting records due to duplicate values in a unique index or due to invalid data values;
see Rejected Records.

Control File

The control file is ULCASE4.CTL:

LOAD DATA

INFILE 'ulcase4.dat'

1) DISCARDFILE 'ulcase4.dsc'

2) DISCARDMAX 999

3) REPLACE

4) CONTINUEIF THIS (1) = '*'
INTO TABLE emp(empno POSITION(1:4)
ename POSITION(6:15)
job POSITION(17:25)
mgr POSITION(27:30)
sal POSITION(32:39)
comm POSITION(41:48)
deptno POSITION(50:51)
hiredate POSITION(52:60)

Notes:

INTEGER EXTERNAL,
CHAR,
CHAR,
INTEGER EXTERNAL,
DECIMAL EXTERNAL,
DECIMAL EXTERNAL,
INTEGER EXTERNAL,
INTEGER EXTERNAL)



DISCARDFILE specifies a discard file named ULCASE4.DSC.

DISCARDMAX specifies a maximum of 999 discards allowed before terminating the run
(for all practical purposes, this allows all discards).

REPLACE specifies that if there is data in the table being loaded, then SQL*Loader should
delete that data before loading new data.

CONTINUEIF THIS specifies that if an asterisk is found in column 1 of the current record,
then the next physical record after that record should be appended to it to from the logical
record. Note that column 1 in each physical record should then contain either an asterisk
or a non-data value.

Data File

The datafile for this case, ULCASE4.DAT, is listed below. Note the asterisks in the first position
and, though not visible, a new line indicator is in position 20 (following "MA", "PR", and so on).
Note that CLARK's commission is -10, and SQL*Loader loads the value converting it to a

negative number.
*7782 CLARK
MANAGER 7839 2572.50 -10 2512-NOV-85
*7839 KING
PRESIDENT 5500.00 2505-APR-83
*7934 MILLER
CLERK 7782 920.00 2508-MAY-80
*7566 JONES
MANAGER 7839 3123.75 2517-JUL-85
*7499 ALLEN
SALESMAN 7698 1600.00 300.00 25 3-JUN-84
*7654 MARTIN
SALESMAN 7698 1312.50 1400.00 2521-DEC-85
*7658 CHAN
ANALYST 7566 3450.00 2516-FEB-84
* CHEN
ANALYST 7566 3450.00 2516-FEB-84
*7658 CHIN
ANALYST 7566 3450.00 2516-FEB-84
Rejected Records

The last two records are rejected, given two assumptions. If there is a unique index created on
column EMPNO, then the record for CHIN will be rejected because his EMPNO is identical to
CHAN's. If EMPNO is defined as NOT NULL, then CHEN's record will be rejected because it
has no value for EMPNO.

Invoking SQL*Loader

Invoke SQL*Loader with a command such as:

sqlldr userid=scott/tiger control=ulcase4.ctl log=ulcase4.log

Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke
SQL*Loader on your operating system, see your operating Oracle system-specific


documentation.

Log File

The following is a portion of the log file:

Control File: ulcase4.ctl

Data File: ulcase4.dat
Bad File: ulcase4.bad
Discard File: ulcase4.dis

(Allow 999 discards)

Number to load: ALL
Number to skip: 0Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytesContinuation: 1:1 = 0X2a(character '*'), in current physicalrecord
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 1:4 4
CHARACTER
ENAME 6:15 10
CHARACTER
JOB 17:25 9
CHARACTER
MGR 27:30 4
CHARACTER
SAL 32:39 8
CHARACTER
COMM 41:48 8
CHARACTER
DEPTNO 50:51 2
CHARACTER
HIREDATE 52:60 9
CHARACTER

Record 8: Rejected - Error on table EMP.
ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."EMPNO")


Record 9: Rejected - Error on table EMP.
ORA-00001: unique constraint (SCOTT.EMPIX) violated


Table EMP:



7 Rows successfully loaded.

2 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: 65520 bytes(910rows)
Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 0
Total logical records read: 9
Total logical records rejected: 2
Total logical records discarded: 0

Run began on Sun Nov 08 11:49:42 1998Run ended on Sun Nov 08 11:49:42 1998

Elapsed time was: 00:00:00.69
CPU time was: 00:00:00.13

Bad File

The bad file, shown below, lists records 8 and 9 for the reasons stated earlier. (The discard file is
not created.)

*
CHEN ANALYST
7566 3450.00 2516-FEB-84
*
CHIN ANALYST
7566 3450.00 2516-FEB-84
Case 5: Loading Data into Multiple Tables

Case 5 demonstrates

•
Loading multiple tables. See Loading Data into Multiple Tables.
•
Using SQL*Loader to break down repeating groups in a flat file and load the data into
normalized tables -- one file record may generate multiple database rows
•
Deriving multiple logical records from each physical record. See Using Multiple INTO
TABLE Statements.
•
Using a WHEN clause. See Choosing which Rows to Load.
•
Loading the same field (EMPNO) into multiple tables.
Control File

The control file is ULCASE5.CTL.

-- Loads EMP records from first 23 characters

-- Creates and loads PROJ records for each PROJNO listed


-- for each employee
LOAD DATA
INFILE 'ulcase5.dat'
BADFILE 'ulcase5.bad'
DISCARDFILE 'ulcase5.dsc'


1) REPLACE

2) INTO TABLE emp(empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL)

2) INTO TABLE proj-- PROJ has two columns, both not null: EMPNO and PROJNO3) WHEN projno != ' '

(empno POSITION(1:4) INTEGER EXTERNAL,
3) projno POSITION(25:27) INTEGER EXTERNAL) -- 1st proj3) INTO TABLE proj4) WHEN projno != ' '

(empno POSITION(1:4) INTEGER EXTERNAL,
4) projno POSITION(29:31 INTEGER EXTERNAL) -- 2nd proj

2) INTO TABLE proj5) WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
5) projno POSITION(33:35) INTEGER EXTERNAL) -- 3rd proj

Notes:

REPLACE specifies that if there is data in the tables to be loaded (EMP and PROJ),
SQL*loader should delete the data before loading new rows.


Multiple INTO clauses load two tables, EMP and PROJ. The same set of records is processed
three times, using different combinations of columns each time to load table PROJ.

WHEN loads only rows with non-blank project numbers. When PROJNO is defined as
columns 25...27, rows are inserted into PROJ only if there is a value in those columns.

When PROJNO is defined as columns 29...31, rows are inserted into PROJ only if there is a
value in those columns.

When PROJNO is defined as columns 33...35, rows are inserted into PROJ only if there is a
value in those columns.

Data File

1234 BAKER 10 9999 101 102 103
1234 JOKER 10 9999 777 888 999
2664 YOUNG 20 2893 425 abc 102
5321 OTOOLE 10 9999 321 55 40


2134 FARMER 20 4555 236 456
2414 LITTLE 20 5634 236 456 40
6542 LEE 10 4532 102 321 14
2849 EDDS xx 4555 294 40
4532 PERKINS 10 9999 40
1244 HUNT 11 3452 665 133 456
123 DOOLITTLE 12 9940 132
1453 MACDONALD 25 5532 200

Invoking SQL*Loader

Invoke SQL*Loader with a command such as:

sqlldr userid=scott/tiger control=ulcase5.ctl log=ulcase5.log

Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke
SQL*Loader on your operating system, see your Oracle operating system-specific
documentation.

Log File

The following is a portion of the log file:

Control File: ulcase5.ctl

Data File: ulcase5.dat
Bad File: ulcase5.bad
Discard File: ulcase5.dis

(Allow all discards)

Number to load: ALL
Number to skip: 0Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytesContinuation: none specifiedPath 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 1:4 4
CHARACTER
ENAME 6:15 10
CHARACTER
DEPTNO 17:18 2
CHARACTER
MGR 20:23 4


CHARACTER

Table PROJ, loaded when PROJNO != 0X202020(character ' ')
Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl
Datatype

EMPNO 1:4 4
CHARACTER
PROJNO 25:27 3
CHARACTER

Table PROJ, loaded when PROJNO != 0X202020(character ' ')
Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl
Datatype

EMPNO 1:4 4
CHARACTER
PROJNO 29:31 3
CHARACTER

Table PROJ, loaded when PROJNO != 0X202020(character ' ')
Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl
Datatype

EMPNO 1:4 4
CHARACTER
PROJNO 33:35 3
CHARACTER


1) Record 2: Rejected - Error on table EMP, column DEPTNO.
1) ORA-00001: unique constraint (SCOTT.EMPIX) violated


1) Record 8: Rejected - Error on table EMP, column DEPTNO.
1) ORA-01722: invalid number


1) Record 3: Rejected - Error on table PROJ, column PROJNO.
1) ORA-01722: invalid number


Table EMP:
2) 9 Rows successfully loaded.
2) 3 Rows not loaded due to data errors.
2) 0 Rows not loaded because all WHEN clauses were failed.



2) 0 Rows not loaded because all fields were null.


Table PROJ:
3) 7 Rows successfully loaded.
3) 2 Rows not loaded due to data errors.
3) 3 Rows not loaded because all WHEN clauses were failed.
3) 0 Rows not loaded because all fields were null.


Table PROJ:
4) 7 Rows successfully loaded.
4) 3 Rows not loaded due to data errors.
4) 2 Rows not loaded because all WHEN clauses were failed.
4) 0 Rows not loaded because all fields were null.


Table PROJ:
5) 6 Rows successfully loaded.
5) 3 Rows not loaded due to data errors.
5) 3 Rows not loaded because all WHEN clauses were failed.
5) 0 Rows not loaded because all fields were null.


Space allocated for bind array:
rows)
Space allocated for memory besides bind array:
65536 bytes(1024
0 bytes
Total logical records skipped:
Total logical records read:
Total logical records rejected:
Total logical records discarded:
0
12
3
0
Run began on Sun Nov 08 11:54:39 1998Run ended on Sun Nov 08 11:54:40 1998
Elapsed time was:
CPU time was:
00:00:00.67
00:00:00.16
Notes:

Errors are not encountered in the same order as the physical records due to buffering (array
batch). The bad file and discard file contain records in the same order as they appear in
the log file.

Of the 12 logical records for input, three rows were rejected (rows for JOKER, YOUNG, and
EDDS). No data was loaded for any of the rejected records.

Nine records met the WHEN clause criteria, and two (JOKER and YOUNG) were rejected
due to data errors.

Ten records met the WHEN clause criteria, and three (JOKER, YOUNG, and EDDS) were


rejected due to data errors.

Nine records met the WHEN clause criteria, and three (JOKER, YOUNG, and EDDS) were
rejected due to data errors.

Loaded Tables

These are results of this execution of SQL*Loader:

SQL> SELECT empno, ename, mgr, deptno FROM emp;
EMPNO ENAME MGR DEPTNO

1234 BAKER 9999 10
5321 OTOOLE 9999 10
2134 FARMER 4555 20
2414 LITTLE 5634 20
6542 LEE 4532 10
4532 PERKINS 9999 10
1244 HUNT 3452 11
123 DOOLITTLE 9940 12
1453 ALBERT 5532 25

SQL> SELECT * from PROJ order by EMPNO;

EMPNO PROJNO

123 132
1234 101
1234 103
1234 102
1244 665
1244 456
1244 133
1453 200
2134 236
2134 456
2414 236
2414 456
2414 40
4532 40
5321 321
5321 40
5321 55
6542 102
6542 14
6542 321

Case 6: Loading Using the Direct Path Load Method

This case study loads the EMP table using the direct path load method and concurrently builds all
indexes. It illustrates the following functions:


•
Use of the direct path load method to load and index data. See Chapter 8, "SQL*Loader:
Conventional and Direct Path Loads".
•
How to specify the indexes for which the data is pre-sorted. See Pre-sorting Data for
Faster Indexing.
•
Loading all-blank numeric fields as null. See Loading All-Blank Fields.
•
The NULLIF clause. See NULLIF Keyword.
Note: Specify the name of the table into which you want to load data; otherwise, you will
see LDR-927. Specifying DIRECT=TRUE as a command-line parameter is not an option
when loading into a synonym for a table.

In this example, field positions and datatypes are specified explicitly.

Control File

The control file is ULCASE6.CTL.

LOAD DATA
INFILE 'ulcase6.dat'
INSERT
INTO TABLE emp


1) SORTED INDEXES (empix)

2) (empno POSITION(01:04) INTEGER EXTERNAL NULLIF empno=BLANKS,
ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR,
mgr POSITION(27:30) INTEGER EXTERNAL NULLIF mgr=BLANKS,
sal POSITION(32:39) DECIMAL EXTERNAL NULLIF sal=BLANKS,
comm POSITION(41:48) DECIMAL EXTERNAL NULLIF comm=BLANKS,
deptno POSITION(50:51) INTEGER EXTERNAL NULLIF deptno=BLANKS)

Notes:

The SORTED INDEXES clause identifies indexes:presorting data:case study the indexes on
which the data is sorted. This clause indicates that the datafile is sorted on the columns in
the EMPIX index. This clause allows SQL*Loader to optimize index creation by
eliminating the sort phase for this data when using the direct path load method.

The NULLIF...BLANKS clause specifies that the column should be loaded as NULL if the
field in the datafile consists of all blanks. For more information, refer to Loading All-
Blank Fields.

Invoking SQL*Loader

Run the script ULCASE6.SQL as SCOTT/TIGER then enter the following at the command line:

sqlldr scott/tiger ulcase6.ctl direct=true log=ulcase6.log


Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke
SQL*Loader on your operating system, see your Oracle operating system-specific
documentation.

Log File

The following is a portion of the log file:

Control File: ulcase6.ctl

Data File: ulcase6.dat
Bad File: ulcase6.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0Errors allowed: 50
Continuation: none specifiedPath used: Direct

Table EMP, loaded from every logical record.
Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl
Datatype

--------------
EMPNO 1:4 4
CHARACTER
ENAME 6:15 10
CHARACTER
JOB 17:25 9
CHARACTER
MGR 27:30 4
CHARACTER
NULL if MGR = BLANKS
SAL 32:39 8
CHARACTER
NULL if SAL = BLANKS
COMM 41:48 8
CHARACTER
NULL if COMM = BLANKS
DEPTNO 50:51 2
CHARACTER
NULL if EMPNO = BLANKS

The following index(es) on table EMP were processed:
index SCOTT.EMPIX loaded successfully with 7 keys


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.

Bind array size not used in direct path.
Space allocated for memory besides bind array: 0 bytes


Total logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 0
Total logical records discarded: 0


Run began on Sun Nov 08 11:15:28 1998
Run ended on Sun Nov 08 11:15:31 1998


Elapsed time was: 00:00:03.22
CPU time was: 00:00:00.10


Case 7: Extracting Data from a Formatted Report

In this case study, SQL*Loader's string processing functions extract data from a formatted report.
It illustrates the following functions:

• Using SQL*Loader with an INSERT trigger (see the chapter on database triggers in
Oracle8i Application Developer's Guide - FundamentalsError! Hyperlink reference
not valid.Use of the SQL string to manipulate data; see Applying SQL Operators to
Fields.

•
Different initial and trailing delimiters; see Specifying Delimiters.
•
Use of SYSDATE; see Setting a Column to the Current Date.
•
Use of the TRAILING NULLCOLS clause; see TRAILING NULLCOLS.
•
Ambiguous field length warnings; see Conflicting Native Datatype Field Lengths and
Conflicting Character Datatype Field Lengths.
Note: This example creates a trigger that uses the last value of unspecified fields.

Data File

The following listing of the report shows the data to be loaded:

Today's Newly Hired EmployeesDept Job Manager MgrNo Emp Name EmpNo Salary(Comm)


-
20 Salesman Blake 7698 Shepard(3%)

Falstaff
(5%)

Major(14%)
30 Clerk Scott 7788 Conrad

Ford
DeSilva
Manager King 7839 Provo

8061

8066

8064

8062
7369
8063
8065

$1,600.00
$1,250.00
$1,250.00
$1,100.00

$800.00
$2,975.00

Insert Trigger

In this case, a BEFORE INSERT trigger is required to fill in department number, job name, and
manager's number when these fields are not present on a data line. When values are present, they
should be saved in a global variable. When values are not present, the global variables are used.

The INSERT trigger and the package defining the global variables is:

CREATE OR REPLACE PACKAGE uldemo7 AS -- Global Package

Variables
last_deptno NUMBER(2);
last_job VARCHAR2(9);
last_mgr NUMBER(4);
END uldemo7;

/

CREATE OR REPLACE TRIGGER uldemo7_emp_insertBEFORE INSERT ON empFOR EACH ROW

BEGIN
IF :new.deptno IS NOT NULL THENuldemo7.last_deptno := :new.deptno; -- save value for later
ELSE

:new.deptno := uldemo7.last_deptno; -- use last valid value
END IF;
IF :new.job IS NOT NULL THEN

uldemo7.last_job := :new.job;
ELSE


:new.job := uldemo7.last_job;
END IF;
IF :new.mgr IS NOT NULL THEN


uldemo7.last_mgr := :new.mgr;
ELSE
:new.mgr := uldemo7.last_mgr;


END IF;
END;
/

Note: The phrase FOR EACH ROW is important. If it was not specified, the INSERT trigger
would only fire once for each array of inserts because SQL*Loader uses the array interface.


Control File

The control file is ULCASE7.CTL.

LOAD DATA
INFILE 'ULCASE7.DAT'
APPEND
INTO TABLE emp


1) WHEN (57) = '.'
2) TRAILING NULLCOLS
3) (hiredate SYSDATE,
4) deptno POSITION(1:2) INTEGER EXTERNAL(3)
5) NULLIF deptno=BLANKS,


job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
6) NULLIF job=BLANKS "UPPER(:job)",
7) mgr POSITION(28:31) INTEGER EXTERNAL

TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHARTERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNALTERMINATED BY WHITESPACE,

sal POSITION(51) CHAR TERMINATED BY WHITESPACE
8) "TO_NUMBER(:sal,'$99,999.99')",
9) comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%'

":comm * 100"
)


Notes:

The decimal point in column 57 (the salary field) identifies a line with data on it. All other
lines in the report are discarded.

The TRAILING NULLCOLS clause causes SQL*Loader to treat any fields that are missing
at the end of a record as null. Because the commission field is not present for every
record, this clause says to load a null commission instead of rejecting the record when
only six fields are found instead of the expected seven.

Employee's hire date is filled in using the current system date.

This specification generates a warning message because the specified length does not agree
with the length determined by the field's position. The specified length (3) is used.

Because the report only shows department number, job, and manager when the value
changes, these fields may be blank. This control file causes them to be loaded as null, and
an RDBMS insert trigger fills in the last valid value.

The SQL string changes the job name to uppercase letters.

It is necessary to specify starting position here. If the job field and the manager field were
both blank, then the job field's TERMINATED BY BLANKS clause would cause
SQL*Loader to scan forward to the employee name field. Without the POSITION clause,


the employee name field would be mistakenly interpreted as the manager field.

Here, the SQL string translates the field from a formatted character string into a number. The
numeric value takes less space and can be printed with a variety of formatting options.

In this case, different initial and trailing delimiters pick the numeric value out of a formatted
field. The SQL string then converts the value to its stored form.

Invoking SQL*Loader

Invoke SQL*Loader with a command such as:

sqlldr scott/tiger ulcase7.ctl ulcase7.log

Log File

The following is a portion of the log file:

1) SQL*Loader-307: Warning: conflicting lengths 2 and 3 specifiedfor column
DEPTNO table EMP
Control File: ulcase7.ctl
Data File: ulcase7.dat

Bad File: ulcase7.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytesContinuation: none specifiedPath used: Conventional

Table EMP, loaded when 57:57 = 0X2e(character '.')
Insert option in effect for this table: APPENDTRAILING NULLCOLS option in effect

Column Name Position Len Term Encl
Datatype

--------------
HIREDATE SYSDATE
DEPTNO 1:2 3
CHARACTER
NULL if DEPTNO = BLANKS
JOB 7:14 8 WHT
CHARACTER
NULL if JOB = BLANKS
MGR
SQL string for column : "UPPER(:job)"
28:31 4 WHT
CHARACTER
NULL if MGR = BLANKS


ENAME 34:41 8 WHT
CHARACTER

SQL string for column : "UPPER(:ename)"
EMPNO NEXT * WHT
CHARACTER
SAL 51 * WHT
CHARACTER

SQL string for column : "TO_NUMBER(:sal,'$99,999.99')"
COMM NEXT * (
CHARACTER

%
SQL string for column : ":comm * 100"

2)Record 1: Discarded - failed all WHEN clauses.
Record 2: Discarded - failed all WHEN clauses.
Record 3: Discarded - failed all WHEN clauses.
Record 4: Discarded - failed all WHEN clauses.
Record 5: Discarded - failed all WHEN clauses.
Record 6: Discarded - failed all WHEN clauses.
Record 10: Discarded - failed all WHEN clauses.

Table EMP:
6 Rows successfully loaded.
0 Rows not loaded due to data errors.

2)
7 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array: 65286 bytes(81rows)
Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 0
Total logical records read: 13
Total logical records rejected: 0
2) Total logical records discarded: 7

Run began on Sun Nov 08 11:16:30 1998Run ended on Sun Nov 08 11:16:31 1998

Elapsed time was: 00:00:00.75
CPU time was: 00:00:00.09

Notes:

A warning is generated by the difference between the specified length and the length derived
from the position specification.

The 6 header lines at the top of the report are rejected, as is the blank separator line in the
middle.


Dropping the Insert Trigger and the Global-Variable Package

After running the example, use ULCASE7E.SQL to drop the insert trigger and global-variable
package.

Case 8: Loading Partitioned Tables

Case 8 demonstrates

•
Partitioning of data. See Oracle8i ConceptsError! Hyperlink reference not
valid.Explicitly defined field positions and datatypes.
•
Loading using the fixed record length option.
Control File

The control file is ULCASE8.CTL. It loads the lineitem table with fixed length records,
partitioning the data according to shipdate.

LOAD DATA

1)
INFILE 'ulcase10.dat' "fix 129"

BADFILE 'ulcase10.bad'

TRUNCATE

INTO TABLE lineitem

PARTITION (ship_q1)

2)
(l_orderkey position (1:6) char,
l_partkey position (7:11) char,
l_suppkey position (12:15) char,
l_linenumber position (16:16) char,
l_quantity position (17:18) char,
l_extendedprice position (19:26) char,
l_discount position (27:29) char,
l_tax position (30:32) char,
l_returnflag position (33:33) char,
l_linestatus position (34:34) char,
l_shipdate position (35:43) char,
l_commitdate position (44:52) char,
l_receiptdate position (53:61) char,
l_shipinstruct position (62:78) char,
l_shipmode position (79:85) char,
l_comment position (86:128) char)

Notes:

Specifies that each record in the datafile is of fixed length (129 characters in this example).
See Input Data and Datafiles.

Identifies the column name and location of the data in the datafile to be loaded into each
column.


Table Creation

In order to partition the data the lineitem table is created using four (4) partitions according to the
shipment date:

create table lineitem
(l_orderkey number,
l_partkey number,
l_suppkey number,
l_linenumber number,
l_quantity number,
l_extendedprice number,
l_discount number,
l_tax number,
l_returnflag char,
l_linestatus char,
l_shipdate date,
l_commitdate date,
l_receiptdate date,
l_shipinstruct char(17),
l_shipmode char(7),
l_comment char(43))
partition by range (l_shipdate)
(
partition ship_q1 values less than (TO_DATE('01-APR-1996', 'DDMON-
YYYY'))
tablespace p01,
partition ship_q2 values less than (TO_DATE('01-JUL-1996', 'DDMON-
YYYY'))
tablespace p02,
partition ship_q3 values less than (TO_DATE('01-OCT-1996', 'DDMON-
YYYY'))
tablespace p03,
partition ship_q4 values less than (TO_DATE('01-JAN-1997', 'DDMON-
YYYY'))
tablespace p04
)


Input Data File

The datafile for this case, ULCASE8.DAT, is listed below. Each record is 129 characters in
length. Note that five(5) blanks precede each record in the file.

1 151978511724386.60 7.04.0NO09-SEP-6412-FEB-9622-MAR


96DELIVER IN

PERSONTRUCK iPBw4mMm7w7kQ zNPL i261OPP1 2731 73223658958.28.09.06NO12-FEB-9628-FEB-9620-APR-96TAKE

BACK RETURN

MAIL 5wM04SNyl0AnghCP2nx lAi1 3370 3713 810210.96 .1.02NO29-MAR-9605-MAR-9631-JAN-96TAKE

BACK RETURN

REG AIRSQC2C 5PNCy4mM1 5214 46542831197.88.09.06NO21-APR-9630-MAR-9616-MAY-96NONE

AIR Om0L65CSAwSj5k6k


1 6564 6763246897.92.07.02NO30-MAY-9607-FEB-9603-FEB96DELIVER
IN
PERSONMAIL CB0SnyOL PQ32B70wB75k 6Aw10m0wh

1 7403 160524 31329.6 .1.04NO30-JUN-9614-MAR-9601 APR-96NONE
FOB C2gOQj OB6RLk1BS15 igN

2 8819 82012441659.44 0.08NO05-AUG-9609-FEB-9711-MAR97COLLECT
COD
AIR O52M70MRgRNnmm476mNm

3 9451 721230 41113.5.05.01AF05-SEP-9629-DEC-9318-FEB-94TAKE
BACK RETURN
FOB 6wQnO0Llg6y

3 9717 1834440788.44.07.03RF09-NOV-9623-DEC-9315-FEB-94TAKE
BACK RETURN
SHIP LhiA7wygz0k4g4zRhMLBAM

3 9844 1955 6 8066.64.04.01RF28-DEC-9615-DEC-9314-FEB-94TAKE
BACK RETURN
REG AIR6nmBmjQkgiCyzCQBkxPPOx5j4hB 0lRywgniP1297

Invoking SQL*Loader

Invoke SQL*Loader with a command such as:

sqlldr scott/tiger control=ulcase8.ctl data=ulcase8.dat

Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke
SQL*Loader, see the Oracle operating system-specific documentation.

Log File

The following shows a portion of the log file:

Control File: ulcase8.ctl

Data File: ulcase8.dat
File processing option string: "fix 129"
Bad File: ulcase10.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytesContinuation: none specifiedPath used: Conventional

Table LINEITEM, partition SHIP_Q1, loaded from every logicalrecord.
Insert option in effect for this partition: TRUNCATE

Column Name Position Len Term Encl
Datatype


L_ORDERKEY 1:6 6
CHARACTER
L_PARTKEY 7:11 5
CHARACTER
L_SUPPKEY 12:15 4
CHARACTER
L_LINENUMBER 16:16 1
CHARACTER
L_QUANTITYCHARACTER
17:18 2
L_EXTENDEDPRICE 19:26 8
CHARACTER
L_DISCOUNT 27:29 3
CHARACTER
L_TAX 30:32 3
CHARACTER
L_RETURNFLAG 33:33 1
CHARACTER
L_LINESTATUS 34:34 1
CHARACTER
L_SHIPDATE 35:43 9
CHARACTER
L_COMMITDATE 44:52 9
CHARACTER
L_RECEIPTDATE 53:61 9
CHARACTER
L_SHIPINSTRUCT 62:78 17
CHARACTER
L_SHIPMODE 79:85 7
CHARACTER
L_COMMENT 86:128 43
CHARACTER

Record 4: Rejected - Error on table LINEITEM, partition SHIP_Q1.
ORA-14401: inserted partition key is outside specified partition


Record 5: Rejected - Error on table LINEITEM, partition SHIP_Q1.
ORA-14401: inserted partition key is outside specified partition


Record 6: Rejected - Error on table LINEITEM, partition SHIP_Q1.
ORA-14401: inserted partition key is outside specified partition


Record 7: Rejected - Error on table LINEITEM, partition SHIP_Q1.
ORA-14401: inserted partition key is outside specified partition


Record 8: Rejected - Error on table LINEITEM, partition SHIP_Q1.
ORA-14401: inserted partition key is outside specified partition


Record 9: Rejected - Error on table LINEITEM, partition SHIP_Q1.
ORA-14401: inserted partition key is outside specified partition



Record 10: Rejected - Error on table LINEITEM, partition SHIP_Q1.
ORA-14401: inserted partition key is outside specified partition

Table LINEITEM, partition SHIP_Q1:
3 Rows successfully loaded.
7 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: 65532 bytes(381rows)
Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 0
Total logical records read: 10
Total logical records rejected: 7
Total logical records discarded: 0

Run began on Sun Nov 08 11:30:49 1998Run ended on Sun Nov 08 11:30:50 1998

Elapsed time was: 00:00:01.11
CPU time was: 00:00:00.14

Case 9: Loading LOBFILEs (CLOBs)

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.
Control File

The control file is ULCASE9.CTL. It loads new emp records with the resume for each employee
coming from a different file.

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,
1) RES_FILE FILLER CHAR,
2) "RESUME" LOBFILE (RES_FILE) TERMINATED BY EOF NULLIF RES_FILE= 'NONE'
)
BEGINDATA
7782,CLARK,MANAGER,7839,2572.50,,10,ulcase91.dat7839,KING,PRESIDENT,,5500.00,,10,ulcase92.dat7934,MILLER,CLERK,7782,920.00,,10,ulcase93.dat7566,JONES,MANAGER,7839,3123.75,,20,ulcase94.dat7499,ALLEN,SALESMAN,7698,1600.00,300.00,30,ulcase95.dat7654,MARTIN,SALESMAN,7698,1312.50,1400.00,30,ulcase96.dat7658,CHAN,ANALYST,7566,3450.00,,20,NONE

Notes:

This is a filler field. The filler field is assigned values from the datafield to which it is
mapped. See Secondary Data Files (SDFs) and LOBFILES for more information.

RESUME is loaded as a CLOB. The LOBFILE function is used to specify the name of the
field that specifies name of the file which contains the data for the LOB field. See
Loading LOB Data Using LOBFILEs for more information.

Input Data Files

>>ulcase91.dat<<
Resume for Mary Clark

Career Objective: Manage a sales team with consistent recordbreaking

Career Objective: President of large computer services company

Education:
Experience:
performance.
BA Business University of Iowa 19921992-1994 - Sales Support at MicroSales Inc.
Won "Best Sales Support" award in 1993 and 19941994-Present - Sales Manager at MicroSales Inc.
Most sales in mid-South division for 2 years
>>ulcase92.dat<<
Resume for Monica King

Education: BA English Literature Bennington, 1985Experience: 1985-1986 - Mailroom at New World Services
1986-1987 - Secretary for sales management at

New World Services
1988-1989 - Sales support at New World Services1990-1992 - Saleman at New World Services
1993-1994 - Sales Manager at New World Services1995 - 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

Education:
Experience:
Services

>>ulcase94.dat<<

companyPlainview High School, 19961996 - Present: Mail room clerk at New World

Resume for Alyson Jones

Career Objective: Work in senior sales management for a vibrant

and

Education:
Experience:
Led in

Services. My
each

>>ulcase95.dat<<

growing companyBA Philosophy Howard Univerity 19931993 - Sales Support for New World Services1994-1995 - Salesman for New World Services.

US sales in both 1994 and 1995.
1996 - present - Sales Manager New World

sales team has beat its quota by at least 15%

year.

Resume for David Allen

Career Objective: Senior Sales man for agressive Services companyEducation: BS Business Administration, Weber State 1994Experience: 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 companyEducation: 1988 - BA Mathematics, University of the NorthExperience: 1988-1992 Sales Support, New World Services

1993-present Salesman New World Services


Invoking SQL*Loader

Invoke SQL*Loader with a command such as:

sqlldr sqlldr/test control=ulcase9.ctl data=ulcase9.dat

Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke
SQL*Loader, see the Oracle operating system-specific documentation.

Log File

The following shows a portion of the log file:

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: 0Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytesContinuation: none specifiedPath 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 * WHT
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: 63984 bytes(31rows)
Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Sun Nov 08 11:31:11 1998Run ended on Sun Nov 08 11:31:19 1998

Elapsed time was: 00:00:08.14
CPU time was: 00:00:00.09

Case 10: Loading REF Fields and VARRAYs

Case 10 demonstrates

•
Loading a customer table, which has a primary key as its OID and which stores order
items in a VARRAY.
•
Loading an order table which has a REF to the customer table and the order times in a
VARRAY.
Control File

LOAD DATA
INFILE *
CONTINUEIF THIS (1) = '*'
INTO TABLE customers
replacefields terminated by ","
(

cust_no char,
name char,
addr char


)
INTO TABLE orders
replacefields terminated by ","
(


order_no char,
1) cust_no FILLER char,
2) cust REF (CONSTANT 'CUSTOMERS',
cust_no),
1) item_list_count FILLER char,
3) item_list varray count (item_list_count)

(
4) item_list column object(

5)
item char,
cnt char,
price char

)

)
)
6) BEGINDATA*00001,Spacely Sprockets,15 Space Way,
*00101,00001,2,
*Sprocket clips, 10000, .01,
Sprocket cleaner, 10, 14.00*00002,Cogswell Cogs,12 Cogswell Lane,
*00100,00002,4,
*one quarter inch cogs,1000,.02,
*one half inch cog, 150, .04,
*one inch cog, 75, .10,
*Custom coffee mugs, 10, 2.50

Notes:

This is a filler field. The filler field is assigned values from the datafield to which it is
mapped. See Secondary Data Files (SDFs) and LOBFILES for more information.

This field is created as a REF field. See

item_listis stored in a VARRAY.

The second occurence of item_list identifies the datatype of each element of the VARRAY.
Here, the datatype is a column object.

This list shows all attributes of the column object that are loaded for the VARRAY. The list
is enclosed in parenthesis. See section Loading Column Objects for more information
about loading column objects.

The data is contained in the control file and is preceded by the keyword BEGINDATA.

Invoking SQL*Loader

Invoke SQL*Loader with a command such as:

sqlldr sqlldr/test control=ulcase10.ctl

Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke


SQL*Loader, see the Oracle operating system-specific documentation.

Log File

The following shows a portion of the log file:

Control File: ulcase10.ctl

Data File: ulcase10.ctl
Bad File: ulcase10.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytesContinuation: 1:1 = 0X2a(character '*'), in current physicalrecord
Path used: Conventional

Table CUSTOMERS, loaded from every logical record.
Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl
Datatype

CUST_NO FIRST * ,
CHARACTER
NAME NEXT * ,
CHARACTER
ADDR NEXT * ,
CHARACTER


Table ORDERS, loaded from every logical record.
Insert option in effect for this table: REPLACE


Column Name Position Len Term Encl
Datatype

ORDER_NO NEXT * ,
CHARACTER
CUST_NO NEXT * ,
CHARACTER


(FILLER FIELD)
CUST DERIVED REF

Arguments are:
CONSTANT 'CUSTOMERS'
CUST_NO


ITEM_LIST_COUNT NEXT * ,


CHARACTER
(FILLER FIELD)
ITEM_LIST DERIVED * VARRAY
Count for VARRAY
ITEM_LIST_COUNT

*** Fields in ITEM_LIST
ITEM_LIST DERIVED * COLUMN
OBJECT

*** Fields in ITEM_LIST.ITEM_LIST
ITEM FIRST * ,
CHARACTER
CNT NEXT * ,
CHARACTER
PRICE NEXT * ,
CHARACTER
*** End of fields in ITEM_LIST.ITEM_LIST

*** End of fields in ITEM_LIST

Table CUSTOMERS:
2 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.

Table ORDERS:
2 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: 65240 bytes(28rows)
Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 0
Total logical records read: 2
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Sun Nov 08 11:46:13 1998Run ended on Sun Nov 08 11:46:14 1998

Elapsed time was: 00:00:00.65
CPU time was: 00:00:00.16