Hi everyone.
I have an issue using IKM Oracle Incremental Update (MERGE).
I have a table that has "internal" columns that we use for keeping track of who updated the table and when. Something like this:
create table MYSCHEMA.BLAH (
DW_UPDATED_BY VARCHAR2(50) NOT NULL,
DW_UPDATED_ON DATE NOT NULL,
BLAH_KEY NUMBER NOT NULL,
COLUMN1 VARCHAR2(n),
COLUMN2 VARCHAR2(n),
...
so on and so forth.
So, in my mapping where this table is the target, the first two columns are not updated by the source table, but rather by the expression within the mapping's field's property:
for DW_UPDATED_BY, I put in 'ODI'
for DW_UPDATED_ON, I put in SYSDATE
So far so good.
However, when I use IKM Oracle Incremental Update (MERGE) to incrementally merge data into this table, I get an error, such as this:
ODI-1228: Task Merge Rows-IKM Oracle Incremental Update (MERGE)- fails on the target connection ORA.
Caused By: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("MYSCHEMA"."BLAH"."DW_UPDATED_BY")
When I inspect the MERGE statement that ODI comes up with, it rightfully includes ALL columns of the table, including these DW_* internal columns and since they are not mapped from the source, they are coming in with NULL values and hence the error.
------------
merge into MYSCHEMA.BLAH T
using ODI_STAGE.I$_BLAH S
on (
T.BLAH_KEY=S.BLAH_KEY
)
when matched
then update set
T.DW_UPDATED_ON = S.DW_UPDATED_ON,
T.DW_UPDATED_BY = S.DW_UPDATED_BY,
T.COLUMN1 = S.COLUMN1,
T.COLUMN2 = S.COLUMN2
when not matched
then insert
(
T.DW_UPDATED_ON,
T.DW_UPDATED_BY,
T.COLUMN1,
T.COLUMN2
)
values
(
S.DW_UPDATED_ON,
S.DW_UPDATED_BY,
S.COLUMN1,
S.COLUMN2
)
------------
What I think I need to do is change the IKM Oracle Incremental Update (MERGE) module to exclude these DW_* columns from the merge.
Does anybody know which steps in the IKM module need to change and how to actually make this change?
Thank you
Boris