DB Alias Tab for Oracle

Use the DB Alias tab to define the parameters needed for the Oracle loader. The availability of certain options and fields might be dependent upon your entries for related options and fields on the tab.

Load Request Editor with DB alias for Oracle as the active tab. The elements are described in the following text.

Mode

Insert
Insert rows from the source file into empty destination tables. If destination tables contain data, the loader returns an error.
Replace
Clear all data in the destination tables and replace with rows from the source file. Replace might be more resource-intensive than truncate which has no logging.
Append
Insert the rows from the source file into the destination tables. If the primary key values match, duplicate rows are discarded or inserted into the exception table (if specified).
Truncate
Replace without logging deleted rows. RI constraints must be disabled.

Load Method

Conventional Path (default)
Conventional path is typically used for:
  • Loading few rows into a large table that has indexes or referential integrity constraints.
  • Applying SQL functions to specific data.
Direct Path
Direct path is typically used for loading and indexing a large volume of data quickly. Direct path load runs faster than the conventional path, especially when you select the option for parallel loads. To use the direct path, the database client and the server must run on the same platform. For complete details, see documentation provided by Oracle.
Parallel Loads
Allow multiple jobs to run concurrently.
UnRecoverable Load
Do not write data to the redo logs.

Disable Triggers

Never
Never disable database triggers for the loader.
Always
Disable database triggers during the load process and re-enable triggers when the process is completed.
Prompt
Display the Disabling Trigger/Constraint Confirmation dialog to list tables with associated triggers. Right-click the Status After Process column to enable or disable the corresponding trigger or constraint after the load completes.

Disable Constraints

Never
Never disable referential integrity constraints for the loader. This option causes a warning message when you run the loader.
Constraint Non-Disablement Warning dialog that is displayed if referential integrity constraints are not disabled.
Always
Disable constraints during the load process and re-enable constraints when the process is completed.
Prompt
Display the Disabling Trigger/Constraint Confirmation dialog to list tables with associated constraints. Right-click the Status During Process column to enable or disable the corresponding trigger or constraint during the load. Right-click the Status After Process column to enable or disable the corresponding trigger or constraint after the load completes.

Options

Perform Load
Run the loader automatically after file conversion. Options to disable triggers and constraints apply. Clear to convert the data and create a BAT file. To run the loader, you must edit the BAT file to include password information, ensure that destination tables are empty, and run.
Use Named Pipe

In a UNIX environment, but not a Windows environment, load performance might be enhanced by using a named pipe. When this option is used, the load process runs in a single phase, rather than a two-stage, process, resulting in reduced elapsed time for load processing. as the row data is loaded immediately after conversion to loader format, space requirements are also reduced significantly, as compared to the two-stage process. The solution creates the named pipe and writes the file to it. The load utility reads the data from the named pipe and submits the data to the loader.

Delete files if Successful
Fast load confirmation is not available. Delete the data files after the loader completes successfully. This option is unavailable if you selected Use Named Pipe.
Delete files if Failure
Fast load confirmation is not available. Delete the data files if the loader does not complete successfully. This option is unavailable if you selected Use Named Pipe.
Create Exception Tables

An exception table records each source row that violates unique index or primary key rules. The exception table includes a copy of the row with a timestamp and a description of the violation.

The solution ensures that names of exception tables do not match names of destination tables specified in the load request. You can modify the exception table names, but ensure that the names do not match names of existing database tables. Leave the exception table name blank to discard duplicate rows. Existing exception tables are dropped before starting the load utility. Dropping the tables ensures that newly created exception tables contain only the information for the current database load. Before tables are dropped, a dialog on which you can change the creator ID to create different exception tables, opens.

Click Tools > Edit Exception Table Map from the menu to display the Exception Table Mapping dialog.

Exception Table Mapping dialog
Inline LOBs
Include LOBs in a data file (inline with the table data). If this option is not selected, each LOB is loaded from a separate file that is referenced in the Oracle loader control file.
Create Discard File
Create a discard file to use during the load process.
Discard Limit
The number of rows (up to 999999999) that the loader can discard because of errors. Stop processing when the specified number of rows is discarded.
  • To end the process if a single row is discarded, specify 1 as the maximum.
  • To allow an unlimited number of rows to be discarded, specify zero (0) or leave blank.
Compressed Files
Create variable length rather than fixed-length data rows. This option can potentially reduce space required for data conversion, but might increase processing time.
Delimiter
Select a column delimiter from the list. To avoid a conversion error, do not use a column delimiter that is in the data that is loaded.
Commit
Specify the number of rows after which the loader commits changes to the database, up to the limit specified in Product Options.
Load When Source is Empty
Complete the load if a table to be loaded contains no rows, deleting any rows from the target. If not selected, empty source tables are skipped.

Additional Loader Parameters

You can augment the loader arguments created automatically with additional loader parameters. The parameters you create are appended to the list created automatically, but are not validated before starting the loader. For valid operands, see the database loader documentation. If additional loader parameters are forced from within product options, you cannot modify them.

Work path for interim files

Specify a default directory path for storing the temporary loader files.



Feedback