Skip to main content

Official Support Articles

Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team

Replicate - Oracle source: replicating LOB columns via ROWID if the table has no Primary Key or Unique Index

cancel
Showing results for 
Search instead for 
Did you mean: 
john_wang
Support
Support

Replicate - Oracle source: replicating LOB columns via ROWID if the table has no Primary Key or Unique Index

When working with Qlik Replicate, there is a limitation applies to LOB columns:

• When replicating a table that has no Primary Key or Unique Index, LOB columns will not be replicated 

This is because Replicate uses SOURCE_LOOKUP function to retrieve the LOB columns thru PK/UI position to the row(s) in source DB table. Hence the PK/UI is mandatory. 

Another limitation about ROWID data type:

• As the ROWID data type is not supported, materialized views based on a ROWID column are also not sup... 

This article describes how to overcome the above limitations and setup Replicate task to replicate LOB columns if the source table has no Primary Key or Unique Index. The workaround works for Oracle source only as the Oracle materialized view is introduced, and the Oracle internal hidden ROWID Pseudocolumn will be used as PK/UI of the materialized view.

Basically the idea is

(1) Expose hidden column ROWID as a regular column in materialized view (in step 3.)

(2) Change the data type ROWID to CHAR (explicitly, or implicitly) (in step 3.)

(3) Define the ROWID column as the PK/UI of the materialized view (in step 4.)

Then the materialized view can be replicated just as same as a regular Oracle table.

Environment

  • Qlik Replicate      All supported versions
  • Oracle database All supported versions

Resolution

1.  Assume there is a table in Oracle source database which has NO Primary Key nor Unique Index. Column "NOTES" is a CLOB column.

 

   CREATE TABLE kitclobnopk (
   id     integer,
   name   varchar(20),
   notes  clob
   );

 

2.  Create materialized view log for the above table

 

   create materialized view log on kitclobnopk WITH ROWID including new values;

 

3.  Create materialized view which exposes ROWID hidden column as regular column (alias KITROWID) to Replicate.

 

   CREATE MATERIALIZED VIEW KITCLOBNOPK_MV                                       
   REFRESH FAST ON COMMIT                                                        
   WITH ROWID                                                                    
   AS                                                                            
   select ROWIDTOCHAR(t.rowid) kitrowid, t.id, t.name,t.notes from kitclobnopk t;

 

      The materialized view data refreshes whenever the base master table changes (I/U/D) being committed. The MV can be custom to meet other requirements and add other rich logics etc.

4. Define the column ROWID as the Primary Key of the materialized view

 

   ALTER MATERIALIZED VIEW KITCLOBNOPK_MV ADD CONSTRAINT KITCLOBNOPK_MV_PK PRIMARY KEY (kitrowid);

 

5.  Add the materialized view kitclobnopk_mv to task as same as it's a regular table, the MV meets both Full Load and CDC demand. (NO need to include the original source master table "kitclobnopk" in Qlik Replicate task). 

6. Limitations and considerations of the WA

      (1) The single UPDATE operation in master table maybe translates to DELETE+INSERT pair operations in materialized view, depends on how the materialized view data refreshes. We may see that in Replicate GUI monitor perspective. 

      (2) If the ROWID changed in the materialized view (eg ALTER TABLE <tableName> SHRINK SPACE special operations ), then table or task reload requires. Just like the RRN Column in DB400 .

      (3) This is only a sample which works in internal labs (sanity test based on Oracle 12c source + Replicate 2022.5). No huge data stress test done, or being verified in a PROD system yet. For implementation and further questions, Professional Service engaged. 

 

Qlik Replicate  

Version history
Last update:
2 weeks ago
Updated by:
Contributors