How to extract the rowid from the source endpoint to the target endpoint?

[yzh]

How to extract the rowid from the source endpoint to the target endpoint

And use this rowid as a new column in target table.

Our source endpoint and target endpoint are both oracle databases.

[john_wang · Accepted solution]

Hello @yzh ,

Thanks for reaching out!

By default the datatype  ROWID is not supported directly by Replicate, this is a known limitation.  However we have a workaround by using the Transformation: Source Lookup - Oracle ROWID .

Please take note even you can replicate the ROWID from source Oracle database to target side Oracle table however this column in target lost itself functionality already as it's not the original "ROWID", the hidden column in an Oracle table but it become a regular column.

Hope this helps.

Regards,

John.

[yzh]

Thanks.It's working.If oracle rowid of the source changes, will the target endpoint also apply it？

[john_wang]

Hello @yzh ,

Thanks for your update.

In general the ROWID Pseudocolumn values are persisted in a table, it's not changed by regular DML operations unless in some exceedingly rare scenarios. anyway, Replicate will replicate the latest ROWID to target side by using source lookup functions.

Regards,

John.

[yzh]

I tried using source_lookup to add rowid, but the source table does not have an index. Is this the reason for many duplicate values in the target table rowid? What if it is not for tables without indexes?

[Heinvandenheuvel]

>>  source_lookup to add rowid, but the source table does not have an index

NO can do. What were you thinking? Were you thinking? Best you can do is provide enough columns  in the where clause and corresponding COND_PARAMS to make it (mostly?) unique. As per full example from the Replicate documentation below.

But let's take a step back, why do you even want to row-id there? I have a strong suspicion that it was exactly because the source rows did not have a unique identified and one was desirable on the target. The thinking was that the RowId might be a good solution for this application level problem.  It is unlikely to be a great solution as for example it doesn't really allow on to re-generate the table on the source without issues.

Maybe, just maybe, if you articulate the actual problem you are trying to solve, instead of asking about an issue with one of the possible solutions the friendly folks reading the forum can come up with a more appropriate solution? Or maybe you just have to bite the bullet and do something on the source. Maybe add a sequence number to the table, rename to <table> to <table_with_seq>, add a view to expose the new table under the old name and replicate the <table_with_seq> renaming to <table> on target. Or add an AFTER INSERT trigger to pick up the RowId and add it as a column? (ugly, expensive). Or maybe a 'timestamp' is 'unique enough' in combination with some other column, and useful in its own right.

Good luck,

Hein.

source_lookup(10000 ,'HR' ,'DEPARTMENTS' ,'DEPARTMENT_NAME’ ,
'COMPANY_ID=? and DIVISION_ID=? and DEPT_ID=?' ,
$COMP_ID , $DIV_ID , $DEPT_ID )

[yzh]

Thank you for your answer.

Our task have many tables.

Several of the tables don't have indexes, causing frequent delays in this task.

And source table may be have duplicate datas.

So my initial idea was to use ROWID as an index.

Could you please help me see if there is a better solution？