Oracle7 Server Utilities User's Guide Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

SQL*Loader Case Studies


The case studies in this chapter illustrate some of the features of SQL*Loader. These case studies start simply and progress in complexity.

The cases are:

Case 1: 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: Loads a datafile with fixed-length, fixed-format records Case 3: 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: Combines multiple physical records into one logical record corresponding to one database row Case 5: Loads data into multiple tables in one run Case 6: Loads data using the direct path load method Case 7: Extracts data from a formatted report


Case Study Files

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

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. The table below lists the files associated with each case:

CASE .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
Table 4 - 1. Case Studies and their Related Files

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

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.


Notes

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

The 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",CLEVELAND
	11,"ART",SALEM
	13,FINANCE,"BOSTON"
	21,"SALES",PHILA.
	22,"SALES",ROCHESTER
	42,"INT'L","SAN FRAN"

Notes:

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

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

3) 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.

4) 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.

5) Specifies that the names of columns to load are enclosed in parentheses.

6) 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.

The Log File

The following shows a portion of the log file:

Control File:      ULCASE1.CTL
Data File:         ULCASE1.DAT
  Bad File:        ULCASE1.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 65336 bytes
Continuation:       none specified
Path 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       *       WHT    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:	49920 bytes(64 rows) 
Space allocated for memory besides bind array: 76000 bytes
Total logical records skipped:           0
Total logical records read:              7
Total logical records rejected:          0
Total logical records discarded:         0

Notes:

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

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


Case 2: Loading Fixed-Format Records

Case 2 demonstrates

In this case, the field positions and datatypes are specified explicitly.

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

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

2) The name of the file containing data follows the keyword INFILE.

3) The INTO TABLE statement is required to identify the table to be loaded into.

4) 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.

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

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

The 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:    0
Errors allowed:    50
Bind array:        64 rows, maximum of 65336 bytes
Continuation:       none specified
Path 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                 4352 bytes(64 rows)
Space allocated for memory besides bind array: 37051 bytes
Total logical records skipped:                   0
Total logical records read:                      7
Total logical records rejected:                  0
Total logical records discarded:                 0


Case 3: Loading a Delimited, Free-Format File

Case 3 demonstrates

The Control File

This control file loads the same table as 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 emp
4)	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:102
	7934, "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:103
	7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50,
	(same line continued)                1400.00, 3:103
	7658, "Chan", "Analyst", 7566, 03-May-1982, 3450,,  20:101

Notes:

1) 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.

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

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

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

5) The data to be loaded into column HIREDATE appears in the format DD-Month-YYYY. The length of the date field is dependent on the mask specified.

6) 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.

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

8) 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.

The Log File

The following shows a portion of the log file:

Control File:      ULCASE3.CTL
Data File:         YLCASE3.DAT
  Bad File:        ULCASE3.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 65336 bytes
Continuation:       none specified
Path 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(")   DATE DD-Month-YYYY
SAL             NEXT       *     ,      O(")   CHARACTER
COMM            NEXT       *     ,      O(")   CHARACTER
DEPTNO          NEXT       *     :      O(")   CHARACTER
PROJNO          NEXT       *     ,      O(")   CHARACTER
LOADSEQ      SEQUENCE (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:               63810 bytes(30 rows)
Space allocated for memory besides bind array: 94391 bytes
Total logical records skipped:           0
Total logical records read:              7
Total logical records rejected:          0
Total logical records discarded:         0


Case 4: Loading Combined Physical Records

Case 4 demonstrates

The 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)         INTEGER EXTERNAL,
	ename          POSITION(6: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,
	deptno         POSITION(50:51)       INTEGER EXTERNAL,
	hiredate       POSITION(52:60)       INTEGER EXTERNAL)

Notes:

1) DISCARDFILE specifies a discard file named ULCASE4.DSC.

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

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

4) 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.

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

The 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.DSC
 (Allow 999 discards)
Number to load:   ALL
Number to skip:   0
Errors allowed:   50
Bind array:       64 rows, maximum of 65336 bytes
 Continuation:    1:1 = 0X2a(character '*'),
                  in current physical record
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,        --EMPNO null
ORA-01400: mandatory (NOT NULL) column is missing or NULL during
           insert
Record 9: Rejected - Error on table EMP.        --EMPNO not unique
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:                5120 bytes(64 rows)
Space allocated for memory besides bind array: 40195 bytes
Total logical records skipped:           0
Total logical records read:              9
Total logical records rejected:          2
Total logical records discarded:         0

The 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

The 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 PROJNO
3)	WHEN projno != '   '
	(empno   POSITION(1:4)     INTEGER EXTERNAL,
3)	projno   POSITION(25:27)   INTEGER EXTERNAL)   -- 1st proj
3)	INTO TABLE proj
4)	WHEN projno != '   '
	(empno   POSITION(1:4)     INTEGER EXTERNAL,
4)	projno   POSITION(29:31    INTEGER EXTERNAL)   -- 2nd proj
2)	INTO TABLE proj
5)	WHEN projno != '   '
	(empno   POSITION(1:4)    INTEGER EXTERNAL,
5)	projno   POSITION(33:35)  INTEGER EXTERNAL)   -- 3rd proj

Notes:

1) 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.

2) 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.

3) 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.

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

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

The Data File

The following is datafile for Case 5:

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.

The 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.DSC
 (Allow all discards)
Number to load:   ALL
Number to skip:   0
Errors allowed:   50
Bind array:       64 rows, maximum of 65336 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                    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)	ORA-01722: invalid number
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:               5120 bytes (64 rows)
Space allocated for memory besides bind array:  46763 bytes
Total logical records skipped:      0
Total logical records read:        12
Total logical records rejected:     3
Total logical records discarded:    0

Notes:

1) 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.

2) 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.

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

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

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

The 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       MACDONALD       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:

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.

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

1) The SORTED INDEXES clause identifies 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.

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

Invoke SQL*Loader with a command such as:

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

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.

The 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: 0
Errors allowed: 50
Continuation:    none specified
Path 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
  SAL                        32:39      8             CHARACTER
  COMM                       41:48      8             CHARACTER
  DEPTNO                     50:51      2             CHARACTER
Column EMPNO is NULL if EMPNO = BLANKS
Column MGR is NULL if MGR = BLANKS
Column SAL is NULL if SAL = BLANKS
Column COMM is NULL if COMM = BLANKS
Column DEPTNO is NULL if DEPTNO = BLANKS
The following index(es) on table EMP were processed:
Index EMPIX was loaded.
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:        164342 bytes
Total logical records skipped:          0
Total logical records read:             7
Total logical records rejected:         0
Total logical records discarded:        0


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:

Note: This example creates a trigger that uses the last value of unspecified fields.

The Data File

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

         Today's Newly Hired Employees
Dept  Job       Manager   MgrNo Emp Name   EmpNo  Salary    (Comm)
----  --------  --------  -----  --------  -----  --------- ------
20    Salesman  Blake     7698  Shepard    8061   $1,600.00 (3%)
                                Falstaff   8066   $1,250.00 (5%)
                                Major      8064   $1,250.00 (14%)
30    Clerk     Scott     7788  Conrad     8062   $1,100.00
                Ford      7369  DeSilva    8063     $800.00
      Manager   King      7839  Provo      8065   $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_insert
  BEFORE INSERT ON emp
  FOR EACH ROW
BEGIN
  IF :new.deptno IS NOT NULL THEN
     uldemo7.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.

The 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) CHAR  
	          TERMINATED BY WHITESPACE  "UPPER(:ename)",
	   empno  POSITION(45) INTEGER EXTERNAL 
	          TERMINATED 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:

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

2) 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.

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

4) 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.

5) 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.

6) The SQL string changes the job name to uppercase letters.

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

8) 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.

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

The Log File

The following is a portion of the log file:

1) SQL*Loader-307: Warning: conflicting lengths 2 and 3 specified
   for column EMP.DEPTNO.
   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: 0
   Errors allowed: 50
   Bind array:     64 rows, maximum of 65536 bytes
   Continuation:    none specified
   Path used:      Conventional
   Table EMP, loaded when 57:57 = 0X2e(character '.')
   Insert option in effect for this table: APPEND
   TRAILING NULLCOLS option in effect
   Column Name          Position   Len  Term Encl Datatype
   ------------------- ---------- ----- ---- ---- -----------
   DEPTNO                    1:2      3           CHARACTER
   JOB                       7:14     8  WHT      CHARACTER
   MGR                      28:31     4  WHT      CHARACTER
   ENAME                    34:41     8  WHT      CHARACTER
   EMPNO                     NEXT     *  WHT      CHARACTER
   SAL                         51     *  WHT      CHARACTER
   COMM                      NEXT     *  (        CHARACTER
                                                  %
   HIREDATE               SYSDATE
   Column DEPTNO is NULL if DEPTNO = BLANKS
   Column JOB is NULL if JOB = BLANKS
   Column JOB had SQL string
   "UPPER(:job)"
    applied to it.
   Column MGR is NULL if MGR = BLANKS
   Column ENAME had SQL string
   "UPPER(:ename)"
    applied to it.
   Column SAL had SQL string
   "TO_NUMBER(:sal,'$99,999.99')"
    applied to it.
   Column COMM had SQL string
   ":comm * 100"
    applied to it.
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:            52480 bytes(64 rows)
   Space allocated for memory besides bind array:   108185 bytes
   Total logical records skipped:          0
   Total logical records read:            13
   Total logical records rejected:         0
2) Total logical records discarded:        7

Notes:

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

2) 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.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index