Case 1: Loading Variable-Length Data - Oracle 8i

Case 1 demonstrates:

  • A simple control file identifying one table and three columns to be loaded.
  • Including data to be loaded from the control file itself, so there is no separate datafile.
  • Loading data in stream format, with both types of delimited fields: terminated and enclosed.

Control File
The control file is ULCASE1.CTL:

  1. LOAD DATA
  2. INFILE *
  3. INTO TABLE dept
  4. FIELDS TERMINATED BY ’,’ OPTIONALLY ENCLOSED BY ’"’
  5. (deptno, dname, loc)
  6. BEGINDATA
  7. 12,RESEARCH,"SARATOGA"
    10,"ACCOUNTING",CLEVELAND
    11,"ART",SALEM
    13,FINANCE,"BOSTON"
    21,"SALES",PHILA.
    22,"SALES",ROCHESTER
    42,"INT’L","SAN FRAN"

Notes:

  • The LOAD DATA statement is required at the beginning of the control file.
  • INFILE * specifies that the data is found in the control file and not in an external file.
  • 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.
  • 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.
  • The names of columns to load are enclosed in parentheses. Because no datatype is specified, the default is a character of length 255.
  • BEGINDATA specifies the beginning of the data.

Invoking SQL*Loader

Before invoking SQL*Loader, run the script ULCASE1.SQL as SCOTT/TIGER. Then invoke SQL*Loader at the command line: 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.

Log File
The following shows a portion of the log file:

Control File: ulcase1.ctl Data File: ulcase1.ctl 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 65536 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 * , 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: 65016 bytes(84 rows)
Space allocated for memory besides bind array:0

bytesTotal logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Sun Nov 08 11:08:19 1998

Run ended on Sun Nov 08 11:08:20 1998

Elapsed time was: 00:00:01.16 CPU time was: 00:00:00.10

Notes:

  1. Position and length for each field are determined for each record, based on delimiters in the input file.
  2. The notation O(") signifies optional enclosure by quotation marks.

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

Oracle 8i Topics