Oracle - Lookup
Overview
The Oracle - Lookup Snap looks up records in the target Oracle table and returns a selected set of fields for every matched record.
The Snap executes one request per multiple input documents to avoid making a request for every input record.
JSON paths can be used in the Snap properties and will have values from an incoming document substituted into the properties. However, documents missing values for a given JSON path will be written to the Snap's error view. After a query is executed, the query's results are merged into the incoming document.
Queries produced by the Snap have the format:
SELECT [Output fields] FROM [Table name] WHERE
[C1 = V11 AND C2 = V21 AND...[Cn = Vn1] OR
[C1 = V12 AND C2 = V22 AND...[Cn = Vn2] OR
....................................... OR
[Cn = V1n AND Cm = V2m AND...[Cn = Vnm]The Snap ignores any duplicated lookup condition in the input document stream since it maintains a cache for lookup conditions internally.
- This is a Read-type Snap.
Works in Ultra Tasks
The Oracle JDBC Driver is upgraded from OJDBC6 JAR (v11.2.0.4) to OJDBC10 JAR (v19.20.0.0) in the latest distribution in October 2023 and deployed to the stable distribution in the November 2023 release (after the Snaplex upgrade). The latest JDBC driver upgrade is backward-compatible. Learn more: Oracle JDBC driver and JOOQ upgrades.
You can consume this driver upgrade with the 434patches23000 Snap Pack version.
This JDBC driver upgrade has resulted in specific behavior changes that include errors, status codes, and success and failure messages. Learn more about the behavior changes to ensure your migration to the upgraded driver is seamless.
Prerequisites
None.
Snap views
| Type | Description | Examples of upstream and downstream Snaps |
|---|---|---|
| Input |
This Snap has exactly one document input view. Each document should have values for one AND clause in the WHERE statement. Expected input: Each document in the input view should contain a Map data of key-value entries. Input data may contain values needed to evaluate expressions in the Object type, Output fields, and Conditions properties. Ensure input data types match column data types in the database table. |
|
| Output |
This Snap has exactly one document output view. The output document includes the corresponding input data under the "original" key. For each input document, if there are no results from the query, there won't be output routing to the output view. Expected output: Each document in the output view contains a Map data of key-value entries, where keys are the Output fields property values. The input data that has produced the corresponding output data is also included in the output data under the "original" key. |
|
| Learn more about Error handling. | ||
Snap settings
- Expression icon (
): Allows using
pipeline parameters to set field values dynamically (if enabled). SnapLogic Expressions
are not supported. If disabled, you can provide a static value. - SnapGPT (
): Generates SnapLogic Expressions
based on natural language using SnapGPT. Learn
more. - Suggestion icon (
): Populates a
list of values dynamically based on your Snap configuration. You can select only one
attribute at a time using the icon. Type into the field if it supports a comma-separated
list of values. - Upload
: Uploads files. Learn more.
| Field/Field set | Description |
|---|---|
Label String |
Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if more than one of the same Snaps is in the pipeline. Default value: Oracle - Lookup |
Schema name
String/Expression/ Suggestion |
Specify the database schema name. The suggestions in the Schema field are populated only when at least a single table exists in the schema. If no tables exist to use that schema, only SYS, SYSTEM, and XDB are populated. Default value: None Example: SYS |
Table name
String/Expression/ Suggestion |
Required. Enter or select the name of the table to execute the lookup query on. The values can be passed using the pipeline parameters but not the upstream parameter. Default value: None Example: people |
Output fields
String/Expression/ Suggestion |
Enter or select output field names for SQL SELECT statement. If this property is empty, the Snap selects all fields by executing the statement "SELECT * FROM ..." Default value: None Example: email, address, first, last |
| Lookup conditions | The lookup conditions are created by using the lookup column name and the lookup column value. Each row will build a condition, such as lookupColumn1 = $inputField. Each additional row will be concatenated using a logical AND. All rows together build the lookup condition being used to lookup records in the lookup table. |
Value
String/Expression |
Required. Enter or select the JSON path of the lookup column value. The value will be provided by the input data field. Default value: None Example: $email, $first, $last |
Lookup column name
String/Expression/ Suggestion |
Required. Enter or select lookup column name. Default value: None Example: email, first, last |
Pass-through on no lookup match
Checkbox |
When there is no lookup matching an input document, the input document will pass through to the output view if this property is selected. Otherwise, it will be written to the error view as an error condition. Default value: Not selected |
Column-specific timestamp precision
Checkbox |
Select this checkbox to display column-specific timestamp with millisecond, microsecond, or nanosecond precision in string type. Default value: Not selected |
|
Snap execution Dropdown list
|
Choose one of the three modes in
which the Snap executes. Available options are:
Default value: Validate & execute Example: Execute only |