This discussion is archived
4 Replies Latest reply: Oct 13, 2012 9:53 PM by cnu143 RSS

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

cnu143 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    thank you Rainey...!


    Regards...!
    Cnu.....

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points