Comments can appear anywhere in the parameter section of the file, but they should not appear within the data.

--This is a comment

The following command-line parameters can be specified using the OPTIONS clause.

BINDSIZE = n
COLUMNARRAYROWS = n
DATE_CACHE = n
DEGREE_OF_PARALLELISM= {degree-num|DEFAULT|AUTO|NONE}
DIRECT = {TRUE | FALSE} 
EMPTY_LOBS_ARE_NULL = {TRUE | FALSE}
ERRORS = n
EXTERNAL_TABLE = {NOT_USED | GENERATE_ONLY | EXECUTE}
FILE = tablespace file 
LOAD = n 
MULTITHREADING = {TRUE | FALSE}
PARALLEL = {TRUE | FALSE}
READSIZE = n
RESUMABLE = {TRUE | FALSE}
RESUMABLE_NAME = 'text string'
RESUMABLE_TIMEOUT = n
ROWS = n 
SDF_PREFIX = string
SILENT = {HEADER | FEEDBACK | ERRORS | DISCARDS | PARTITIONS | ALL} 
SKIP = n   
SKIP_INDEX_MAINTENANCE = {TRUE | FALSE}
SKIP_UNUSABLE_INDEXES = {TRUE | FALSE}
STREAMSIZE = n
TRIM= {LRTRIM|NOTRIM|LTRIM|RTRIM|LDRTRIM}

OPTIONS (BINDSIZE=100000, SILENT=(ERRORS, FEEDBACK) )

SQL and SQL*Loader reserved words must be specified within double quotation marks.

The only SQL*Loader reserved word is CONSTANT.

You must use double quotation marks if the object name contains special characters other than those recognized by SQL ($, #, _), or if the name is case sensitive.

The SQL string applies SQL operators to data fields.

You must specify SQL strings within double quotation marks.

Your course of action may depend on the operating system that you are using.

The following sections discuss situations in which your course of action may depend on the operating system that you are using.

• Specifying a Complete Path
  Specifying the path name within single quotation marks prevents errors.
• Backslash Escape Character
  In DDL syntax, you can place a double quotation mark inside a string delimited by double quotation marks by preceding it with the backslash escape character (\), if the escape character is allowed on your operating system.
• Nonportable Strings
  There are two kinds of character strings in a SQL*Loader control file that are not portable between operating systems: filename and file processing option strings.
• Using the Backslash as an Escape Character
  Use the backslash character to separate directories in a path name.
• Escape Character Is Sometimes Disallowed
  The release of the Oracle database running on your operating system may not implement the escape character for nonportable strings.

The following list shows different ways you can specify INFILE syntax.

To load data from multiple data files in one SQL*Loader run, use an INFILE clause for each data file.

Data files need not have the same file processing options, although the layout of the records must be identical. For example, two files could be specified with completely different file processing options strings, and a third could consist of data in the control file.

You can also specify a separate discard file and bad file for each data file. In such a case, the separate bad files and discard files must be declared immediately after each data file name. For example, the following excerpt from a control file specifies four data files with separate bad and discard files:

INFILE  mydat1.dat  BADFILE  mydat1.bad  DISCARDFILE mydat1.dis 
INFILE  mydat2.dat 
INFILE  mydat3.dat  DISCARDFILE  mydat3.dis 
INFILE  mydat4.dat  DISCARDMAX  10 0

• For mydat1.dat, both a bad file and discard file are explicitly specified. Therefore both files are created, as needed.

• For mydat2.dat, neither a bad file nor a discard file is specified. Therefore, only the bad file is created, as needed. If created, the bad file has the default file name and extension mydat2.bad. The discard file is not created, even if rows are discarded.

• For mydat3.dat, the default bad file is created, if needed. A discard file with the specified name (mydat3.dis) is created, as needed.

• For mydat4.dat, the default bad file is created, if needed. Because the DISCARDMAX option is used, SQL*Loader assumes that a discard file is required and creates it with the default name mydat4.dsc.

Using the bad file in an example.

To specify a bad file with file name sample and default file extension or file type of .bad, enter the following in the control file:

BADFILE sample 

To specify only a directory name, enter the following in the control file:

BADFILE '/mydisk/bad_dir/'

To specify a bad file with file name bad0001 and file extension or file type of .rej, enter either of the following lines in the control file:

BADFILE bad0001.rej
BADFILE '/REJECT_DIR/bad0001.rej' 

Data from LOBFILEs and SDFs is not written to a bad file when there are rejected rows.

If there is an error loading a LOB, then the row is not rejected. Rather, the LOB column is left empty (not null with a length of zero (0) bytes). However, when the LOBFILE is being used to load an XML column and there is an error loading this LOB data, then the XML column is left as null.

This section explains the criteria for rejecting records.

A record can be rejected for the following reasons:

1. Upon insertion, the record causes an Oracle error (such as invalid data for a given data type).

2. The record is formatted incorrectly so that SQL*Loader cannot find field boundaries.

3. The record violates a constraint or tries to make a unique index non-unique.

If the data can be evaluated according to the WHEN clause criteria (even with unbalanced delimiters), then it is either inserted or rejected.

Neither a conventional path nor a direct path load will write a row to any table if it is rejected because of reason number 2 in the previous list.

A conventional path load will not write a row to any tables if reason number 1 or 3 in the previous list is violated for any one table. The row is rejected for that table and written to the reject file.

In a conventional path load, if the data file has a record that is being loaded into multiple tables and that record is rejected from at least one of the tables, then that record is not loaded into any of the tables.

The log file indicates the Oracle error for each rejected record. Case study 4 demonstrates rejected records. (See SQL*Loader Case Studies for information on how to access case studies.)

To specify the name of the file, use the DISCARDFILE clause, followed by a directory path and/or file name.

Description of the illustration discard.eps

The DISCARDFILE clause specifies that a discard directory path and/or file name follows. Neither the directory_path nor the filename is required. However, you must specify at least one.

The directory parameter specifies a directory to which the discard file will be written.

The filename parameter specifies a valid file name specification for your platform. Any spaces or punctuation marks in the file name must be enclosed in single quotation marks.

The default file name is the name of the data file, and the default file extension or file type is .dsc. A discard file name specified on the command line overrides one specified in the control file. If a discard file with that name already exists, then it is either overwritten or a new version is created, depending on your operating system.

• Limiting the Number of Discard Records
  You can limit the number of records to be discarded for each data file by specifying an integer for either the DISCARDS or DISCARDMAX keyword.

The list shows different ways that you can specify a name for the discard file from within the control file.

• To specify a discard file with file name circular and default file extension or file type of .dsc:

  DISCARDFILE  circular 
  

• To specify a discard file named notappl with the file extension or file type of .may:

  DISCARDFILE notappl.may 
  

• To specify a full path to the discard file forget.me:

  DISCARDFILE  '/discard_dir/forget.me'

If there is no INTO TABLE clause specified for a record, then the record is discarded.

This situation occurs when every INTO TABLE clause in the SQL*Loader control file has a WHEN clause and, either the record fails to match any of them, or all fields are null.

No records are discarded if an INTO TABLE clause is specified without a WHEN clause. An attempt is made to insert every record into such a table. Therefore, records may be rejected, but none are discarded.

Case study 7, Extracting Data from a Formatted Report, provides an example of using a discard file. (See SQL*Loader Case Studies for information on how to access case studies.)

Data from LOBFILEs and SDFs is not written to a discard file when there are discarded rows.

This section explains how to specify a discard file from the command line.

See DISCARD for information about how to specify a discard file from the command line.

A file name specified on the command line overrides any discard file that you may have specified in the control file.

Multibyte character sets support Asian languages.

Data can be loaded in multibyte format, and database object names (fields, tables, and so on) can be specified with multibyte characters. In the control file, comments and object names can also use multibyte characters.

SQL*Loader supports loading data that is in a Unicode character set.

Unicode is a universal encoded character set that supports storage of information from most languages in a single character set. Unicode provides a unique code value for every character, regardless of the platform, program, or language. There are two different encodings for Unicode, UTF-16 and UTF-8.

The UTF-16 Unicode encoding is a fixed-width multibyte encoding in which the character codes 0x0000 through 0x007F have the same meaning as the single-byte ASCII codes 0x00 through 0x7F.

The UTF-8 Unicode encoding is a variable-width multibyte encoding in which the character codes 0x00 through 0x7F have the same meaning as ASCII. A character in UTF-8 can be 1 byte, 2 bytes, or 3 bytes long.

The Oracle database uses the database character set for data stored in SQL CHAR data types (CHAR, VARCHAR2, CLOB, and LONG), for identifiers such as table names, and for SQL statements and PL/SQL source code.

Only single-byte character sets and varying-width character sets that include either ASCII or EBCDIC characters are supported as database character sets. Multibyte fixed-width character sets (for example, AL16UTF16) are not supported as the database character set.

An alternative character set can be used in the database for data stored in SQL NCHAR data types (NCHAR, NVARCHAR2, and NCLOB). This alternative character set is called the database national character set. Only Unicode character sets are supported as the database national character set.

By default, the data file is in the character set defined by the NLS_LANG parameter.

The data file character sets supported with NLS_LANG are the same as those supported as database character sets. SQL*Loader supports all Oracle-supported character sets in the data file (even those not supported as database character sets).

For example, SQL*Loader supports multibyte fixed-width character sets (such as AL16UTF16 and JA16EUCFIXED) in the data file. SQL*Loader also supports UTF-16 encoding with little-endian byte ordering. However, the Oracle database supports only UTF-16 encoding with big-endian byte ordering (AL16UTF16) and only as a database national character set, not as a database character set.

The character set of the data file can be set up by using the NLS_LANG parameter or by specifying a SQL*Loader CHARACTERSET parameter.

The default character set for all data files, if the CHARACTERSET parameter is not specified, is the session character set defined by the NLS_LANG parameter.

The character set used in input data files can be specified with the CHARACTERSET parameter.

SQL*Loader can automatically convert data from the data file character set to the database character set or the database national character set, when they differ.

When data character set conversion is required, the target character set should be a superset of the source data file character set. Otherwise, characters that have no equivalent in the target character set are converted to replacement characters, often a default character such as a question mark (?). This causes loss of data.

The sizes of the database character types CHAR and VARCHAR2 can be specified in bytes (byte-length semantics) or in characters (character-length semantics). If they are specified in bytes, and data character set conversion is required, then the converted values may take more bytes than the source values if the target character set uses more bytes than the source character set for any character that is converted. This will result in the following error message being reported if the larger target value exceeds the size of the database column:

ORA-01401: inserted value too large for column

You can avoid this problem by specifying the database column size in characters and also by using character sizes in the control file to describe the data. Another way to avoid this problem is to ensure that the maximum column size is large enough, in bytes, to hold the converted value.

• Considerations When Loading Data into VARRAYs or Primary-Key-Based REFs
  This section describes the considerations that you should take when loading data into VARRAYs or into a primary-key-based REFs.
• CHARACTERSET Parameter
  Specifying the CHARACTERSET parameter tells SQL*Loader the character set of the input data file.
• Control File Character Set
  The SQL*Loader control file itself is assumed to be in the character set specified for your session by the NLS_LANG parameter.
• Character-Length Semantics
  Byte-length semantics are the default for all data files except those that use the UTF16 character set (which uses character-length semantics by default).

In general, loading shift-sensitive character data can be much slower than loading simple ASCII or EBCDIC data.

The fastest way to load shift-sensitive character data is to use fixed-position fields without delimiters. To improve performance, remember the following points:

• The field data must have an equal number of shift-out/shift-in bytes.

• The field must start and end in single-byte mode.

• It is acceptable for the first byte to be shift-out and the last byte to be shift-in.

• The first and last characters cannot be multibyte.

• If blanks are not preserved and multibyte-blank-checking is required, then a slower path is used. This can happen when the shift-in byte is the last byte of a field after single-byte blank stripping is performed.

In a conventional path load, data is committed after all data in the bind array is loaded into all tables.

If the load is discontinued, then only the rows that were processed up to the time of the last commit operation are loaded. There is no partial commit of data.

In a direct path load, the behavior of a discontinued load varies depending on the reason the load was discontinued.

These sections describe the reasons why a load was discontinued:

• Load Discontinued Because of Space Errors
  If a load is discontinued because of space errors, then the behavior of SQL*Loader depends on whether you are loading data into multiple subpartitions.
• Load Discontinued Because Maximum Number of Errors Exceeded
  If the maximum number of errors is exceeded, then SQL*Loader stops loading records into any table and the work done to that point is committed.
• Load Discontinued Because of Fatal Errors
  If a fatal error is encountered, then the load is stopped and no data is saved unless ROWS was specified at the beginning of the load.
• Load Discontinued Because a Ctrl+C Was Issued
  If SQL*Loader is in the middle of saving data when a Ctrl+C is issued, then it continues to do the save and then stops the load after the save completes.

When a load is discontinued, any data already loaded remains in the tables, and the tables are left in a valid state.

If the conventional path is used, then all indexes are left in a valid state.

If the direct path load method is used, then any indexes on the table are left in an unusable state. You can either rebuild or re-create the indexes before continuing, or after the load is restarted and completes.

Other indexes are valid if no other errors occurred. See Indexes Left in an Unusable State for other reasons why an index might be left in an unusable state.

The SQL*Loader log file tells you the state of the tables and indexes and the number of logical records already read from the input data file.

Use this information to resume the load where it left off.

When SQL*Loader must discontinue a direct path or conventional path load before it is finished, some rows have probably already been committed or marked with savepoints.

To continue the discontinued load, use the SKIP parameter to specify the number of logical records that have already been processed by the previous load. At the time the load is discontinued, the value for SKIP is written to the log file in a message similar to the following:

Specify SKIP=1001 when continuing the load.

This message specifying the value of the SKIP parameter is preceded by a message indicating why the load was discontinued.

Note that for multiple-table loads, the value of the SKIP parameter is displayed only if it is the same for all tables.

Use CONCATENATE when you want SQL*Loader to always combine the same number of physical records to form one logical record.

In the following example, integer specifies the number of physical records to combine.

CONCATENATE  integer 

The integer value specified for CONCATENATE determines the number of physical record structures that SQL*Loader allocates for each row in the column array. In direct path loads, the default value for COLUMNARRAYROWS is large, so if you also specify a large value for CONCATENATE, then excessive memory allocation can occur. If this happens, you can improve performance by reducing the value of the COLUMNARRAYROWS parameter to lower the number of rows in a column array.

Use CONTINUEIF if the number of physical records to be combined varies.

The CONTINUEIF clause is followed by a condition that is evaluated for each physical record, as it is read. For example, two records might be combined if a pound sign (#) were in byte position 80 of the first record. If any other character were there, then the second record would not be added to the first.

The full syntax for CONTINUEIF adds even more flexibility:

Description of the illustration continueif.eps

Table 9-2 describes the parameters for the CONTINUEIF clause.

        %%aaaaaaaa....
        %%bbbbbbbb....
        ..cccccccc....
        %%dddddddddd..
        %%eeeeeeeeee..
        ..ffffffffff..

CONTINUEIF THIS (1:2) = '%%'

        aaaaaaaa....bbbbbbbb....cccccccc....
        dddddddddd..eeeeeeeeee..ffffffffff..

CONTINUEIF THIS PRESERVE (1:2) = '%%'

        %%aaaaaaaa....%%bbbbbbbb......cccccccc....
        %%dddddddddd..%%eeeeeeeeee....ffffffffff..

        ..aaaaaaaa....
        %%bbbbbbbb....
        %%cccccccc....
        ..dddddddddd..
        %%eeeeeeeeee..
        %%ffffffffff..

CONTINUEIF NEXT (1:2) = '%%'

        aaaaaaaa....bbbbbbbb....cccccccc....
        dddddddddd..eeeeeeeeee..ffffffffff..

CONTINUEIF NEXT PRESERVE (1:2) = '%%'

        ..aaaaaaaa....%%bbbbbbbb....%%cccccccc....
        ..dddddddddd..%%eeeeeeeeee..%%ffffffffff..

The INTO TABLE clause of the LOAD DATA statement enables you to identify tables, fields, and data types.

It defines the relationship between records in the data file and tables in the database. The specification of fields and data types is described in later sections.

• INTO TABLE Clause
  Among its many functions, the INTO TABLE clause enables you to specify the table into which you load data.

When you are loading a table, you can use the INTO TABLE clause to specify a table-specific loading method (INSERT, APPEND, REPLACE, or TRUNCATE) that applies only to that table.

That method overrides the global table-loading method. The global table-loading method is INSERT, by default, unless a different method was specified before any INTO TABLE clauses. The following sections discuss using these options to load data into empty and nonempty tables.

• Loading Data into Empty Tables
  This section describes how to load data into empty tables.
• Loading Data into Nonempty Tables
  This section describes loading data into nonempty tables.

The OPTIONS parameter can be specified for individual tables in a parallel load. (It is valid only for a parallel load.)

The syntax for the OPTIONS parameter is as follows:

Description of the illustration into_table3.eps

You can choose to load or discard a logical record by using the WHEN clause to test a condition in the record.

The WHEN clause appears after the table name and is followed by one or more field conditions. The syntax for field_condition is as follows:

Description of the illustration fld_cond.eps

For example, the following clause indicates that any record with the value "q" in the fifth column position should be loaded:

WHEN (5) = 'q' 

A WHEN clause can contain several comparisons, provided each is preceded by AND. Parentheses are optional, but should be used for clarity with multiple comparisons joined by AND. For example:

WHEN (deptno = '10') AND (job = 'SALES') 

• Using the WHEN Clause with LOBFILEs and SDFs
  This section describes using the WHEN clause with LOBFILEs and SDFs.

If all data fields are terminated similarly in the data file, then you can use the FIELDS clause to indicate the default termination and enclosure delimiters.

• fields_spec
  
• termination_spec
  
• enclosure_spec
  

When the control file definition specifies more fields for a record than are present in the record, SQL*Loader must determine whether the remaining (specified) columns should be considered null or whether an error should be generated.

If the control file definition explicitly states that a field's starting position is beyond the end of the logical record, then SQL*Loader always defines the field as null. If a field is defined with a relative position (such as dname and loc in the following example), and the record ends before the field is found, then SQL*Loader could either treat the field as null or generate an error. SQL*Loader uses the presence or absence of the TRAILING NULLCOLS clause (shown in the following syntax diagram) to determine the course of action.

Description of the illustration into_table6.eps

Description of the illustration into_table7.eps

• TRAILING NULLCOLS Clause
  

The SORTED INDEXES clause applies to direct path loads. It tells SQL*Loader that the incoming data has already been sorted on the specified indexes, allowing SQL*Loader to optimize performance.

The SINGLEROW option is intended for use during a direct path load with APPEND on systems with limited memory, or when loading a small number of records into a large table. This option inserts each index entry directly into the index, one record at a time.

By default, SQL*Loader does not use SINGLEROW to append records to a table. Instead, index entries are put into a separate, temporary storage area and merged with the original index at the end of the load. This method achieves better performance and produces an optimal index, but it requires extra storage space. During the merge operation, the original index, the new index, and the space for new entries all simultaneously occupy storage space.

With the SINGLEROW option, storage space is not required for new index entries or for a new index. The resulting index may not be as optimal as a freshly sorted one, but it takes less space to produce. It also takes more time because additional UNDO information is generated for each index insert. This option is suggested for use when either of the following situations exists:

• Available storage is limited.

• The number of records to be loaded is small compared to the size of the table (a ratio of 1:20 or less is recommended).

When the data records are short, more than one can be stored in a single, physical record to use the storage space efficiently.

Some data storage and transfer media have fixed-length physical records.

In this example, SQL*Loader treats a single physical record in the input file as two logical records and uses two INTO TABLE clauses to load the data into the emp table. For example, assume the data is as follows:

1119 Smith      1120 Yvonne 
1121 Albert     1130 Thomas 

The following control file extracts the logical records:

INTO TABLE emp 
     (empno POSITION(1:4)  INTEGER EXTERNAL, 
      ename POSITION(6:15) CHAR) 
INTO TABLE emp 
     (empno POSITION(17:20) INTEGER EXTERNAL, 
      ename POSITION(21:30) CHAR) 

• Relative Positioning Based on Delimiters
  The same record could be loaded with a different specification.

A single data file might contain records in a variety of formats.

Consider the following data, in which emp and dept records are intermixed:

1 50   Manufacturing       — DEPT record 
2 1119 Smith      50       — EMP record 
2 1120 Snyder     50 
1 60   Shipping 
2 1121 Stevens    60 

A record ID field distinguishes between the two formats. Department records have a 1 in the first column, while employee records have a 2. The following control file uses exact positioning to load this data:

INTO TABLE dept 
   WHEN recid = 1 
   (recid  FILLER POSITION(1:1)  INTEGER EXTERNAL,
    deptno POSITION(3:4)  INTEGER EXTERNAL, 
    dname  POSITION(8:21) CHAR) 
INTO TABLE emp 
   WHEN recid <> 1 
   (recid  FILLER POSITION(1:1)   INTEGER EXTERNAL,
    empno  POSITION(3:6)   INTEGER EXTERNAL, 
    ename  POSITION(8:17)  CHAR, 
    deptno POSITION(19:20) INTEGER EXTERNAL) 

• Relative Positioning Based on the POSITION Parameter
  Records can be loaded as delimited data.

A single data file may contain records made up of row objects inherited from the same base row object type.

For example, consider the following simple object type and object table definitions, in which a nonfinal base object type is defined along with two object subtypes that inherit their row objects from the base type:

CREATE TYPE person_t AS OBJECT 
 (name    VARCHAR2(30), 
  age     NUMBER(3)) not final; 

CREATE TYPE employee_t UNDER person_t 
 (empid   NUMBER(5), 
  deptno  NUMBER(4), 
  dept    VARCHAR2(30)) not final; 

CREATE TYPE student_t UNDER person_t 
 (stdid   NUMBER(5), 
  major   VARCHAR2(20)) not final; 

CREATE TABLE persons OF person_t;

The following input data file contains a mixture of these row objects subtypes. A type ID field distinguishes between the three subtypes. person_t objects have a P in the first column, employee_t objects have an E, and student_t objects have an S.

P,James,31, 
P,Thomas,22, 
E,Pat,38,93645,1122,Engineering, 
P,Bill,19, 
P,Scott,55, 
S,Judy,45,27316,English, 
S,Karen,34,80356,History, 
E,Karen,61,90056,1323,Manufacturing, 
S,Pat,29,98625,Spanish, 
S,Cody,22,99743,Math, 
P,Ted,43, 
E,Judy,44,87616,1544,Accounting, 
E,Bob,50,63421,1314,Shipping, 
S,Bob,32,67420,Psychology, 
E,Cody,33,25143,1002,Human Resources,

The following control file uses relative positioning based on the POSITION parameter to load this data. Note the use of the TREAT AS clause with a specific object type name. This informs SQL*Loader that all input row objects for the object table will conform to the definition of the named object type.

INTO TABLE persons 
REPLACE 
WHEN typid = 'P' TREAT AS person_t 
FIELDS TERMINATED BY "," 
 (typid   FILLER  POSITION(1) CHAR, 
  name            CHAR, 
  age             CHAR) 

INTO TABLE persons 
REPLACE 
WHEN typid = 'E' TREAT AS employee_t 
FIELDS TERMINATED BY "," 
 (typid   FILLER  POSITION(1) CHAR, 
  name            CHAR, 
  age             CHAR, 
  empid           CHAR, 
  deptno          CHAR, 
  dept            CHAR) 

INTO TABLE persons 
REPLACE 
WHEN typid = 'S' TREAT AS student_t 
FIELDS TERMINATED BY "," 
 (typid   FILLER  POSITION(1) CHAR, 
  name            CHAR, 
  age             CHAR, 
  stdid           CHAR, 
  major           CHAR)

By using the POSITION parameter with multiple INTO TABLE clauses, data from a single record can be loaded into multiple normalized tables.

See case study 5, Loading Data into Multiple Tables, for an example. (See SQL*Loader Case Studies for information about how to access case studies.).

Multiple INTO TABLE clauses allow you to extract multiple logical records from a single input record and recognize different record formats in the same file.

For delimited data, proper use of the POSITION parameter is essential for achieving the expected results.

When the POSITION parameter is not used, multiple INTO TABLE clauses process different parts of the same (delimited data) input record, allowing multiple tables to be loaded from one record. When the POSITION parameter is used, multiple INTO TABLE clauses can process the same record in different ways, allowing multiple formats to be recognized in one input file.

The bind array must be large enough to contain a single row.

If the maximum row length exceeds the size of the bind array, as specified by the BINDSIZE parameter, then SQL*Loader generates an error. Otherwise, the bind array contains as many rows as can fit within it, up to the limit set by the value of the ROWS parameter. (The maximum value for ROWS in a conventional path load is 65534.)

Although the entire bind array need not be in contiguous memory, the buffer for each field in the bind array must occupy contiguous memory. If the operating system cannot supply enough contiguous memory to store a field, then SQL*Loader generates an error.

Large bind arrays minimize the number of calls to the Oracle database and maximize performance.

In general, you gain large improvements in performance with each increase in the bind array size up to 100 rows. Increasing the bind array size to be greater than 100 rows generally delivers more modest improvements in performance. The size (in bytes) of 100 rows is typically a good value to use.

In general, any reasonably large size permits SQL*Loader to operate effectively. It is not usually necessary to perform the detailed calculations described in this section. Read this section when you need maximum performance or an explanation of memory usage.

When you specify a bind array size using the command-line parameter BINDSIZE or the OPTIONS clause in the control file, you impose an upper limit on the bind array.

The bind array never exceeds that maximum.

As part of its initialization, SQL*Loader determines the size in bytes required to load a single row. If that size is too large to fit within the specified maximum, then the load terminates with an error.

SQL*Loader then multiplies that size by the number of rows for the load, whether that value was specified with the command-line parameter ROWS or the OPTIONS clause in the control file.

If that size fits within the bind array maximum, then the load continues - SQL*Loader does not try to expand the number of rows to reach the maximum bind array size. If the number of rows and the maximum bind array size are both specified, then SQL*Loader always uses the smaller value for the bind array.

If the maximum bind array size is too small to accommodate the initial number of rows, then SQL*Loader uses a smaller number of rows that fits within the maximum.

The bind array's size is equivalent to the number of rows it contains times the maximum length of each row.

bind array size =
    (number of rows) * (  SUM(fixed field lengths)
                        + SUM(maximum varying field lengths)
                        + ( (number of varying length fields)
                             * (size of length indicator) )
                       )

• Determining the Size of the Length Indicator
  Use the control file to determine the size of the length indicator.
• Calculating the Size of Field Buffers
  This section describes how to calculate the size of the field buffers.

Pay particular attention to the default sizes allocated for VARCHAR, VARGRAPHIC, and the delimited forms of CHAR, DATE, and numeric EXTERNAL fields.

They can consume enormous amounts of memory - especially when multiplied by the number of rows in the bind array. It is best to specify the smallest possible maximum length for these fields. Consider the following example:

CHAR(10) TERMINATED BY "," 

With byte-length semantics, this example uses (10 + 2) * 64 = 768 bytes in the bind array, assuming that the length indicator is 2 bytes long and that 64 rows are loaded at a time.

With character-length semantics, the same example uses ((10 * s) + 2) * 64 bytes in the bind array, where "s" is the maximum size in bytes of a character in the data file character set.

Now consider the following example:

CHAR TERMINATED BY "," 

Regardless of whether byte-length semantics or character-length semantics are used, this example uses (255 + 2) * 64 = 16,448 bytes, because the default maximum size for a delimited field is 255 bytes. This can make a considerable difference in the number of rows that fit into the bind array.

When calculating a bind array size for a control file that has multiple INTO TABLE clauses, calculate as if the INTO TABLE clauses were not present.

Imagine all of the fields listed in the control file as one, long data structure—that is, the format of a single row in the bind array.

If the same field in the data record is mentioned in multiple INTO TABLE clauses, then additional space in the bind array is required each time it is mentioned. It is especially important to minimize the buffer allocations for such fields.