4 Replies Latest reply: Oct 13, 2012 11:53 PM by cnu143 RSS

    while doing scd2 with ikm oracle slowly changing get this error plz help me

    cnu143
      ODI-1217: Session INT_EMP1_SCD2 (574001) fails with return code 936.
      ODI-1226: Step INT_EMP1_SCD2 fails after 1 attempt(s).
      ODI-1240: Flow INT_EMP1_SCD2 fails while performing a Integration operation. This flow loads target table ODI_EMP1_SCD2_TGT.
      ODI-1228: Task INT_EMP1_SCD2 (Integration) fails on the target ORACLE connection DS_DEV_TGT.
      Caused By: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
           at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
           at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
           at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
           at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
           at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
           at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1115)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)
           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
           at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3954)
           at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1539)
           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:2913)
           at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
           at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
           at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
           at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
           at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
           at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
           at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
           at java.lang.Thread.run(Thread.java:722)
        • 1. Re: while doing scd2 with ikm oracle slowly changing get this error plz help me
          mRainey
          Often this "missing expression" error occurs when an Update Key is not selected for the Target Datastore in the Interface. But, with the SCD Knowledge Module, this could also be related to the SCD Behavior Type not being set on the Datastore columns (in the Model).

          To resolve the Update Key issue, add a Primary Key or Alternate Key constraint to your target Datastore. Then, in the Interface, select the target Datastore and in the property inspector choose the constraint as your Update Key.

          To resolve the SCD Behavior Type issue, you must determine the behavior (surrogate key, business key, starting timestamp, insert on change, etc.) of each column in the target Datastore and set it appropriately. This property is set in the Datastore column, so you will need to open each column and edit the behavior type.

          Hopefully one of these resolves your issue. If not, it may be worth posting the code for the insert into flow (I$) table step.

          Regards,
          Michael Rainey
          • 2. Re: while doing scd2 with ikm oracle slowly changing get this error plz help me
            cnu143
            thank you Rainey?

            iam not understand this case, plz give me a brief explanation?

            *"To resolve the Update Key issue, add a Primary Key or Alternate Key constraint to your target Datastore. Then, in the Interface, select the target Datastore and in the property inspector choose the constraint as your Update Key."*



            Regards..!
            Cnu...
            • 3. Re: while doing scd2 with ikm oracle slowly changing get this error plz help me
              mRainey
              Hi Cnu

              Let me work through an example. A table named EMPLOYEE may have a natural key of EMPLOYEE_ID. When loading this table as a target in ODI, you will need to define the key that will be used by the KM for Updates and Deletes. This key will be defined on the Datastore as a constraint.

              If you expand the Model, expand the EMPLOYEE Datastore, you will find the Constraints section under the Datastore. Create a new constraint. In the case of an SCD integration type, you should have a surrogate key as well as the natural key. EMPLOYEE_ID, being the natural key for EMPLOYEE, would most likely need to be setup as an Alternate Key constraint. So, set the constraint type as such and add the column EMPLOYEE_ID to the list of key columns. Save the constraint.

              Open the Interface that loads the EMPLOYEE table, click on the Target Datastore (EMPLOYEE), and in the Property Inspector you should find a property named Update Key. This dropdown will list all of the available constraints from the Target Datastore. You will want to choose the constraint that the Interface will use to perform updates and deletes on in the Target (depending on your KM). In this case, the Alternate Key constraint setup based on the EMPLOYEE_ID should be chosen.

              A long answer, but I hope it explains it well for you.

              Regards,
              Michael Rainey
              • 4. Re: while doing scd2 with ikm oracle slowly changing get this error plz help me
                cnu143
                thank you Rainey...!


                Regards...!
                Cnu.....

                Edited by: 964805 on Oct 14, 2012 10:23 AM