Case 3: Loading a Delimited, Free-Format File - Oracle 8i

Case 3 demonstrates:

  • Loading data (enclosed and terminated) in stream format.
  • Loading dates using the datatype DATE.
  • Using SEQUENCE numbers to generate unique keys for loaded data.
  • Using APPEND to indicate that the table need not be empty before inserting new records.
  • Using Comments in the control file set off by two hyphens.

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. To test this control file, add these columns to the EMP table with the command:


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 DATA2) 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) BEGINDATA8) 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


  1. Comments may appear anywhere in the command lines of the file, but they should not appear in data. They are preceded with two hyphens 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 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.
  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
Before invoking SQL*Loader, run the script ULCASE3.SQL as SCOTT/TIGER.

Then invoke SQL*Loader at the command line: 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: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 65536 bytes Continuation: none specified Path used: Conventional

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

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

Oracle 8i Topics