4 Replies Latest reply: Aug 31, 2009 6:40 AM by CezarSantos-http://odiexperts.com RSS

    Scenario errors out

    718594
      Hi,

      I have a procedure, which when dragged into a package works fine, but when a scenarion generated from the same procedure is dragged into the package, the package errors out.

      The error is:

      ava.lang.Exception: The scenario did not end properly.
      *     at com.sunopsis.dwg.dbobj.SnpScen.a(SnpScen.java)*
      *     at com.sunopsis.dwg.dbobj.SnpScen.localExecuteSync(SnpScen.java)*
      *     at com.sunopsis.dwg.tools.StartScen.actionExecute(StartScen.java)*
      *     at com.sunopsis.dwg.function.SnpsFunctionBaseRepositoryConnected.execute(SnpsFunctionBaseRepositoryConnected.java)*
      *     at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execIntegratedFunction(SnpSessTaskSql.java)*
      *     at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)*
      *     at com.sunopsis.dwg.dbobj.SnpSessTaskSqlS.treatTaskTrt(SnpSessTaskSqlS.java)*
      *     at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)*
      *     at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)*
      *     at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)*
      *     at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)*
      *     at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)*
      *     at com.sunopsis.dwg.cmd.e.i(e.java)*
      *     at com.sunopsis.dwg.cmd.h.y(h.java)*
      *     at com.sunopsis.dwg.cmd.e.run(e.java)*
      *     at java.lang.Thread.run(Unknown Source)*


      Thanks,
      Is

      Edited by: Is916 on Aug 28, 2009 4:20 AM
        • 1. Re: Scenario errors out
          CezarSantos-http://odiexperts.com
          Hi Is,

          At your operator, change to Hieraquical view, open the session that is launched and then open the "Child Sessions" options. You will see the procedure under there and the error that is happening...

          Could you do that?



          Cezar Santos
          [www.odiexperts.com|www.odiexperts.com]
          • 2. Re: Scenario errors out
            718594
            Hi,

            Error is
            923 : 42000 : java.sql.SQLException: ORA-00923: FROM keyword not found where expected

            Query rendered
            Select Exception getPrevStepLog("INSERT_COUNT") : No previous Step as rec from dual

            but in the previous step 1k records are insetred

            Query used in the procedure(Source) is
            Select <%=odiRef.getPrevStepLog("INSERT_COUNT")%> as rec from dual

            the same query works when the procedure is used instead of the generated scenario(procedure)

            Thanks,
            Is
            • 3. Re: Scenario errors out
              717842
              Hi,

              There is maybe a problem with your ODI getPrevStepLog Substitution Method :

              On metalink, read this DocId : 744117.1

              Note : This Subtition does not exists on Sunopsis V3-V4.

              ---------------------
              Part of DocId :

              1. First create an Integration Interface that loads data from source to target.

              2. Create a Specific Procedure, for example "AUDIT_COUNT" and insert a command "INSERT_UPDATE_COUNT" and include the following usig the expression editor:
              INSERT INTO AUDIT_DT
              (STEP_NAME,INSERT_COUNT,UPDATE_COUNT,ERROR_COUNT)
              VALUES
              ('%=odiRef.getPrevStepLog("STEP_NAME")%>',<%=odiRef.getPrevStepLog("INSERT_COUNT")%>,<%=odiRef.getPrevStepLog("UPDATE_COUNT")%>,<%=odiRef.getPrevStepLog("ERROR_COUNT")%>)

              3. Create an ODI Package, for example "LOAD_PKG", and include the Integration Interface followed by a call to the Specific Procedure as shown below:

              Edited by: user967629 on 31 août 2009 04:54
              • 4. Re: Scenario errors out
                CezarSantos-http://odiexperts.com
                Hi Is,

                that is a "Session" problem.

                Once each scenario is a new ODI session, the internal API getPrevStepLog is re-initialized when a new scenario starts.

                To get the values, you need to "ask" in the same session then a scenario can't be used. You must use the procedure instead scenario in this case.


                Make any sense?