Both Portal and Datamaker publish Oracle errors with unique indices having functions

Document ID : KB000009108
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

While starting work on a new schema (NEW_OWNER), a very strange publishing error occurred.  It turns out it is being caused by rules (case and functions) clause in the declaration of Unique index.  

Tested a single table 'DOG' and added a log. This table has an index on zero. We do not know why this construct is being used, but the Datamaker log indicates it is a functional index.

 
CREATE TABLE dog ( 
dog_id number (10, 0) NOT NULL, 
...
catcher_id number (12, 0) NOT NULL, 
...; 


ALTER TABLE dog 
ADD CONSTRAINT dog_pk PRIMARY KEY ( 
dog_id , 
catcher_id ); 


CREATE INDEX dog_rpad_pan_fbi ON dog ( 
"0" );

 

Log files indicate many duplicates.

0 generated duplicate row(s) removed for table PC_DOG_FIN_ADMINTYPE

And Errors

c.c.t.p.l.TDMPublishLog:  ERROR: Publish failed for job 390, Inconsistent table definitions.  Please check the log file.

c.c.t.p.l.TDMPublishLog:  End date and time: 2017/12/27 12:54:21

c.c.t.p.m.TDMPublishJobStart:  doPublish error, jobId=397, Exception: Inconsistent table definitions.  Please check the log file.

 

c.c.t.p.l.TDMPublishLog:  

c.c.t.p.l.TDMPublishLog:  ERROR: Publish failed for job 398, Table MY_SECURE_VMAIL does not contain column LOWER("ADDRESS")

c.c.t.p.l.TDMPublishLog:  End date and time: 2017/12/27 13:03:53

 

Why am I getting duplicates and how can I correct them?

Environment:
TDM 4.3- Datamaker & Portal
Cause:

No code fix needed.  This is accounted for already.

Resolution:

The functionality is already in place in 4.3 patches and is in the TDM 4.4 GA. No code change is needed for this.

 

The failure is caused by the TDM usage of indexes as column names. However, if we select ignore, then we do not hit the problem.

Ignore Duplicates.png