1.7 Bulk Loading into Version-Enabled Tables

You can use SQL*Loader to perform bulk loading into version-enabled tables, but you must also call some special Workspace Manager procedures, and some restrictions apply.

You can perform both direct-path and conventional-path bulk loading of data into either the latest version of any workspace or into the root version (version number 0, which is in the LIVE workspace). The root version is the ancestor of all other versions, so data in the root version is visible from all other workspaces (unless non-LIVE workspaces have updated the data).

Follow these general steps for bulk loading into a version-enabled table:

1. Call the BeginBulkLoading procedure to prepare the table for bulk loading. When data is being bulk loaded into a version-enabled table, DML and workspace operations on the table are not allowed, although workspace operations that do not involve this table are allowed. The BeginBulkLoading procedure prevents invalid operations from being performed on this table.
2. Use SQL*Loader to perform the bulk loading. Only one line needs to be changed in the control file, to specify the <table_name>_LT name. For example, assume that the existing control file has the following line:

   Load data into table departments (name, loc)
   

   The line in the control file for bulk loading into the version-enabled table should be changed to:

   Load data into table departments_LT (name, loc)

   Note:

   In versions of Workspace Manager before 12.1, it was necessary to include the wm_version column. This column is now automatically populated by Workspace Manager, and an error will be generated if you populate it explicitly. This ensures that all the bulk-loaded rows will be tagged with the appropriate version, and that the other Workspace Manager-specific columns for these rows will have null values.

   If the table was version-enabled with the history option, create and retire times can be bulk loaded into the wm_createtime and wm_retiretime columns of <table_name>_LT.

3. Complete the bulk loading process by calling either the CommitBulkLoading procedure to commit the bulk loading changes or the RollbackBulkLoading procedure to roll back the bulk loading changes.

If you commit the bulk loading changes, Workspace Manager ensures that the data is updated in the required workspace and version. By default, the bulk-loaded data is checked for each unique or referential constraint defined on the table, and any bulk-loaded rows that are in violation of any constraints are moved to a discards table specified as a parameter to the CommitBulkLoading procedure. If you specified to check for duplicates (that is, records in the data to be bulk loaded that have the same values in the primary key columns), for any duplicate records only the record with the lowest ROWID value is loaded into the table, and the rest are moved to the discards table.

The following restrictions apply to bulk loading with version-enabled tables in the current release:

• Bulk loading into a table with a self-referential integrity constraint is not allowed.

• Bulk loading into a workspace, other than LIVE, that has continually refreshed child workspaces is not allowed.

• Only the owner of a table or a user with the WM_ADMIN system privilege can bulk load into a version-enabled table.

• The user that is bulk loading the version-enabled table must have the INSERT privilege for <table_name>_LT.

• User-defined triggers on version-enabled tables are not executed during bulk loading.

• Session locking mode is not enforced for the bulk-loaded rows. Use the LockRows procedure to lock these rows.