topic Re: Possible to bring in another table from a different Oracle source into the same target DB? in Qlik Replicate

Possible to bring in another table from a different Oracle source into the same target DB?

desmondchew — Fri, 24 Jun 2022 07:31:16 GMT

I have setup replication from an Oracle DB source database eg: ABC, target DB is a MySQL database. The source schema let's call it schema: HR.

So application team require a table(ADMIN) from another source Oracle database XYZ. We have created a dblink that allows schema HR to access to this table - ADMIN from Oracle XYZ DB, in database ABC using a synonym.

When I try to replicate the table using Qlik Attunity I was not able to do so as the replication GUI couldn't find the table.

Please let me know if there is a workaround to bring in different tables from another source?

Re: Possible to bring in another table from a different Oracle source into the same target DB?

KellyHobson — Fri, 24 Jun 2022 09:14:16 GMT

Hey @desmondchew

Replicate supports one source endpoint and one target endpoint connection per task.

For your original task, this is OracleABC -> MySQL_Target. Where OracleABC has connection string, user, password, configured for ABC.

Thus if you would like to replicate from another Oracle source DB (XYZ), you need to create a new source endpoint for XYZ and a new task which uses XYZ as source.

OracleXYZ -> MySQLTarget

When you set up the second endpoint connection and task, then you will be able to search for the schema and tables you are interested in.

Let us know if you have any additional questions.

Best,

Kelly

Re: Possible to bring in another table from a different Oracle source into the same target DB?

SwathiPulagam — Fri, 24 Jun 2022 11:53:03 GMT

Hi @desmondchew,

From the Replicate task, we can only load the tables from one database and there is no option to get the tables from other databases. The only option is to create a separate task.

To be more specific Replicate can only see tables and views and not the synonyms.

Thanks,

Swathi

Re: Possible to bring in another table from a different Oracle source into the same target DB?

Michael_Litz — Fri, 24 Jun 2022 17:06:33 GMT

Hi,

Just wanted to let you know "if" the table was in the same database you would be able to add a field from it to the original table by using a field level transformation (date enrichment) function called source lookup.

Here is a link to a community article that will help explain how this would work.

Transformation: Source Lookup - Oracle ROWID
https://community.qlik.com/t5/Knowledge/Transformation-Source-Lookup-Oracle-ROWID/ta-p/1805170

Re: Possible to bring in another table from a different Oracle source into the same target DB?

SwathiPulagam — Sun, 26 Jun 2022 14:37:20 GMT

@desmondchew As I said before, Replicate can't see synonyms so you can create a view from an existing synonym and create a replicate FULL LOAD only task to replicate the view.

NOTE: Views can be replicated only in the FULL LOAD task.

Re: Possible to bring in another table from a different Oracle source into the same target DB?

desmondchew — Mon, 27 Jun 2022 01:44:29 GMT

Hi,

I have tried creating a view on the synonym that points to another database via db link. Qlik GUI doesn't retrieve the view from "Table Selection"

Re: Possible to bring in another table from a different Oracle source into the same target DB?

SwathiPulagam — Mon, 27 Jun 2022 12:22:15 GMT

@desmondchew You can't see the views in the Full load + CDC task. Please create a Fullload-only task and try to view the objects again.