This content has been marked as final. Show 10 replies
Create a constraint in your table inside ODI. Go to datastore expand it. double click on constraints. then go to column. add the required column from left pane to right pane.
but how does the synchronisation happen then towards my database because when I made a new constraint in ODI it doesn't show up in sql developer?
Because there was a unique constraint of multiple columns on my target table that gave errors, I dropped it, and created a new adjusted one. I need to get that one in my target table and making a new one like that in ODI doesn't seem to me like a good solution. Isn't there anyway besides starting all over in ODI to get the new constraint made in sql developer into ODI?
or do I have to delete the table from my models and delete the interface, reverse engineer again en start from scratch over again?
Also making a unique constraint in ODI doesn't seem possible. You can only make a pk, fk reference and a condition.
Why dont you mark required column as keys in your target and remove the old one or create a primary key in ODI level with all the unique keys.1 person found this helpful
yea I just thought there was a better way of sql developer communicating towards ODI.
Also I deleted the unique constraint in sql developer and after reverse engineering the table again in my models, the constraint there is gone too. But when using this adjusted table as
a target in my interface, it still gives me the error: ORA-00001: Schending van UNIQUE-beperking (TIME_UK).
while the time_uk constraint has been dropped and also is gone from my datastory table. I don't understand and it's quite frustrating...
Curious to know if you are getting this UK violation error even though there is no such constraint/index created at the backend or in ODI . Can you see the exat code which is giving this error ?
this is the full error if that's what you mean?
ODI-1217: Session webworx_naar_irma_v2 (129001) fails with return code 1.
ODI-1226: Step webworx_naar_irma_v2 fails after 1 attempt(s).
ODI-1240: Flow webworx_naar_irma_v2 fails while performing a Integration operation. This flow loads target table TIMESHEETS.
ODI-1228: Task webworx_naar_irma_v2 (Integration) fails on the target ORACLE connection irma.
Caused By: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: Schending van UNIQUE-beperking (TIME_UK).
So the steps I made were the following:
1. Drop the constraint in sql developer
2. Create new adjusted constraint in sql developer and committed
3. Re-reverse engineered the the timesheets table
4. Re-dragged the table to target in the interface (this worked when I added a new column in sql developer, the mappings remained and the new column was added)
5. Gave the above error so deleted the table from the interface
6. Made a new interface just to start clean and did all the mapping again
7.Still got the same error.
--> so I think the only option is to delete the interface where timesheets is ever been used because even when the table is deleted from the interface, it still seems to reference(used in) so I can't delete the datastore without deleting the interface is seems. Then do a new fresh reverse engineer of the table and start over again. It is a hassle but I'm out of other options it seems.
So I deleted the interface, the datastore en redid everything but still I get the error while the constraint doesn't exist anymore. I'm running out of options, do you have a suggestion?
When I add a test column to my table in sql developer and I reverse engineer it in ODI, the new column appears so no problem there, but with constraints it's really acting funny.
Thanks a lot
try using the RKM Oracle to reverse .1 person found this helpful
thanks for the answer but no luck.
So the error was that normally when you drop a constraint, the index should be dropped too automatically. But this wasn't the case so the constraint kept persisting. So I dropped the index and it worked. Thanks for the help I'm still a noob at this, but I am learning :-)