Case 4: Loading Combined Physical Records - Oracle 8i

Case 4 demonstrates:

  • Combining multiple physical records to form one logical record with CONTINUEIF.
  • Inserting negative numbers.
  • Indicating with REPLACE that the table should be emptied before the new data is inserted.
  • Specifying a discard file in the control file using DISCARDFILE.
  • Specifying a maximum number of discards using DISCARDMAX.
  • Rejecting records due to duplicate values in a unique index or due to invalid data values.

Control File
The control file is ULCASE4.CTL:

LOAD DATA
INFILE ’ulcase4.dat’

1) DISCARDFILE ’ulcase4.dsc2) DISCARDMAX 9993) 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:

  • 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 nondata value.

Data File
The datafile for this case, ULCASE4.DAT, looks as follows. Note the asterisks in the first position and, though not visible, a new line indicator is in position 20. Note that CLARK’s commission is -10, and SQL*Loader loads the value converting it to a negative number.

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
Before invoking SQL*Loader, run the script ULCASE4.SQL as SCOTT/TIGER.

Then invoke SQL*Loader at the command line: 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:

Bad File
The bad file, shown in the following display, lists records 8 and 9 for the reasons stated earlier. (The discard file is not created.)


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 8i Topics