Oracle7 Server Utilities

Case 4: Loading Combined Physical Records

Case 4 demonstrates

Combining multiple physical records to form one logical record with CONTINUEIF. See .

Inserting negative numbers

Indicating with REPLACE that the table should be emptied before the new data is inserted. See .

Specifying a discard file in the control file using DISCARDFILE. See .

Specifying a maximum number of discards using DISCARDMAX. See .

Rejecting records due to duplicate values in a unique index or due to invalid data values. See .

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