Command-Line Syntax
SQL*Loader may be invoked from a host system command-line prompt or from a batch script, using the following syntax:
sqlload [USERID=username/password[@hostname]] [,BAD=badfile] [,BINDSIZE=bindsize] [,CONTROL=controlfile] [,DATA=datafile] [,DIRECT={TRUE|FALSE}] [,DISCARD=discard_count] [,DISCARDMAX=max_discard_count] [,ERRORS=max_error_count] [,FILE=filename] [,LOAD=max_record_count] [,LOG=logfile] [,PARFILE=parfile] [,PARALLEL=[TRUE | FALSE}] [,ROWS=rows_per_commit] [,SILENT=({[[HEADER][,FEEDBACK][,ERRORS][,DISCARDS] [,PARTITIONS]] | ALL)}] [SKIP=skip_count] [,SKIP_UNUSABLE_INDEXES={TRUE | FALSE}] [,SKIP_INDEX_MAINTENANCE={TRUE | FALSE}]
Tip
Note that the actual name of the SQL*Loader executable may vary from
platform to platform, and from release to release. For example, the
executable name for SQL*Loader for Windows 95 is
sqlldr73.exe, and is therefore entered on the
command line as sqlldr73.
- BAD
Specifies the name of a file to store records that cause errors during insert or are improperly formatted. If a badfile is not specified, the name of the control file is used by default, along with the .bad extension. This file has the same format as the input datafile, so it can be loaded by the same control file after corrections are made.
- BINDSIZE
Specifies the maximum size in bytes of the bind array. The default size is operating system dependent.
- CONTROL
Specifies the name of the file that describes how to load data. If the file extension or file type is not specified, it defaults to .ctl. If a controlfile is not specified, you will be prompted for one. See Section 16.4.2, later in this chapter, for more information on the contents and syntax of the control file.
- DATA
Specifies the name of the file containing the data to be loaded. If a datafile is not specified, the data will be expected in the control file. If the file extension is not specified, it defaults to .dat.
- DIRECT
Specifies the load method to use. TRUE specifies the direct path load, and FALSE (the default) specifies the conventional path load.
- DISCARD
Specifies a file to store records that are neither inserted into a table nor rejected. This file has the same format as the input datafile, so it can be loaded by the same control file after appropriate corrections are made.
- DISCARDMAX
Specifies the number of records that may be discarded before the load is terminated. If the parameter is omitted, all discards are allowed.
- ERRORS
Specifies the number of insert errors that will be allowed before the load is terminated. The default is 50. When SQL*Loader is performing a single-table load, any data inserted prior to load termination due to errors will be committed. SQL*Loader maintains the consistency of records across all tables, so multitable loads do not terminate immediately if errors exceed the error limit. Before terminating a load, SQL*Loader completely processes the current bind array to filter out the bad rows contained in the bind array.
- FILE
Specifies the database file from which to allocate extents. It is used only for parallel loads to allow data to be loaded with minimal disk contention.
- LOAD
Specifies the maximum number of logical records to load. The default is to load all records.
- LOG
Specifies the file that will store logging information about the SQL*Loader process. If a logfile is not specified, the name of the control file is used with the extension .log.
- PARFILE
Specifies the file that contains command-line parameters. Parameters can appear either on the command line or in parfile. If a parameter appears in both, the value specified on the command line will override that specified in the parfile. The PARFILE is especially useful when the length of a command line would otherwise exceed the system command-line length limitation.
- PARALLEL
Specifies whether direct loads can operate in multiple concurrent sessions to load data into the same table.
- ROWS
For conventional path loads, specifies the number of rows in the bind array. The default is 64. For direct path loads, ROWS specifies the number of datafile rows to be read before a data save. The default is to read all rows before a save. Since direct load uses buffers that are the same size as the system’s I/O blocks, only full buffers are written to the database, so the value of ROWS is approximate.
- SILENT
Specifies types of SQL*Loader messages to be suppressed. The following keywords may be specified:
- HEADER
Suppresses the SQL*Loader header messages.
- FEEDBACK
Suppresses the “commit point reached” feedback messages.
- ERRORS
Suppresses the data error messages that occur when a record generates an Oracle error that causes it to be written to the bad file.
- DISCARDS
Suppresses the messages for each record written to the discard file.
- PARTITIONS (Oracle8)
Suppresses writing the per-partition statistics for direct load of a partitioned table.
- ALL
Implements all of the keywords.
- 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 is useful when continuing loads that have been interrupted for some reason. It cannot be used for multiple-table direct loads when a different number of records were loaded into each table.
- SKIP_UNUSABLE_INDEXES (Oracle8)
Allows SQL*Loader to load a table with indexes that are in Index Unusable (IU) state prior to the beginning of the load. Indexes that are not in IU state at load time will be maintained by SQL*Loader. Indexes that are in IU state at load time will not be maintained, but will remain in IU state at load completion. Indexes that are UNIQUE and marked IU are not allowed to skip index maintenance.
- SKIP_INDEX_MAINTENANCE (Oracle8)
Prevents index maintenance for direct path loads, and causes the index partitions that would have had index keys added to them to be marked Index Unusable because the index segment is inconsistent with respect to the data it indexes. Index segments that are not affected by the load retain the state they had prior to the load.
- USERID
Used to provide your Oracle username/password@hostname. If USERID is omitted, you are prompted for it. If only a slash is used, USERID defaults to your operating system logon. A hostname can be specified for a conventional path load into a remote database.