10 Replies Latest reply: Apr 3, 2012 4:28 AM by 926353 RSS

    Synchronizing changes between sql developer and ODI

      Hello guys,

      Just now in SQL developer I had changed a constraint of one of my target tables and committed. The question is how to get this change to my target table in ODI wich is already fully mapped in an interface?

      I reverse engineered this table again in my models. But the constraint doesn't show. And I can't delete this table because it's used in the interface.

      Change data capture isn't for this purpose I guess. Is there an 'easier' option I'm missing here?

      thanks a lot !
        • 1. Re: Synchronizing changes between sql developer and ODI
          Bhabani Ranjan
          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.
          Save it.

          • 2. Re: Synchronizing changes between sql developer and ODI
            Hi Bhabani,

            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.

            • 3. Re: Synchronizing changes between sql developer and ODI
              Bhabani Ranjan
              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.
              • 4. Re: Synchronizing changes between sql developer and ODI
                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...

                • 5. Re: Synchronizing changes between sql developer and ODI
                  Sutirtha Roy

                  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 ?

                  • 6. Re: Synchronizing changes between sql developer and ODI
                    Hi Roy,

                    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).

                         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:457)
                         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
                         at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:889)
                         at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:476)
                         at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:204)
                         at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:540)
                         at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
                         at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1079)
                         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1466)
                         at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3752)
                         at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3937)
                         at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1535)
                         at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:163)
                         at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:102)
                         at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)
                         at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
                         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2906)
                         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2609)
                         at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:537)
                         at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:453)
                         at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1740)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:338)
                         at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:214)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:272)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:263)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:822)
                         at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:123)
                         at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
                         at java.lang.Thread.run(Thread.java:619)

                    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.

                    • 7. Re: Synchronizing changes between sql developer and ODI
                      Hey Roy,

                      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

                      • 8. Re: Synchronizing changes between sql developer and ODI
                        try using the RKM Oracle to reverse .
                        • 9. Re: Synchronizing changes between sql developer and ODI
                          thanks for the answer but no luck.
                          • 10. Re: Synchronizing changes between sql developer and ODI
                            Hi guys,

                            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 :-)