Default: The default depends on whether any data file(s) are specified (using the DATA parameter). See Default Values Used by SQL*Loader Express Mode.

Purpose

The BAD parameter specifies the location and name of the bad file.

Syntax and Description

BAD=[directory/][filename]

The bad file stores records that cause errors during insert or that are improperly formatted. If you specify the BAD parameter, you must supply either a directory or file name, or both. If you do not specify the BAD parameter, and there are rejected records, then the default file name is used.

The directory variable specifies a directory to which the bad file is written. The specification can include the name of a device or a network node.

The filename variable specifies a file name recognized as valid on your platform. You must specify only a name (and extension, if one other than .bad is desired). Any spaces or punctuation marks in the file name must be enclosed in single quotation marks.

The values of directory and filename are determined as follows:

• If the BAD parameter is specified with a file name but no directory, then the directory defaults to the current directory.

• If the BAD parameter is specified with a directory but no file name, then the specified directory is used and the default is used for the file name and the extension.

The BAD parameter applies to all the files which match the specified DATA parameter (if it is specified). It applies to the one data file (table-name.dat) if the data parameter is not specified.

Restrictions

Example

The following specification creates a bad file named emp1.bad in the current directory:

> sqlldr hr TABLE=employees BAD=emp1

Default: NLS client character set as specified in the NLS_LANG environment variable

Purpose

The CHARACTERSET parameter specifies a character set, other than the default, to use for the load.

Syntax and Description

CHARACTERSET=character_set_name

The CHARACTERSET parameter specifies the character set of the SQL*Loader input data files. If the CHARACTERSET parameter is not specified, then the default character set for all data files is the session character set, which is defined by the NLS_LANG environment variable. Only character data (fields of the SQL*Loader data types CHAR, VARCHAR, VARCHARC, numeric EXTERNAL, and the datetime and interval data types) is affected by the character set of the data file.

The character_set_name variable specifies the character set name. Normally, the specified name must be the name of a character set that is supported by Oracle Database.

For UTF-16 Unicode encoding, use the name UTF16 rather than AL16UTF16. AL16UTF16, which is the supported character set name for UTF-16 encoded data, is only for UTF-16 data that is in big-endian byte order. However, because you are allowed to set up data using the byte order of the system where you create the data file, the data in the data file can be either big-endian or little-endian. Therefore, a different character set name (UTF16) is used. The character set name AL16UTF16 is also supported. But if you specify AL16UTF16 for a data file that has little-endian byte order, then SQL*Loader issues a warning message and processes the data file as little-endian.

The CHARACTERSET parameter value is assumed to the be same for all data files.

Restrictions

• None

Example

The following example specifies the UTF-8 character set:

> sqlldr hr TABLE=employees CHARACTERSETNAME=utf8

Default: If the CSV parameter is not specified on the command line, then SQL*Loader express assumes that the CSV file being loaded contains data that has no embedded characters and no enclosures.

If CSV=WITHOUT_EMBEDDED is specified on the command line, then SQL*Loader express assumes that the CSV file being loaded contains data that has no embedded characters and that is optionally enclosed by '"'.

Purpose

The CSV parameter provides options that let you specify whether the comma-separated value (CSV) format file being loaded contains fields in which record terminators are embedded.

Syntax and Description

CSV=[WITH_EMBEDDED | WITHOUT_EMBEDDED]

The valid options for this parameter are as follows:

• WITH_EMBEDDED — This option means that there may be record terminators included (embedded) in a field in the record. The record terminator is newline. The default delimiters are TERMINTATED BY "," and OPTIONALLY_ENCLOSED_BY '"'. Embedded record terminators must be enclosed.

  If the CSV file contains many embedded record terminators, it is possible that performance may be adversely affected.

• WITHOUT_EMBEDDED — This option means that there are no record terminators included (embedded) in a field in the record. The record terminator is newline. The default delimiters are TERMINTATED BY "," and OPTIONALLY_ENCLOSED_BY ' " '.

Restrictions

• Normally a file can be processed in parallel (split up and processed by more than one execution server at a time). But in the case of CSV format files with embedded record terminators, the file must be processed by only one execution server. Therefore, parallel processing within a data file is disabled when CSV=WITH_EMBEDDED.

Example

The following example processes the data files as CSV format files with embedded record terminators.

> sqlldr hr TABLE=employees CSV=WITH_EMBEDDED

Default: The same name as the table name, but with an extension of .dat.

Purpose

The DATA parameter specifies the name(s) of the data file(s) containing the data to be loaded.

Syntax and Description

DATA=data-file-name

If you do not specify a file extension, then the default is .dat.

The file specification can contain wildcards (only in the file name and file extension, not in a device or directory name). An asterisk (*) represents multiple characters and a question mark (?) represents a single character. For example:

DATA='emp*.dat'

DATA='m?emp.dat'

To list multiple data file specifications (each of which can contain wild cards), the file names must be separated by commas.

If the file name contains any special characters (for example, spaces, *, ?, ), then the entire name must be enclosed within single quotation marks.

The following are three examples of possible valid uses of the DATA parameter (the single quotation marks would only be necessary if the file name contained special characters):

DATA='file1','file2','file3','file4','file5','file6' 

DATA='file1','file2'
DATA='file3,'file4','file5'
DATA='file6' 

DATA='file1'
DATA='file2'
DATA='file3'
DATA='file4'
DATA='file5'
DATA='file6' 

Restrictions

Example

Assume that the current directory contains data files with the names emp1.dat, emp2.dat, m1emp.dat, and m2emp.dat and you issue the following command:

> sqlldr hr TABLE=employees DATA='emp*','m1emp'

The command loads the emp1.dat, emp2.dat, and m1emp.dat files. The m2emp.dat file is not loaded because it did not match any of the wildcard criteria.

Default

If the DATE_FORMAT parameter is not specified, then the NLS_DATE_FORMAT, NLS_LANGUAGE, or NLS_DATE_LANGUAGE environment variable settings (if defined for the SQL*Loader session) are used. If the NLS_DATE_FORMAT is not defined, then dates are assumed to be in the default format defined by the NLS_TERRITORY setting.

Purpose

The DATE_FORMAT parameter specifies a date format that overrides the default value for all date fields.

Syntax and Description

DATE_FORMAT=mask

The mask is a date format mask, normally enclosed in double quotation marks.

Restrictions

• None

Example

If the date in the data file was 17-March-2012, then the date format would be specified as follows:

> sqlldr hr TABLE=employees DATE_FORMAT="DD-Month-YYYY"

Default

AUTO

Purpose

The DEGREE_OF_PARALLELISM parameter specifies the degree of parallelism to use for the load.

Syntax and Description

DEGREE_OF_PARALLELISM=[degree-num|DEFAULT|AUTO|NONE]

If a degree-num is specified, then it must be a whole number value from 1 to n.

If DEFAULT is specified, then the default parallelism of the database (not the default parameter value of AUTO) is used.

If AUTO is used, then the Oracle database automatically sets the degree of parallelism for the load. This is also the default if the DEGREE_OF_PARALLELISM parameter is not specified at all.

If NONE is specified, then the load is not performed in parallel. A value of NONE is the same as a value of 1.

Restrictions

• The DEGREE_OF_PARALLELISM parameter is ignored if you force the load method to be conventional or direct path (the NONE option is used). Any time you specify the DEGREE_OF_PARALLELISM parameter, for any value, you receive a message reminding you of this.

• If the load is a default external tables load and an error occurs that causes SQL*Loader express mode to use direct path load instead, then the job is not performed in parallel, even if you had specified a degree of parallelism or had accepted the external tables default of AUTO. A message is displayed alerting you to this change.

Example

The following example loads the data without using parallelism:

> sqlldr hr TABLE=employees DEGREE_OF_PARALLELISM=NONE

Default: There is no default

Purpose

The DIRECT parameter specifies the load method to use, either conventional path or direct path.

Syntax and Description

DIRECT=[TRUE|FALSE]

A value of TRUE specifies a direct path load. A value of FALSE specifies a conventional path load.

This parameter overrides the default load method of external tables, used by SQL*Loader express mode.

For some errors, SQL*Loader express mode automatically switches from the default external tables load method to direct path load. An example of when this might occur would be if a privilege violation caused the CREATE DIRECTORY SQL command to fail.

If you use the DIRECT parameter to specify a conventional or direct path load, then the following regular SQL*Loader parameters are valid to use in express mode:

• BINDSIZE

• COLUMNARRAYROWS (direct path loads only)

• DATE_CACHE

• ERRORS

• MULTITHREADING (direct path loads only)

• NO_INDEX_ERRORS (direct path loads only)

• RESUMABLE

• RESUMABLE_NAME

• RESUMABLE_TIMEOUT

• ROWS

• SKIP

• STREAMSIZE

Restrictions

• None

Example

In the following example, SQL*Loader uses the direct path load method for the load instead of external tables:

> sqlldr hr TABLE=employees DIRECT=TRUE

Default

TRUE

Purpose

The DNFS_ENABLE parameter lets you enable and disable use of the Direct NFS Client on input data files during a SQL*Loader operation.

The Direct NFS Client is an API that can be implemented by file servers to allow improved performance when Oracle accesses files on those servers.

Syntax and Description

The syntax is as follows:

DNFS_ENABLE=[TRUE|FALSE]

SQL*Loader uses the Direct NFS Client interfaces by default when it reads data files over 1 GB. For smaller files, the operating system's I/O interfaces are used. To use the Direct NFS Client on all input data files, use DNFS_ENABLE=TRUE.

To disable use of the Direct NFS Client for all data files, specify DNFS_ENABLE=FALSE.

The DNFS_ENABLE parameter can be used in conjunction with the DNFS_READBUFFERS parameter, which can specify the number of read buffers used by the Direct NFS Client.

Default

4

Purpose

The DNFS_READBUFFERS parameter lets you control the number of read buffers used by the Direct NFS Client. The Direct NFS Client is an API that can be implemented by file servers to allow improved performance when Oracle accesses files on those servers.

Syntax and Description

The syntax is as follows:

DNFS_READBUFFERS = n

Using values larger than the default might compensate for inconsistent I/O from the Direct NFS Client file server, but it may result in increased memory usage.

To use this parameter without also specifying the DNFS_ENABLE parameter, the input file must be larger than 1 GB.

Default: The default is that there is no enclosure character.

Purpose

The ENCLOSED_BY parameter specifies a field enclosure string.

Syntax and Description

ENCLOSED_BY=['string'|x'hex-string']

The enclosure character must be a string or a hexadecimal string.

Restrictions

• The same string must be used to signify both the beginning and the ending of the enclosure.

Example

In the following example, the field data is enclosed by the '/' character (forward slash).

> sqlldr hr TABLE=employees ENCLOSED_BY='/'

Default: EXECUTE

Purpose

The EXTERNAL_TABLE parameter instructs SQL*Loader whether to load data using the external tables option.

Syntax and Description

EXTERNAL_TABLE=[NOT_USED | GENERATE_ONLY | EXECUTE]

There are three possible values:

• NOT_USED — It means the load is performed using either conventional or direct path mode.

• GENERATE_ONLY — places all the SQL statements needed to do the load using external tables in the SQL*Loader log file. These SQL statements can be edited and customized. The actual load can be done later without the use of SQL*Loader by executing these statements in SQL*Plus.

• EXECUTE — the default value in SQL*Loader express mode. Attempts to execute the SQL statements that are needed to do the load using external tables. However, if any of the SQL statements returns an error, then the attempt to load stops. Statements are placed in the log file as they are executed. This means that if a SQL statement returns an error, then the remaining SQL statements required for the load will not be placed in the log file.

Note that the external table option uses directory objects in the database to indicate where all data files are stored and to indicate where output files, such as bad files and discard files, are created. You must have READ access to the directory objects containing the data files, and you must have WRITE access to the directory objects where the output files are created. If there are no existing directory objects for the location of a data file or output file, then SQL*Loader will generate the SQL statement to create one. Therefore, when the EXECUTE option is specified, you must have the CREATE ANY DIRECTORY privilege. If you want the directory object to be deleted at the end of the load, then you must also have the DROP ANY DIRECTORY privilege.

Restrictions

• None

Example

sqlldr hr TABLE=employees EXTERNAL_TABLE=NOT_USED

Default: NONE

Purpose

The FIELD_NAMES parameter is used to override the fields being in the order of the columns in the database table. (By default, SQL*Loader Express uses the table column definitions found in the ALL_TAB_COLUMNS view to determine the input field order and data types.)

An example of when this parameter could be useful is when the data in the input file is not in the same order as the columns in the table. In such a case, you can include a field name record (similar to a column header row for a table) in the data file and use the FIELD_NAMES parameter to notify SQL*Loader to process the field names in the first record to determine the order of the fields.

Syntax and Description

FIELD_NAMES=[ALL | ALL_IGNORE | FIRST | FIRST_IGNORE | NONE]

The valid options for this parameter are as follows:

• ALL — The field name record is processed for every data file.

• ALL_IGNORE — Ignore the first (field names) record in all the data files and process the data records normally.

• FIRST — In the first data file, process the first (field names) record. For all other data files, there is no field names record, so the data file is processed normally.

• FIRST_IGNORE — In the first data file, ignore the first (field names) record and use table column order for the field order.

• NONE — There are no field names records in any data file, so the data files are processed normally. This is the default.

Restrictions

• If any field name has mixed case or special characters (for example, spaces), you must use either the OPTIONALLY_ENCLOSED_BY parameter, or the ENCLOSED_BY parameter to indicate that case should be preserved and special characters should be included as part of the field name.

Example

If you are loading a CSV file that contains column headers into a table, and the fields in each row in the input file are in the same order as the columns in the table, then you could use the following:

> sqlldr hr TABLE=employees CSV=WITHOUT_EMBEDDED FIELD_NAMES=FIRST_IGNORE

Default: All records are loaded.

Purpose

The LOAD parameter specifies the number of records to be loaded.

Syntax and Description

LOAD=n

To test that all parameters you have specified for the load are set correctly, use the LOAD parameter to specify a limited number of records rather than loading all records. No error occurs if fewer than the maximum number of records are found.

Restrictions

• None

Example

The following example specifies that a maximum of 10 records be loaded:

> sqlldr hr TABLE=employees LOAD=10

For external tables method loads (the default load method for express mode), only successfully loaded records are counted toward the total. So if there are 15 records in the file and records 2 and 4 are bad, then the following records are loaded into the table, for a total of 10 records - 1, 3, 5, 6, 7, 8, 9, 10, 11, and 12.

For conventional and direct path loads, both successful and unsuccessful load attempts are counted toward the total. So if there are 15 records in the file and records 2 and 4 are bad, then only the following 8 records are actually loaded into the table - 1, 3, 5, 6, 7, 8, 9, and 10.

Default

The default is that no NULLIF checking is done.

Syntax and Description

NULLIF = "string"

Or:

NULLIF != "string"

SQL*Loader checks the specified value against the value of the field in the record. If there is a match using the equal (=) or not equal (!=) specification, then the field is set to NULL for that row. Any field that has a length of 0 after blank trimming is also set to NULL.

Restrictions

• None

Example

In the following example, if there are any fields whose value is a period, then those fields are set to NULL in their respective rows.

> sqlldr hr TABLE=employees NULLIF="."

Default: The default is that there is no optional field enclosure character.

Purpose

The OPTIONALLY_ENCLOSED_BY parameter specifies an optional field enclosure string.

Syntax and Description

OPTIONALLY_ENCLOSED_BY=['string'| x'hex-string']

The enclosure character is a string or a haxadecimal string.

Restrictions

• The same string must be used to signify both the beginning and the ending of the enclosure.

Examples

The following example specifies the optional enclosure character as a double quotation mark ("):

> sqlldr hr TABLE=employees OPTIONALLY_ENCLOSED_BY='"'

The following example specifies the optional enclosure character in hexadecimal format:

> sqlldr hr TABLE=employees OPTIONALLY_ENCLOSED_BY=x'22'

Default

There is no default

Syntax and Description

PARFILE=parameter_file_name

It is recommend that a parameter file be used if any parameter values contain quotation marks.

Restrictions

• For security reasons, you should not include your USERID password in a parameter file. SQL*Loader will prompt you for the password after you specify the parameter file at the command line, for example:

  > sqlldr hr TABLE=employees PARFILE=daily_report.par
  Password: 

Example

Suppose you have the following parameter file, test.par:

table=employees
data='mydata*.dat'
enclosed_by='"'

Any fields enclosed by double quotation marks, in any data files that match mydata*.dat, are loaded into table employees when you execute the following command:

> sqlldr hr PARFILE=test.par
Password:

Default: If this parameter is not specified, then no content is suppressed.

Purpose

The SILENT parameter suppresses some of the content that is written to the screen during a SQL*Loader operation.

Syntax and Description

The syntax is as follows:

SILENT={HEADER | FEEDBACK | ERRORS | DISCARDS | PARTITIONS | ALL}

Use the appropriate values to suppress one or more of the following (if more than one option is specified, they must be separated by commas):

• 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" messages and the status messages for the load 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. This option is ignored in express mode.

• PARTITIONS — Disables writing the per-partition statistics to the log file during a direct load of a partitioned table. This option is meaningful only in a forced direct path operation.

• ALL — Implements all of the suppression options.

Example

For example, you can suppress the header and feedback messages that normally appear on the screen with the following command-line argument:

> sqlldr hr TABLE=employees SILENT=HEADER, FEEDBACK

Default

There is no default.

Syntax and Description

TABLE=[schema-name.]table-name

If the schema name or table name includes lower case characters, spaces, or other special characters, then the names must be enclosed in double quotation marks and that entire string enclosed within single quotation marks. For example:

TABLE='"hr.Employees"'

Restrictions

• The TABLE parameter is valid only in SQL*Loader express mode.

Example

The following example loads the table employees in express mode:

> sqlldr hr TABLE=employees

Default: comma

Purpose

The TERMINATED_BY parameter specifies a field terminator that overrides the default.

Syntax and Description

TERMINATED_BY=['string'| x'hex-string' | WHITESPACE]

The field terminator must be a string or a hexadecimal string. If TERMINATED_BY=WHITESPACE is specified, then data is read until the first occurrence of a whitespace character (spaces, tabs, blanks, line feeds, form feeds, or carriage returns). Then the current position is advanced until no more adjacent whitespace characters are found. This allows field values to be delimited by varying amounts of whitespace.

If you use TERMINATED_BY=WHITESPACE, then null fields cannot contain just blanks or other whitespace because the blanks and whitespace will be skipped and an error may be reported. This means that if you have null fields in the data, then you may have to use another string to indicate the null field and use the NULLIF parameter to indicate the NULLIF string. For example, you could use the string "NoData" to indicate a null field and then insert the string "NoData" in the data to indicate a null field. Specify NULLIF="NoData" to tell SQL*Loader to set fields with the string "NoData" to NULL.

Restrictions

• None

Example

In the following example, fields are terminated by the | character.

> sqlldr hr TABLE=employees TERMINATED_BY="|"

Default

The default is taken from the value of the NLS_TIMESTAMP_FORMAT environment variable. If NLS_TIMESTAMP_FORMAT is not set up, then timestamps use the default format defined in the NLS_TERRITORY environment variable, with 6 digits of fractional precision.

Syntax and Description

TIMESTAMP_FORMAT="timestamp_format"

Restrictions

• None

Example

The following is an example of specifying a timestamp format:

> sqlldr hr TABLE=employees TIMESTAMP_FORMAT="MON-DD-YYYY HH:MI:SSXFF AM"

Default: The default for conventional and direct path loads is LDRTRIM. The default for external tables loads is LRTRIM.

Purpose

The TRIM parameter specifies the type of trimming to use during the load.

Syntax and Description

TRIM=[LRTRIM | NOTRIM | LTRIM | RTRIM |LDRTRIM]

The TRIM parameter is used to specify that spaces should be trimmed from the beginning of a text field, or the end of a text field, or both. Spaces include blanks and other nonprinting characters such as tabs, line feeds, and carriage returns.

NOTRIM indicates that no characters will be trimmed from the field. This setting generally yields the fastest performance.

LRTRIM, LTRIM, and RTRIM are used to indicate that characters should be trimmed from the field. LRTRIM means that both leading and trailing spaces are trimmed. LTRIM means that leading spaces will be trimmed. RTRIM means trailing spaces are trimmed.

LDRTRIM is the same as NOTRIM except in the following case:

• If the field is a delimited field with OPTIONALLY_ENCLOSED_BY specified, and the optional enclosures are missing for a particular instance, then spaces will be trimmed from the left.

If trimming is specified for a field that is all spaces, then the field will be set to NULL.

Restrictions

• Only LDRTRIM is supported for forced conventional path and forced direct path loads. Any time you specify the TRIM parameter, for any value, you receive a message reminding you of this.

• If the load is a default external tables load and an error occurs that causes SQL*Loader express mode to use direct path load instead, then LDRTRM is used as the trimming method, even if you specified a different method or had accepted the external tables default of LRTRIM. A message is displayed alerting you to this change.

  To use NOTRIM, use a control file with the PRESERVE BLANKS clause.

Example

The following example reads the fields, trimming all spaces on the right (trailing spaces).

> sqlldr hr TABLE=employees TRIM=RTRIM

Default: none

Purpose

The USERID parameter is used to provide your Oracle username and password.

Syntax and Description

USERID = [username | / | SYS]

If you do not specify the USERID parameter, then you are prompted for it. If only a slash is used, then USERID defaults to your operating system login.

If you connect as user SYS, then you must also specify AS SYSDBA in the connect string.

Restrictions

• Because the string, AS SYSDBA, contains a blank, some operating systems may require that the entire connect string be placed in quotation marks or marked as a literal by some method. Some operating systems also require that quotation marks on the command line be preceded by an escape character, such as backslashes.

  See your Oracle operating system-specific documentation for information about special and reserved characters on your system.

Example

The following example starts the job for user hr:

> sqlldr USERID=hr TABLE=employees
  Password: