You configure the Oracle connector to perform a sparse
lookup on an Oracle database.
Before you begin
- To specify the format of the data records that the Oracle connector
reads from an Oracle database, set up column definitions on a link.
- Configure the Oracle connector as a source for the reference data.
About this task
In a sparse lookup, the connector runs the specified SELECT
statement or PL/SQL block one time for each parameter set that arrives
in the form of a record on the input link to the Lookup stage. The
specified input parameters in the statement must have corresponding
columns defined on the reference link. Each input record includes
a set of parameter values that are represented by key columns. The
Oracle connector sets the parameter values on the bind variables in
the SELECT statement or PL/SQL block, and then the Oracle connector
runs the statement or block. The result of the lookup is routed as
one or more records through the reference link from the Oracle connector
back to the Lookup stage and from the Lookup stage to the output link
of the Lookup stage. A sparse lookup is also known as a direct lookup
because the lookup is performed directly on the database.
Typically,
you use a sparse lookup when the target table is too large to fit
in memory. You can also use the sparse lookup method for real-time
jobs.
You can use the sparse lookup method only in parallel
jobs.
Procedure
- Add a Lookup stage to the job design
canvas, and then create a reference link from the Oracle Connector
stage to the Lookup stage.
- Double-click the Oracle Connector
stage.
- From the Lookup Type list, select Sparse.
- Specify the key columns:
- If you set Generate SQL to Yes when
you configured the connector as a source, specify the table name,
and then specify the key columns on the Columns page.
- If you set Generate SQL to No when
you configured the connector as a source, specify a value for the Select
statement property.
In the select part of the
SELECT statement, list the columns to return to the Lookup stage.
Ensure that this list matches the columns on the Columns page.
- On the Properties page, specify a table name, and then
specify a WHERE clause for the lookup.
Key columns that
follow the WHERE clause must have the word ORCHESTRATE and
a period added to the beginning of the column name. ORCHESTRATE can
be all uppercase or all lowercase letters, such as ORCHESTRATE.Field001.
The following SELECT statement is an example of the correct syntax
of the WHERE clause: select Field002,Field003 from MY_TABLE
where Field001 = ORCHESTRATE.Field001
. The column names that
follow the word ORCHESTRATE must match the column names
on the Columns page.
- To save the changes, click OK.
- Double-click the Lookup stage.
- Map the input link and reference link
columns to the output link columns and specify conditions for a lookup
failure:
- Drag or copy the columns from the input link
and reference link to your output link.
- To define conditions for a lookup failure, click
the Constraints icon in the menu.
- In the Lookup Failure column, select a value,
and then click OK. If you select Reject,
you must have a reject link from the Lookup stage and a target stage
in your job configuration to capture the rejected records.
- Click OK.
- Save, compile, and run the job.