Oracle Autonomous Database Connector > Mappings for Oracle Autonomous Database > Lookup transformation

Lookup transformation

You can create lookups for objects in an Oracle Autonomous Database mapping. You can retrieve data from an Oracle Autonomous Database lookup object based on the specified lookup condition.

Use the Lookup transformation to look up data in an Oracle Autonomous Database object. For example, the source table includes the customer code, but you want to include the customer name in the target table to make summary data easy to read. You can use the Oracle Autonomous Database Lookup transformation to look up the customer name in another Oracle Autonomous Database object.

You can add the following lookups to a mapping:

•Connected with cached
•Connected with uncached
•Unconnected with cached
•Dynamic lookup cache

For more information about the Lookup transformation, see Transformations in the Data Integration documentation.

Lookup properties for Oracle Autonomous Database

The following table describes the Oracle Autonomous Database lookup object properties that you can configure in a Lookup transformation:

Property	Description
Connection	Name of the lookup connection. You can select an existing connection, create a new connection, or define parameter values for the lookup connection property. If you want to override the lookup connection properties at runtime, select the Allow parameter to be overridden at run time option.
Source Type	Type of the source object. Select Single Object, Query, or Parameter. Note: You cannot configure uncached lookups when you select query as the source type.
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 lookup object or click New Parameter to define a new parameter for the lookup object. The Parameter property appears only if you select parameter as the lookup type. When the task runs, the Secure Agent uses the parameters from the file that you specify in the advanced session properties.
Lookup Object	Name of the lookup object for the mapping.
Multiple Matches	Behavior when the lookup condition returns multiple matches. You can return all rows, any row, the first row, the last row, or an error. You can select from the following options in the lookup object properties to determine the behavior: - Return first row - Return last row - Return any row - Return all rows - Report error

The following table describes the Oracle Autonomous Database advanced lookup properties that you can configure in a Lookup transformation:

Property	Description
Bucket Name	The Oracle Cloud Object Storage bucket name that contains the objects.
Staging Directory Path	Path of the local staging directory. Ensure that the user has write permissions on the directory. In addition, ensure that there is sufficient space to enable staging of the entire file. Default staging directory is the /temp directory on the machine that hosts the Secure Agent. The temporary files are created within the new directory.
EXPORT_DATA Command Options	The options to provide in the EXPORT_DATA command when loading the data from Oracle Autonomous Database to Oracle Cloud Object Storage. Example: DELIMITER=\|
Schema Name	Overrides the default schema name. Note: You cannot configure a custom query when you use the schema name.
Table Name	Overrides the default source table name. Note: When you select multiple objects or query as the source type, you cannot use this property.
Pre SQL	The pre SQL command to run on the Oracle Autonomous Database source table before the agent reads the data. For example, if you want to update records in the database before you read the records from the table, specify a pre SQL statement. The query needs to include a fully qualified table name. You can specify multiple pre SQL commands, each separated with a semicolon.
Post SQL	The post SQL command to run on the Oracle Autonomous Database table after the agent completes the read operation. For example, if you want to delete some records after the latest records are loaded, specify a post SQL statement. The query needs to include a fully qualified table name. You can specify multiple post SQL commands, each separated with a semicolon.
SQL Override	The SQL statement to override the default query used to read data from the Oracle Autonomous Database source.
Tracing Level	Determines the amount of detail that appears in the log file. You can select Terse, Normal, Verbose Initialization, or Verbose Data. Default is Normal.
Lookup Data Filter	Limits the number of lookups that the mapping performs on the cache of the lookup source table based on the value you specify in the filter condition. Maximum length is 32768 characters.

For more information about the Lookup transformation, see Transformations in the Data Integration documentation.

Rules and guidelines for the Lookup transformation

Consider the following rules and guidelines when you configure a Lookup transformation:

•By default, you cannot use null values in the filter condition for the connected Lookup transformation. To use null values in the filter condition, set the ENABLE_NULL_FLAG_FOR_UNCACHED_LOOKUP=true property as a JVM option under the DTM type in the Data Integration Server service on the Secure Agent.
•When you use a string column as the lookup field in a cached Lookup transformation and write to a flat file target, the mapping handles null values in the lookup condition differently depending on the configured read mode. If you use direct read mode, the mapping writes the null value in the string column as null in the target. However, if you use staging optimization, the mapping writes a null value in the string column as an empty string in the target.