Case 1: Loading Variable-Length Data

• A simple control file identifying one table and three columns to be loaded. See .

• Including data to be loaded from the control file itself, so there is no separate datafile. See .

• Loading data in stream format, with both types of delimited fields -- terminated and enclosed. See .

The Control File

1)	LOAD DATA

2)	INFILE *

3)	INTO TABLE dept

4)	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

5)	(deptno, dname, loc)

6)	BEGINDATA

	12,RESEARCH,"SARATOGA"

	10,"ACCOUNTING",CLEVELAND

	11,"ART",SALEM

	13,FINANCE,"BOSTON"

	21,"SALES",PHILA.

	22,"SALES",ROCHESTER

	42,"INT'L","SAN FRAN"

Notes:

1) The LOAD DATA statement is required at the beginning of the control file.

2) INFILE * specifies that the data is found in the control file and not in an external file.

3) The INTO TABLE statement is required to identify the table to be loaded (DEPT) into. By default, SQL*Loader requires the table to be empty before it inserts any records.

4) FIELDS TERMINATED BY specifies that the data is terminated by commas, but may also be enclosed by quotation marks. Datatypes for all fields default to CHAR.

5) Specifies that the names of columns to load are enclosed in parentheses.

6) BEGINDATA specifies the beginning of the data.

Invoking SQL*Loader

sqlldr userid=scott/tiger control=ulcase1.ctl log=ulcase1.log

SQL*Loader loads the DEPT table and creates the log file.

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

Control File:      ULCASE1.CTL

Data File:         ULCASE1.DAT

  Bad File:        ULCASE1.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 DEPT, loaded from every logical record. 

Insert option in effect for this table: INSERT

	Column Name    Position     Len      Term    Encl   Datatype

 ---------------   --------     ---      ----    ----   ---------

1)	DEPTNO            FIRST       *        ,     O(")   CHARACTER

 	DNAME              NEXT       *        ,     O(")   CHARACTER

2)	LOC                NEXT       *       WHT    O(")   CHARACTER

Table DEPT:

	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:	49920 bytes(64 rows) 

Space allocated for memory besides bind array: 76000 bytes

Total logical records skipped:           0

Total logical records read:              7

Total logical records rejected:          0

Total logical records discarded:         0

Notes:

1) Position and length for each field are determined for each record, based on delimiters in the input file.

2) WHT signifies that field LOC is terminated by WHITESPACE. The notation O(") signifies optional enclosure by quotation marks.