Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!

Transformation: Source Lookup - Oracle ROWID

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
Michael_Litz
Support
Support

Transformation: Source Lookup - Oracle ROWID

Last Update:

Jun 4, 2024 8:51:56 AM

Updated By:

Steve_Nguyen

Created date:

May 4, 2021 8:42:36 PM

Environment

  • Replicate
  • Table/Field level transformation

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Field Transformation

On my Source  table from Oracle, I am using the source lookup function under Data Enrichment in order to retrieve the Oracle ROWID field value.  Not all fields are logged by oracle into the Redo Logs. In the case of ROWID we will use a source lookup function to query the source table and retrieve the field value, putting it  into the Add Column that we defined and called ROWID. This source lookup function is documented in the replicate user guide and the on-line help.

The general form of the function is as follows:

source_lookup(TTL,'SCHM','TBL','EXP','COND',COND_PARAMS)


For this example in am doing the lookup into the Employees table:

source_lookup('NO_CACHING','HR','EMPLOYEES','ROWID','EMPLOYEE_ID=:1',$EMPLOYEE_ID)


NO_CACHING is important to ensure it keeps changing for each value and doesn’t re-use values
HR is the schema in oracle
EMPLOYEES is the table
ROWID is what I want returned
EMPLOYEE_ID=:1 is the predicate for the lookup 
$EMPLOYEE_ID is the value from the redo log that we are using in the predicate in place of :1

NOTE: If the source lookup needed more than one field in the key then: 
(Multiple variables would look like: 'EMPLOYEE_ID=:1 AND DEPARTMENT_ID=:2)
$EMPLOYEE_ID, $DEPARTMENT_ID (in this example we are using field values that the task has read from the redo log file.

NOTE: That the lookup may have a performance implication and will need to be tested to see if it meets all of your latency criteria. Please also note that the Oracle ROWID is not guaranteed to persist. Traditional Oracle ROWID's can change if a table is quiesced or rebuilt with dbms_redefinition.

This expression is created for the Add Column transformation ROWID as seen in the screen shot below.

 

Transformation Screen shotTransformation Screen shot

 

NOTE: Unfortunately data enrichment function can not be tested on the screen, they must be saved and the task run for the transformation to be tested.

The screen shot below shows the target table with the ROWID field after the task has run.

Target Table with ROWID fieldTarget Table with ROWID field

 

Related Content

 

Labels (1)
Version history
Last update:
‎2024-06-04 08:51 AM
Updated by: