Oracle7 Server Utilities

Case 2: Loading Fixed-Format Records

Case 2 demonstrates

A separate datafile. See .

Fixed-format data. See .

Data conversions. See .

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