Sqlldr reference manual

To completely disable the date cache feature, set it to 0. Every table has its own date cache, if one is needed. A date cache is created only if at least one date or timestamp value is loaded that requires datatype conversion in order to be stored in the table.

The date cache feature is only available for direct path loads. It is enabled by default. The default date cache size is elements. If the default size is used and the number of unique input values loaded exceeds , then the date cache feature is automatically disabled for that table.

However, if you override the default and specify a nonzero date cache size and that size is exceeded, then the cache is not disabled. You can use the date cache statistics entries, hits, and misses contained in the log file to tune the size of the cache for future similar loads.

DIRECT specifies the data path, that is, the load method to use, either conventional path or direct path. A value of true specifies a direct path load. A value of false specifies a conventional path load.

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 is specified also in the control file, the command-line value overrides it. To stop on the first discarded record, specify one 1.

To specify that all errors be allowed, use a very high number. Any data inserted up that point, however, is committed. Therefore, multitable loads do not terminate immediately if errors exceed the error limit. There are three possible values:. It means the load is performed using either conventional or direct path mode. These SQL statements can be edited and customized. 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.

The results of doing the load this way will be different than if the load were done with conventional or direct path. Note that the external tables option uses directory objects in the database to indicate where all datafiles 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 datafiles, and you must have WRITE access to the directory objects where the output files are created. Extract those SQL statements and change references to directory objects to be the directory object that you have privileges to access. Then, execute those SQL statements. Creates a table in the database that describes all fields in the datafile that will be loaded into any table. This is because the field names may not be unique across the different tables in the control file.

Built-in functions and SQL strings cannot be used for object elements when you insert data into a database table from an external table. FILE specifies the database file to allocate extents from. It is used only for parallel loads. LOAD specifies the maximum number of logical records to load after skipping the specified number of records.

No error occurs if fewer than the maximum number of records are found. By default, the multithreading option is always enabled set to true on multiple-CPU systems. On single-CPU systems, multithreading is set to false by default. To use multithreading between two single-CPU systems, you must enable multithreading; it will not be on by default. This will allow stream building on the client system to be done in parallel with stream loading on the server system.

It also means that the characters in positions 3 through 5 are removed from the record even if the continuation characters are not in positions 3 through 5. Note that columns 1 and 2 are not removed from the physical records when the logical records are assembled. Therefore, the logical records are assembled as follows the same results as for Example The specification of fields and datatypes is described in later sections.

The table must already exist. If the table is not in the user's schema, then the user must either use a synonym to reference the table or include the schema name as part of the table name for example, scott. That method overrides the global table-loading method. The following sections discuss using these options to load data into empty and nonempty tables. It requires the table to be empty before loading. Case study 1, Loading Variable-Length Data, provides an example. If data does not already exist, the new rows are simply loaded.

Case study 4, Loading Combined Physical Records, provides an example. The row deletes cause any delete triggers defined on the table to fire. For more information about cascaded deletes, see the information about data integrity in Oracle Database Concepts. To update existing rows, use the following procedure:.

It is valid only for a parallel load. 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. For example, the following clause indicates that any record with the value "q" in the fifth column position should be loaded:.

Parentheses are optional, but should be used for clarity with multiple comparisons joined by AND, for example:. If all data fields are terminated similarly in the datafile, you can use the FIELDS clause to indicate the default delimiters.

Description of the illustration terminat. Description of the illustration enclose. You can override the delimiter for any given column by specifying it after the column name. Specifying Delimiters for a complete description of the syntax.

Assume that the preceding data is read with the following control file and the record ends after dname:. In this case, the remaining loc field is set to null. This option inserts each index entry directly into the index, one record at a time.

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.

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:. The number of records to be loaded is small compared to the size of the table a ratio of or less is recommended. Some data storage and transfer media have fixed-length physical records.

When the data records are short, more than one can be stored in a single, physical record to use the storage space efficiently. For example, assume the data is as follows:. The same record could be loaded with a different specification. The following control file uses relative positioning instead of fixed positioning. Instead, scanning continues where it left off.

A single datafile might contain records in a variety of formats. Consider the following data, in which emp and dept records are intermixed:. 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:. The records in the previous example could also be loaded as delimited data.

The following control file could be used:. It causes field scanning to start over at column 1 when checking for data that matches the second format. A single datafile 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:. The following input datafile contains a mixture of these row objects subtypes.

A type ID field distinguishes between the three subtypes. See case study 5, Loading Data into Multiple Tables, for an example. Multiple rows are read at one time and stored in the bind array. It does not apply to the direct path load method because a direct path load uses the direct path API, rather than Oracle's SQL interface. The bind array must be large enough to contain a single row. 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.

Although the entire bind array need not be in contiguous memory, the buffer for each field in the bind array must occupy contiguous memory. 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 rows.

Increasing the bind array size to be greater than rows generally delivers more modest improvements in performance. The size in bytes of rows is typically a good value to use. 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.

The bind array never exceeds that maximum. If that size is too large to fit within the specified maximum, the load terminates with an error. The bind array's size is equivalent to the number of rows it contains times the maximum length of each row.

The maximum length of a row is equal to the sum of the maximum field lengths, plus overhead, as follows:. Many fields do not vary in size.

These fixed-length fields are the same for each loaded row. There is no overhead for these fields. The maximum lengths describe the number of bytes that the fields can occupy in the input data record. That length also describes the amount of storage that each field occupies in the bind array, but the bind array includes additional overhead for fields that can vary in size. When specified without delimiters, the size in the record is fixed, but the size of the inserted field may still vary, due to whitespace trimming.

So internally, these datatypes are always treated as varying-length fields—even when they are fixed-length fields. A length indicator is included for each of these fields in the bind array. The space reserved for the field in the bind array is large enough to hold the longest possible value of the field. The length indicator gives the actual length of the field for each row.

On most systems, the size of the length indicator is 2 bytes. On a few systems, it is 3 bytes. To determine its size, use the following control file:. This control file loads a 1-byte CHAR using a 1-row bind array. In this example, no data is actually loaded because a conversion error occurs when the character a is loaded into a numeric column deptno.

The bind array size shown in the log file, minus one the length of the character field is the value of the length indicator. Table through Table summarize the memory requirements for each datatype. 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:. This can make a considerable difference in the number of rows that fit into the bind array.

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. It is especially important to minimize the buffer allocations for such fields. In general, the control file has three main sections, in the following order: Sessionwide information Table and field-list information Input data optional section Example shows a sample control file.

Comments in the Control File Comments can appear anywhere in the command section of the file, but they should not appear within the data. Precede any comment with two hyphens, for example: --This is a comment All text to the right of the double hyphen is ignored, until the end of the line. Operating System Considerations The following sections discuss situations in which your course of action may depend on the operating system you are using. Specifying a Complete Path If you encounter problems when trying to specify a complete path name, it may be due to an operating system-specific incompatibility caused by special characters in the specification.

Therefore, you should avoid creating strings with an initial quotation mark. Using the Backslash as an Escape Character If your operating system uses the backslash character to separate directories in a path name, and if the version of the Oracle database running on your operating system implements the backslash escape character for filenames and other nonportable strings, then you must specify double backslashes in your path names and use single quotation marks.

Escape Character Is Sometimes Disallowed The version of the Oracle database running on your operating system may not implement the escape character for nonportable strings. Specifying Datafiles To specify a datafile that contains the data to be loaded, use the INFILE keyword, followed by the filename and optional file processing options string. Note: The information in this section applies only to primary datafiles.

If you have data in the control file as well as datafiles, you must specify the asterisk first in order for the data to be read. It specifies the datafile format. It also optimizes datafile reads.

The syntax used for this string is specific to your operating system. See Specifying Datafile Format and Buffering. For example, the following excerpt from a control file specifies four datafiles with separate bad and discard files: INFILE mydat1. 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 created and the rejected records are logged. Note: On some systems, a new version of the file may be created if a file with the same name already exists.

Examples of Specifying a Bad File Name To specify a bad file with filename sample and default file extension or file type of. Criteria for Rejected Records A record can be rejected for the following reasons: Upon insertion, the record causes an Oracle error such as invalid data for a given datatype.

The record violates a constraint or tries to make a unique index non-unique. A discard file is created according to the following rules: You have specified a discard filename and one or more records fail to satisfy all of the WHEN clauses specified in the control file. If no records are discarded, then a discard file is not created. Description of the illustration discard.

Examples of Specifying a Discard File Name The following list shows different ways you can specify a name for the discard file from within the control file: To specify a discard file with filename circular and default file extension or file type of.

This will result in the following error message being reported if the larger target value exceeds the size of the database column: ORA 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.

Character-Length Semantics Byte-length semantics are the default for all datafiles except those that use the UTF16 character set which uses character-length semantics by default. Interrupted Load s Loads are interrupted and discontinued for a number of reasons.

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

Space errors when loading data into multiple subpartitions that is, loading into a partitioned table, a composite partitioned table, or one partition of a composite partitioned table : If space errors occur when loading into multiple subpartitions, the load is discontinued and no data is saved unless ROWS has been specified in which case, all data that was previously committed will be saved.

Load Discontinued Because of Fatal Errors If a fatal error is encountered, the load is stopped and no data is saved unless ROWS was specified at the beginning of the load.

A variable length field defaults to bytes for a CHAR. If no datatype is specified, it defaults to a CHAR of bytes as well.

Set the limit of your filesize with ulimit ksh and sh or limit csh command to a value larger than the size of your sqlloader datafile. Educational purpose only. Log in. Ora Training Blog Serious about Oracle. Leave a comment Trackback. Reply Quote. Hi, good post. I have been wondering about this issue,so thanks for posting. Leave a comment Cancel reply.

RSS for comments on this post. Search this blog. Hot topics Oracle Fusion Applications installation This is a personal blog for non-profit educational purpose only. It has no affiliation with Oracle or any company and any views expressed here are of the author himself only. Specifies the name of the bad file.

You may include a path as part of the name. By default, the bad file takes the name of the control file, but with a. If you specify a different name, the default extension is still. However, if you use the BAD parameter to specify a bad file name, the default directory becomes your current working directory. If you are loading data from multiple files, then this bad file name only gets associated with the first file being loaded.

Specifies the maximum size, in bytes, of the bind array. This parameter overrides any bind array size computed as a result of using the ROWS parameter.

The default bind array size is 65, bytes, or 64K. Specifies the name, which may include the path, of the control file. The default extension is. Specifies the name of the file containing the data to load. By default, the name of the control file is used, but with the. If you are loading from multiple files, you can only specify the first file name using this parameter.

A value of TRUE results in a direct path load. Specifies the name of the discard file. By default, the discard file takes the name of the control file, but it has a.

If you are loading data from multiple files, then this discard file name only gets associated with the first file being loaded. Sets an upper limit on the number of logical records that can be discarded before a load will terminate. The default is to allow an unlimited number of discards. Specifies a limit on the number of errors to tolerate before the load is aborted. The default is to abort a load when the error count exceeds There is no way to allow an unlimited number of errors.

The best you can do is to specify a very high number for this parameter. Specifies the database data file from which to allocate extents.

Use this parameter when doing parallel loads, to ensure that each load session is using a different disk.