Infolinks

Wednesday, 2 May 2012

SQL*LOADER:Loading Variable-Length Data

Loading Variable-Length Data

Case 1 demonstrates

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
   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. Since no datatype is specified, the default is a character of length 255.
  6. BEGINDATA specifies the beginning of the data.

Invoking SQL*Loader

To run this example, invoke SQL*Loader with the command:
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 bytes

Total 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. WHT signifies that field LOC is terminated by WHITESPACE. The notation O(") signifies optional enclosure by quotation marks.

No comments:

Post a Comment