Using SQL Loader

SQL*Loader never creates tables, it loads existing tables. Tables may already contain data, or they may be empty. You must have INSERT privileges on the table to be loaded. You must have DELETE privilege on the table to be loaded, when using the REPLACE or TRUNCATE option to empty out the table's old data before loading the new data in its place.

PARFILE specifies the name of a file that contains commonly used command-line parameters. For example, the command line could read:

C:\>SQLLDR PARFILE=example.par

 or if you don't want to use a Parameter file invoke it this way:

sqlldr userid=mike/1234@mike.main control=c:\example.ctl logfile=c:\example.log

The example.par file could have the following contents:

userid=mike/1234@mike.main
control=example.ctl
errors=10
log=example.log
load=1000

The example.ctl file could have the following contents:

LOAD DATA INFILE 'example.txt' DISCARDFILE 'example.dsc' REPLACE -- INSERT, APPEND, TRUNCATE INTO TABLE mike.example
-- PARTITION (Partition_table_name) FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (action,
partno,
vendorno,
vendorname char "LOWER(:vendorname)",)

Here is the "example.txt" file to be read by the SQL Loader:

"C","000046     ","2786","MICROSOFT - CONSUMER                                         "
"C","000058     ","2539","MICROSOFT - PRESS                                                    "
"C","000460     ","2539","MICROSOFT - PRESS                                                    "
"D","000829     ","7659","LOTUS PASSPORT GOV                                              "
"A","001381     ","9588","HEWLETT PACKARD - ASSEMBLY VECTRA       "

When SQL*Loader executes, it can create a file called a bad file or reject file in which it places records that were rejected because of formatting errors or because they caused Oracle errors. If you have specified that a bad file is to be created, the following applies:

	If one or more records are rejected, the bad file is logged.
	If no records are rejected, then the bad file is not created. When this occurs, you must reinitialize the bad file for the next run.
	If the bad file is created, it overwrites any existing file with the same name; ensure that you do not overwrite a file you wish to retain.

For more information see these Case Studies.

Send mail to mike@infomaxweb.com with questions or comments about this web site. Copyright © 2003 Infomax Last modified: 10/27/06

Send mail to mike@infomaxweb.com with questions or comments about this web site.
Copyright © 2003 Infomax
Last modified: 10/27/06