Oracle7 Server Utilities User's Guide Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

SQL*Loader Command-Line Reference


This chapter shows you how to run SQL*Loader with command-line keywords. The following subjects are discussed:


The SQL*Loader Command Line

You can invoke SQL*Loader from the command line followed by certain keywords.

Additional Information: The command to invoke SQL*Loader is operating system-dependent. The following examples use the UNIX-based name, "sqlldr". See your Oracle operating system-specific documentation for the correct command for your system.

If you invoke SQL*Loader with no keywords, SQL*Loader displays a help screen with the available keywords and default values. The following example shows default values that are the same on all operating systems.

sqlldr
...
Valid Keywords:
	    userid -- Oracle username/password
	   control -- Control file name 
	       log -- Log file name  
	       bad -- Bad file name
	      data -- Data file name
	   discard -- Discard file name
	discardmax -- Number of discards to allow
				(Default all)
	      skip -- Number of logical records to skip
				(Default 0)
	      load -- Number of logical records to load
				(Default all)
	    errors -- Number of errors to allow
				(Default 50)
	      rows -- Number of rows in conventional path bind array
				or between direct path data saves
				(Default: Conventional Path 64, Direct path all)
	  bindsize -- Size of conventional path bind array in bytes
				(System-dependent default)
	    silent -- Suppress messages during run 
				(header, feedback, errors, discards)
	    direct -- Use direct path
				(Default FALSE)
	   parfile -- Parameter file: name of file that contains 
			     parameter specifications
	  parallel - Perform parallel load
				(Default FALSE)
	      file - File to allocate extents from

Using Command-Line Keywords

Keywords are optionally separated by commas. They are entered in any order. Keywords are followed by valid arguments.

For example, :

SQLLDR CONTROL=foo.ctl, LOG=bar.log, BAD=baz.bad, DATA=etc.dat 
	USERID=scott/tiger, ERRORS=999, LOAD=2000, DISCARD=toss.dis,
	DISCARDMAX=5

Specifying Keywords in the Control File

If the command line's length exceeds the size of the maximum command line on your system, you can put some of the command-line keywords in the control file, using the control file keyword OPTIONS. See page 5 - 12.

They can also be specified in a separate file specified by the keyword PARFILE (see page 6 - 5). These alternative methods are useful for keyword entries that seldom change. Keywords specified in this manner can still be overridden from the command line.


Command-Line Keywords

This section describes each of SQL*Loader's command-line keywords.

Keyword Identifies
BAD Bad File
BAD specifies the name the bad file created by SQL*Loader to store records that cause errors during insert or that are improperly formatted. If a filename is not specified, the name of the control file is used by default with the extension .BAD. This file has the same format as the input datafile, so it can be loaded by the same control file after appropriate updates or corrections are made.

A bad file filename specified on the command line becomes the bad file associated with the first INFILE statement in the control file. If the bad file filename was also specified in the control file, the command-line value overrides it.

BINDSIZE Maximum sizes
BINDSIZE specifies the maximum size of the bind array in bytes. The size of the bind array given by BINDSIZE overrides the default size (which is system dependent) and any size determined by ROWS. The bind array is discussed 5 - 63.

Keyword Identifies
CONTROL Control File
CONTROL specifies the control file that describes how to load data. If a file extension or file type is not specified, it defaults to CTL. If omitted, you are prompted for it.

DATA Datafile
DATA specifies the data file containing the data to be loaded. If a filename is not specified, the name of the control file is used by default. If a file extension or file type is not specified, it defaults to DAT.

DIRECT Data path
DIRECT specifies the load method to use, conventional path or direct path. TRUE specifies a direct path load. FALSE specifies a conventional path load. The defaults is FALSE. Load methods are explained[*].

DISCARD Discard file
DISCARD specifies an optional discard file which will be created by SQL*Loader to store records that are neither rejected, nor inserted, into a table. If a filename is not specified, the name of the control file is used by default with the default extension .DSC. This file has the same format as the input datafile. So it can be loaded by the same control file after appropriate updates or corrections are made.

A discard file filename specified on the command line becomes the discard file associated with the first INFILE statement in the control file. If the discard file filename was also specified in the control file, the command-line value overrides it.

DISCARDMAX Discards to allow
DISCARDMAX specifies the number of discard records that will terminate the load. The default value is all discards are allowed. To stop on the first discarded record, specify one (1).

ERRORS Errors to allow
ERRORS specifies the number of insert errors that will terminate the load. The default is 50. To stop on the first error, specify one (1). To specify that all errors be allowed, use a very high number.

Keyword Identifies
FILE File to Load Into
FILE specifies the database file to allocate extents from. It is used only for parallel loads. By varying the value of the FILE parameter for different SQL*Loader processes, data can be loaded onto a system with minimal disk contention. For more information, see "Parallel Data Loading" [*].

LOAD Records to load
LOAD specifies the maximum number of logical records to load (after skipping the specified number of records). By default all records are loaded. No error occurs if fewer than the maximum number of records are found.

LOG Log File
LOG specifies the log file which SQL*Loader will create to store logging information about the loading process. If a filename is not specified, the name of the control file is used by default with the default extension of .LOG.

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

SQLLDR PARFILE=example.par

and the parameter file could have the following contents:

userid=scott/tiger
control=example.ctl
errors=9999
log=example.log

Note: Although it is not usually important, on some systems it may be necessary to have no spaces around the equal sign ("=") in the parameter specifications.

PARALLEL Parallel Load
PARALLEL specifies whether direct loads can operate in multiple concurrent sessions to load data into the same table. For more information on PARALLEL loads, see "Parallel Data Loading" on page 8 - 21.

Keyword Identifies
ROWS Rows per commit
Conventional path loads only: ROWS specifies the number of rows in the bind array. The default is 64. (The bind array is discussed on page 5 - 63.)

Direct path, loads only: ROWS identifies the number of rows you want to read from the data file before a data save. The default is to save data once at the end of the load. For more information, see "Data Saves" [*].

Because the direct load is optimized for performance, it uses buffers that are the same size and format as the system's I/O blocks. Only full buffers are written to the database, so the value of ROWS is approximate.

SILENT Feedback mode
When SQL*Loader begins, a header message like this appears on the screen and is placed in the log file:

SQL*Loader:   Production on Wed Feb 24 15:07:23... 
Copyright (c) Oracle Corporation... 

As SQL*Loader executes, you also see feedback messages on the screen, like this:

Commit point reached - logical record count 20

SQL*Loader may also display data error messages like these:

Record 4: Rejected - Error on table EMP
ORA-00001: unique constraint <name> violated

You can suppress these messages by specifying SILENT with an argument. For example, you can suppress the header and feedback messages that normally appear on the screen with the following command-line argument:

SILENT=(HEADER, FEEDBACK)

Use the appropriate keyword to suppress:

HEADER Suppresses the SQL*Loader header messages that normally appear on the screen. Header messages still appear in the log file.
FEEDBACK Suppresses the "commit point reached" feedback messages that normally appear on the screen.
ERRORS Suppresses the data error messages in the log file that occur when a record generates an Oracle error that causes it to be written to the bad file. A count of rejected records still appears.
DISCARDS Suppresses the messages in the log file for each record written to the discard file.
ALL All of the above.
Keyword Identifies
SKIP Records to skip
SKIP specifies the number of logical records from the beginning of the file that should not be loaded. By default, no records are skipped.

This parameter continues loads that have been interrupted for some reason. It is used for all conventional loads, for single-table direct loads, and for multiple-table direct loads when the same number of records were loaded into each table. It is not used for multiple table direct loads when a different number of records were loaded into each table. See "Continuing Multiple Table Direct Loads" [*] for more information.

USERID Userid
USERID is used to provide your Oracle username/password. If omitted, you are prompted for it. If just a slash is used, USERID defaults to your operating system logon. A SQL*Net database specification string can be used for a conventional path load into a remote database. For more information on SQL*Net, see your SQL*Net documentation.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index