To write data to an Oracle target, configure an Oracle object as the Target transformation in a mapping. Specify the name and description of the Oracle target. Configure the target and advanced properties for the target object.
The following table describes the properties that you can configure for an Oracle target:
Property
Description
Connection
Name of the target connection.
You can select an existing connection, create a new connection, or define parameter values for the target connection property.
If you want to overwrite the target connection properties at runtime, select the Allow parameter to be overridden at run time option.
Target Type
Type of the Oracle target object available. You can choose from the following target types:
- Single
- Parameter
Parameter
A parameter file where you define values that you want to update without having to edit the task.
Select an existing parameter for the target object or click New Parameter to define a new parameter for the target object.
The Parameter property appears only if you select parameter as the target type.
If you want to overwrite the target object at runtime, select the Allow parameter to be overridden at run time option.
When the task runs, the Secure Agent uses the parameters from the file that you specify in the advanced session properties.
Object
You can select an existing object from the list or create a target at run time.
Operation
Select the target operation. You can perform the following operations on an Oracle target:
- Insert
- Update
- Upsert
- Delete
- Data Driven
Truncate target
When you enable the Truncate Target option, the Secure Agent truncates the table. Default is false.
Enable Target Bulk Load
Uses Oracle bulk API to insert data in bulk mode.
When you create a mapping task, you can use the Oracle bulk API to perform insert operation.
Pre SQL
Pre-SQL command to run against the target database before writing data to the target.
Post SQL
Post-SQL command to run against the target database after writing data to the target.
Update Override
An update SQL statement that updates the data in an Oracle target table.
The update SQL statement you specify overrides the default update statements that the Secure Agent generates to update the target based on key columns. You can define an update override statement to update target tables based on both key or non-key columns. You cannot validate the update SQL statement in the SQL transformation.
In the override statement, you must enclose all reserved words in quotation marks.
Reject File Directory
The directory that stores the rejected files.
Specify the directory where you want to store the rejected files.
Reject File Name
Name of the rejected file that is stored in the reject file directory.
Schema Name
Overrides the schema name of the target object.
If you select the Forward Rejected Rows option, the Secure Agent flags the rows for reject and writes them to the reject file. If you do not select the Forward Rejected Rows option, the Secure Agent drops rejected rows and writes them to the session log file. The Secure Agent does not write the rejected rows to the reject file.
Note: If there is an error row when you run a task to write data to an Oracle target in bulk mode, the Secure Agent writes the batch that contains the error row in normal mode.
Configuring an update override for the target
To override the default update SQL statement that the Secure Agent generates, you can specify an SQL statement in the Update Override field of the advanced target properties.
1Next to the Update Override field, click Configure.
2In the Update Override SQL Editor dialog box, enter the update SQL statement that the Secure Agent must use.
3Click Generate SQL to generate an SQL query.
4Click Format SQL to format the SQL query you entered.
You can modify the generated SQL query in the SQL editor based on your requirement.
5Click OK.
Enabling array update and upsert operations for an Oracle target
You can configure an Oracle mapping to update or upsert data in batches of arrays.
When you configure an array update or upsert, the Secure Agent clubs all records together into arrays and writes those arrays of data into the Oracle target in batches instead of updating or upserting data row by row. Array update and upsert operations reduce the network traffic and optimizes performance.
To configure an array update or upsert, you must set the following custom properties:
EnableArrayUpsert
Before you upsert data into an Oracle target, configure the EnableArrayUpsert custom property in the Secure Agent properties or in the Oracle mapping task properties, and set its value to yes.
EnableArrayUpdate
Before you update data into an Oracle target, configure the EnableArrayUpdate custom property in the Secure Agent properties or in the Oracle mapping task properties, and set its value to yes.
Note: You cannot array update or upsert data to an Oracle target when you select the data driven operation in an Oracle target transformation.