SQL*LOADER

1
SQLLOADER
2
What Is It?

• Oracle utility which populates Oracle tables from
  host files
• Tables must be created first
• SQLLOADER operates with control file
• Data can be put into control file or stored in
  separate data file.

3
Data

• Positioned file
• Each row starts from the most left position.
• Each field takes the number of positions
  according to its domain constraint
• Files with delimiter
• Each record is placed into one row
• Each field is delimited by a symbol

4
Control file

• Specifies the action
• Insert
• Replace
• Append
• Specifies the data file name and list of fields
• Has an extension .ctl

5
Control File for Positioned Data File

• LOAD DATA
• INFILE File_name
• REPLACE INTO TABLE Table_name
• (field_1 POSITION (SPEP) data_type_1,
• ,
• field_K POSITION (SPEP) data_type_K) )

6
Control File for Data File with Delimiter

• LOAD DATA
• INFILE File_name
• REPLACE INTO TABLE Table_name
• FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY
  
• (field_1, field_2, , field_K)

7
Control File Contains Data

• Control file is prepared in the same way
• INFILE
• BEGINDATA clause is added
• Below the clause data are placed according to the
  format declared in the control information

8
Using the SQLLOADER

• Create table
• Prepare Control file and data file or place data
  into control file
• SQLLOADER is executed from the HOST environment

9
Using the SQLLOADER in ITEC Lab

• Login onto sit.yorku.ca
• Call SQLLOADER utility
• sqlldr login_at_studb10g Control_File
• System asks for your password
• Enter your password

10
If You Get an Error

• Control file has an extension other than .ctl
• You did not create the table first
• You use different column names in the control
  file and in the table you created
• SQLLOADER creates .log file
• SQLLOADER creates .bad file if an error occurs.

11
Example Control File

• LOAD DATA
• INFILE 'site.dat'
• REPLACE INTO TABLE Site
• FIELDS TERMINATED By ',' OPTIONALLY ENCLOSED BY
  "'"
• (site_id, location)

12
Example Data File

• 1, 'Paris'
• 2, 'Boston'
• 3, 'London
• 4, Ottawa
• 5, Toronto

13
Example Data in the Control File

• LOAD DATA
• INFILE
• REPLACE INTO TABLE building3
• FIELDS TERMINATED BY ''
• ( B_NAME, HF, DID, B_SIZE)
• BEGINDATA
• greenyd320
• redyd118
• bluend216