Oracle7 Server Utilities

Case 3: Loading a Delimited, Free-Format File

Case 3 demonstrates

Loading data in (enclosed and terminated) stream format. See .

Loading dates. See .

Using the SEQUENCE function to generate unique keys for loaded data. See .

Using APPEND to indicate that the table need not be empty before inserting new records. See .

Using comments in the control file set off by double dashes. See .

Overriding general specifications with declarations for individual fields. See .

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