Hi All,
would you please help me understand how ODI 12c works. I have pretty simple case to implement - move data from one table to another between two Oracle 12c database.
Source database has two schemas:
1. P_OPERATOR - owner of the source table - this schema is set up as "Schema" in ODI Physical Schema Topology
2. P_ODI - custom schema for ODI, it has select grant to the source table - it is set up as "Work Schema" in ODI Physical Schema Topology
Target database which I own has two another schemas:
1. P_STAGING - staging area for data warehouse - it is set up as "Work Schema" in ODI Physical Schema Topology
2. P_MAIN - main data warehouse area where target table is located - this schema is set up as "Schema" in ODI Physical Schema Topology
The goal is to do merge on target table with data from source table.
I have DB Link defined from target database to source database P_ODI schema.
The table I want to move has one column being SDO_GEOMETRY type so I cannot do Merge via dblink but can do it via temporary table in the staging area.
What I want to do is to move data from source table into staging table in my P_STAGING schema and then Merge data from both tables.
I started with the simplest LKM which is LKM SQL to Oracle (Built-in).GLOBAL and IKM Oracle Merge.GLOBAL.
The problem I face is that no matter what I am doing ODI always creates staging table in source database instead of target database.
I selected option "Staging Location Hint" but it did not help.
I tried also "Preset Staging Location" and it works but ODI creates two staging tables in my staging area which is redundant.