Oracle7 Server Utilities

Case 6: Loading using the Direct Path Load Method

This case study loads the EMP table using the direct path load method and concurrently builds all indexes. It illustrates the following functions:

Use of the direct path load method to load and index data. See Chapter 8.

How to specify the indexes for which the data is pre-sorted. See .

Loading all-blank numeric fields as null. See .

The NULLIF clause. See .

Note: Specify the name of the table into which you want to load data; otherwise, you will see LDR-927. Specifying DIRECT=TRUE as a command-line parameter is not an option when loading into a synonym for a table.

In this example, field positions and datatypes are specified explicitly.

The Control File

The control file is ULCASE6.CTL.

	LOAD DATA

	INFILE 'ulcase6.dat'

	INSERT

	INTO TABLE emp

1)	SORTED INDEXES (empix)

2)	(empno POSITION(01:04) INTEGER EXTERNAL NULLIF empno=BLANKS,

	ename  POSITION(06:15) CHAR,

	job    POSITION(17:25) CHAR,

	mgr    POSITION(27:30) INTEGER EXTERNAL NULLIF mgr=BLANKS,

	sal    POSITION(32:39) DECIMAL EXTERNAL NULLIF sal=BLANKS,

	comm   POSITION(41:48) DECIMAL EXTERNAL NULLIF comm=BLANKS,

	deptno POSITION(50:51) INTEGER EXTERNAL NULLIF deptno=BLANKS)

Notes:

1) The SORTED INDEXES clause identifies the indexes on which the data is sorted. This clause indicates that the datafile is sorted on the columns in the EMPIX index. This clause allows SQL*Loader to optimize index creation by eliminating the sort phase for this data when using the direct path load method.

2) The NULLIF...BLANKS clause specifies that the column should be loaded as NULL if the field in the datafile consists of all blanks. For more information, refer to "Loading All-Blank Fields" .

**Invoking SQL*Loader**

Invoke SQL*Loader with a command such as:

sqlldr scott/tiger ulcase6.ctl log=ulcase6.log direct=true

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 is a portion of the log file:

Control File:   	ULCASE6.CTL

Data File:      	ULCASE6.DAT

  Bad File:     	ULCASE6.BAD

  Discard File:	 none specified

 (Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Continuation:    none specified

Path used:      Direct

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

Column EMPNO is NULL if EMPNO = BLANKS

Column MGR is NULL if MGR = BLANKS

Column SAL is NULL if SAL = BLANKS

Column COMM is NULL if COMM = BLANKS

Column DEPTNO is NULL if DEPTNO = BLANKS

The following index(es) on table EMP were processed:

Index EMPIX was loaded.

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.

Bind array size not used in direct path.

Space allocated for memory besides bind array:        164342 bytes

Total logical records skipped:          0

Total logical records read:             7

Total logical records rejected:         0

Total logical records discarded:        0