SQL*Loader Changes

Oracle7 Server Release 7.1

• Data can be loaded in parallel with the PARALLEL direct path load. See .

• Direct loads can load data into synonyms for tables (but not synonyms for views, or synonyms for synonyms). See .

• Direct loads can specify UNRECOVERABLE to improve performance by disabling redo logging. See .

• In Release 7.1, direct loads can specify a datafile character set which is different from the control file using the CHARACTERSET clause. In 7.0 this was possible for a conventional load, but not for a direct load. See .

As before, the control file must be in the same character set as the session so that the session can interpret it. And, as before, the database character set can be different -- data conversion happens automatically.

• The REPLACE option returns to using DELETE TABLE semantics. That was the standard behavior before Release 7.0. Release 7.1 returns to that standard, and no longer performs a table TRUNCATE when REPLACE is specified. With this change:

• Integrity constraints on the table do not have to be disabled before the load.

• Delete triggers that are defined on the table fire as rows are deleted.

• DELETE privilege on the table is sufficient if the table is not in your own schema. See for more information.

• A new option, TRUNCATE, uses fast table truncation for a load. This option does not fire delete triggers for the truncated rows. Integrity constraints on the table must be disabled before loading with TRUNCATE. Finally, if the table is not in your own schema, the DELETE ANY TABLE privilege is required. See .

• Previously, only one direct path load could be performed on any given table. With Oracle Server release 7.1, you can optionally specify a PARALLEL clause within the SQL*Loader control file or on the command line to indicate that multiple SQL*Loader direct path load sessions can share access to the table. See .

• The FILE parameter allows different parts of a parallel load to allocate extents from different database files.

Oracle7

• Direct loads bypass referential integrity constraints and database triggers. See .

• Input character conversion possible with the CHARACTERSET clause. See .

• Multi-byte (Asian language) characters supported. See .

• Fast table truncation used for REPLACE option. See .

Release 1.1

• Maximum performance available with the direct path option. See Chapter 8.

• Operating system sorts can be used for high-speed sorting, fast indexing, and reduced need for temporary storage. See .

• DIRECT command-line parameter added. See .

• Meaning of ROWS parameter extended for direct loads. See .

• CONTINUE_LOAD and table-level SKIP clauses added to continue a multiple-table direct path load after an interruption. See .

• The file processing string allows optimizing datafile reads. See .

• The keywords RECLEN, STREAM, RECORD, FIXED, BLOCKSIZE, and VARIABLE are now obsolete. They are still recognized for upward compatibility, but they have no effect.

• Single quotation marks recommended for filenames. See .

• When loading records that contain only generated data, SQL*Loader skips reading of input file. See .

• The bad file is only created if it is needed. See .

• SQL string allows use of SQL operators on fields. See on page 6-73.

• RAW datatype added. See .

• Insert current date/time with SYSDATE keyword. See .

• Initial and trailing field delimiters can be different. See .

• Mismatches in field length specifications generate warning messages in native datatype fields and character datatype fields. See and .

• Default maximum field size for delimited fields is 255 bytes instead of 240 bytes. See .

• Command-line specifications override control file options. See and .

• Bad file command-line specification overrides control-file specification. See and .

• Discard file command-line specification overrides control-file specification. See and .

• Arguments can be specified in a separate file with PARFILE command-line parameter. See .

• Because it has been replaced by the file processing options string, the RECLEN parameter is obsolete.

In addition, the old syntax

[ STREAM | RECORD | FIXED len [BLOCKSIZE size] | VARIABLE [len] ]

does not affect the way SQL*Loader reads the datafile, although the syntax is recognized to maintain upward compatibility.

Release 1.0.26

• Load character fields with all whitespace intact, using the PRESERVE BLANKS option. See .

• Test to see if a field of undetermined length is all blank with the BLANKS keyword. See .

• All-blank numeric fields no longer automatically loaded as NULL. See .

Release 1.0.22

• ZONED DECIMAL datatype added. See .

• Specifying how to handle missing fields at the end of a record with the TRAILING NULLCOLS keyword. See .

• Putting special characters into quoted strings with the quoted-string escape character. See .