If the tables you are loading into already contain data, then you have three options:
If data already exists in the table, then SQL*Loader appends the new rows to it. If data does not already exist, then the new rows are simply loaded. You must have
SELECT privilege to use the
APPEND option. Case study 3, Loading a Delimited Free-Format File, provides an example. (See "SQL*Loader Case Studies" for information on how to access case studies.)
REPLACE option executes a SQL
DELETE FROM TABLE statement. All rows in the table are deleted and the new data is loaded. The table must be in your schema, or you must have
DELETE privilege on the table. Case study 4, Loading Combined Physical Records, provides an example. (See "SQL*Loader Case Studies" for information on how to access case studies.)
The row deletes cause any delete triggers defined on the table to fire. If
DELETE CASCADE has been specified for the table, then the cascaded deletes are carried out. For more information about cascaded deletes, see Oracle Database Concepts.
REPLACE method is a table replacement, not a replacement of individual rows. SQL*Loader does not update existing records, even if they have null columns. To update existing rows, use the following procedure:
UPDATEstatement with correlated subqueries.
TRUNCATE option executes a SQL
REUSE STORAGE statement, which means that the table's extents will be reused. The
TRUNCATE option quickly and efficiently deletes all rows from a table or cluster, to achieve the best possible performance. For the
TRUNCATE statement to operate, the table's referential integrity constraints must first be disabled. If they have not been disabled, then SQL*Loader returns an error.
Once the integrity constraints have been disabled,
DELETE CASCADE is no longer defined for the table. If the
DELETE CASCADE functionality is needed, then the contents of the table must be manually deleted before the load begins.