1 2 Previous Next 15 Replies Latest reply on Feb 28, 2013 5:13 AM by Phanikanth

    IKM Oracle Incremetal Update(Merge) not handling Duplicate records

    Phanikanth
    Phanikanth Feb 26, 2013 3:39 AM
      Hi

      I am using IKM Oracle Incremetal Update(Merge) Km (source and target is Oracle DB) and LKM Oracle to Oracle(DBLink) and CKM Oracle KM and when duplicate records comming from source side, My interface went failed at IKM and throwing below errors at Merge Row step and error as below, some time first error some time second error comming for same interface.

      Errors:
      ORA-30926: unable to get a stable set of rows in the source tables

      (or)

      ORA-00001: unique constraint (constraint_name) violated

      How to handle those discarded records in ODI

      and some times below select query is not capturing error message from below error code

      Select Query:
      select SUBSTR('<%=odiRef.getPrevStepLog("MESSAGE")%>',1,250) from dual

      Error Code:

      ODI-1217: Session LOAD_ONE_SIEBEL_TO_WCDS_DS_INVENTORY_PKG (23440) fails with return code 1704.
      ODI-1226: Step PVV_ERR_MESSAGE fails after 1 attempt(s).
      ODI-1228: Task PVV_ERR_MESSAGE (Variable) fails on the target connection MODA_WORKREP.
      Caused By: java.sql.SQLSyntaxErrorException: ORA-01704: string literal too long

           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.executeForDescribe(T4CPreparedStatement.java:947)
           at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1283)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1441)
           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
           at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3823)
           at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1671)
           at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:189)
           at oracle.odi.runtime.agent.execution.sql.RefreshVariableExecutor.execute(RefreshVariableExecutor.java:54)
           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.SnpSessTaskSqlV.processTask(SnpSessTaskSqlV.java:410)
           at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
           at com.sunopsis.dwg.dbobj.SnpSessTaskSqlV.treatTask(SnpSessTaskSqlV.java:240)
           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 com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1889)
           at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$2.doAction(StartScenRequestProcessor.java:580)
           at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
           at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor.doProcessStartScenTask(StartScenRequestProcessor.java:513)
           at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$StartScenTask.doExecute(StartScenRequestProcessor.java:1066)
           at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
           at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$1.run(DefaultAgentTaskExecutor.java:49)
           at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:49)
           at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor.executeAgentTask(DefaultAgentTaskExecutor.java:41)
           at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor.doExecuteAgentTask(TaskExecutorAgentRequestProcessor.java:92)
           at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor.process(TaskExecutorAgentRequestProcessor.java:83)
           at oracle.odi.runtime.agent.RuntimeAgent.startScenario(RuntimeAgent.java:803)
           at oracle.odi.runtime.agent.InternalRuntimeAgent.startScenario(InternalRuntimeAgent.java:52)
           at com.sunopsis.dwg.tools.StartScen.startScenOnLocalAgent(StartScen.java:1011)
           at com.sunopsis.dwg.tools.StartScen.actionExecute(StartScen.java:243)
           at com.sunopsis.dwg.function.SnpsFunctionBaseRepositoryConnected.execute(SnpsFunctionBaseRepositoryConnected.java:162)
           at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execIntegratedFunction(SnpSessTaskSql.java:3437)
           at com.sunopsis.dwg.dbobj.SnpSessTaskSql.executeOdiCommand(SnpSessTaskSql.java:1509)
           at oracle.odi.runtime.agent.execution.cmd.OdiCommandExecutor.execute(OdiCommandExecutor.java:44)
           at oracle.odi.runtime.agent.execution.cmd.OdiCommandExecutor.execute(OdiCommandExecutor.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 com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1889)
           at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$2.doAction(StartScenRequestProcessor.java:580)
           at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
           at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor.doProcessStartScenTask(StartScenRequestProcessor.java:513)
           at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$StartScenTask.doExecute(StartScenRequestProcessor.java:1066)
           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:662)



      If I user IKM SQL Incremental Append KM, it will resolve my problem.

      Please help me, it is urgent

      Regards,
      Phanikanth

      Edited by: Phanikanth on Feb 25, 2013 7:48 AM

      Edited by: Phanikanth on Feb 25, 2013 7:39 PM
      • 2544 Views
      • Tags:
        • 1. Re: IKM Oracle Incremetal Update(Merge) not handling Duplicate records
          actdi
          actdi Feb 26, 2013 4:22 AM (in response to Phanikanth)
          Phani,

          1.) You will have to paste the sql command raising the error. The data that is being used in the "using" clause contains duplicates. Hence the "not stable rows" error message.

          2) Unfortunately, the CKM doesnt handle duplicates well. Either you can try to use the "Distinct" checkbox in the interface or update the CKM to delete only one instance of the duplicate record and put the second (or more) in the E$ table.

          3.) "<%=odiRef.getPrevStepLog("MESSAGE")%>" probably is returning more than 4000 bytes and hence you get the Oracle length error while using SUBSTR (there is a limit to what substr can handle)

          Hope that helps
          • 2. Re: IKM Oracle Incremetal Update(Merge) not handling Duplicate records
            Phanikanth
            Phanikanth Feb 27, 2013 5:47 AM (in response to actdi)
            Hi

            Thanks for the reply

            I have tried with out substr function on "<%=odiRef.getPrevStepLog("MESSAGE")%>" and still I am facing same issue.
            Please help me to resolve the issue

            Regards,
            Phanikanth
            • 3. Re: IKM Oracle Incremetal Update(Merge) not handling Duplicate records
              Bhabani Ranjan Mahapatra
              Bhabani Ranjan Mahapatra Feb 27, 2013 7:49 AM (in response to Phanikanth)
              This wont work as there is certain limitation with substr in oracle. You have to use multiline string.
              You can achive this using jython. Store <%=odiRef.getPrevStepLog("MESSAGE")%> into multiline string and substr it.
              Then insert it using jdbc into the audit table.
              • 4. Re: IKM Oracle Incremetal Update(Merge) not handling Duplicate records
                Phanikanth
                Phanikanth Feb 27, 2013 11:43 AM (in response to Bhabani Ranjan Mahapatra)
                Thanks bhabani,

                Can you please provide the steps

                Regards,
                Phanikanth
                • 5. Re: IKM Oracle Incremetal Update(Merge) not handling Duplicate records
                  Bhabani Ranjan Mahapatra
                  Bhabani Ranjan Mahapatra Feb 27, 2013 12:30 PM (in response to Phanikanth)
                  command on source: oracle and its logical schema
                  command on target jython:

                  import java.sql as sql
                  import java.lang as lang

                  text = """
                  <%=odiRef.getPrevStepLog("MESSAGE")%>
                  """
                  textsubstr=text[:100]

                  sql_text="insert into t6_audit_table values('"+textsubstr+"')"

                  try:
                  # Source Connection
                  ConSrc = snpRef.getJDBCConnection( "SRC" )


                  stmtTrg = ConSrc.createStatement()
                  readTrg = stmtTrg.executeUpdate(sql_text)
                  finally:
                  stmtTrg.close()
                  • 6. Re: IKM Oracle Incremetal Update(Merge) not handling Duplicate records
                    Phanikanth
                    Phanikanth Feb 27, 2013 12:35 PM (in response to Bhabani Ranjan Mahapatra)
                    Thanks bhabani,

                    I am trying below code, please correct me if Iam wrong

                    I have created ODI procedure, in command on source >Technology as oracle and selected Logical schema

                    command:
                    <% String vErr = odiRef.getPrevStepLog("MESSAGE"); %>
                    begin
                    insert into ODI_JOB_EXECUTION_DETAILS values(
                    '<%=snpRef.getSession("SESS_NO")%>',
                    '#PVV_GET_SCEN_NAME',
                    '<%=odiRef.getContext( "CTX_NAME" )% >',
                    '<%=vErr%>',
                    '<%=odiRef.getPrevStepLog("INSERT_COUNT")%>',
                    '<%=odiRef.getPrevStepLog("ERROR_COUNT")%>'
                    );

                    end;


                    Table structure:

                    create table ODI_JOB_EXECUTION_DETAILS (
                    SESSION_NO NUMBER(30) PRIMARY KEY,
                    SCENARIO_NAME VARCHAR2(100),
                    CONTEXT_NAME VARCHAR2(20),
                    ERR_MESSAGE VARCHAR2(4000),
                    INSERT_COUNT NUMBER(10),
                    ERROR_COUNT NUMBER(10)
                    );



                    Regards,
                    Phanikanth

                    Edited by: Phanikanth on Feb 27, 2013 4:34 AM

                    Edited by: Phanikanth on Feb 27, 2013 4:35 AM
                    • 7. Re: IKM Oracle Incremetal Update(Merge) not handling Duplicate records
                      Bhabani Ranjan Mahapatra
                      Bhabani Ranjan Mahapatra Feb 27, 2013 12:42 PM (in response to Phanikanth)
                      I dont think it will work if the error message exceed 4000 characters. Please refer the jython code i posted earlier.
                      • 8. Re: IKM Oracle Incremetal Update(Merge) not handling Duplicate records
                        Phanikanth
                        Phanikanth Feb 27, 2013 1:09 PM (in response to Bhabani Ranjan Mahapatra)
                        Hi

                        I have changed as below, correct me if i am wrong and I have changed my table structure

                        * table structure:*

                        create table ODI_JOB_EXECUTION_DETAILS (
                        SESSION_NO VARCHAR2(30) PRIMARY KEY,
                        SCENARIO_NAME VARCHAR2(100),
                        CONTEXT_NAME VARCHAR2(20),
                        ERR_MESSAGE VARCHAR2(4000),
                        INSERT_COUNT VARCHAR2(10),
                        ERROR_COUNT VARCHAR2(10)
                        );


                        I have given below code on command on source (because the above structure created on source DB

                        Command on source:

                        technology as jython and STAGING is my logical schemaname for source Data server (which I given below)

                        import java.sql as sql
                        import java.lang as lang

                        text = """
                        <%=odiRef.getPrevStepLog("MESSAGE")%>
                        """
                        textsubstr=text[:100]
                        sessno="<%=snpRef.getSession("SESS_NO")%>"
                        ctx="<%=odiRef.getContext( "CTX_NAME" )% >"
                        ins_rec="<%=odiRef.getPrevStepLog("INSERT_COUNT")%>"
                        err_rec="<%=odiRef.getPrevStepLog("ERROR_COUNT")%>"

                        sql_text="insert intoODI_JOB_EXECUTION_DETAILS values("sessno","#PVV_GETSCEN_NAME","ctx","textsubstr"',"ins_rec","err_rec")"


                        ConSrc = snpRef.getJDBCConnection( "SRC" )
                        stmtTrg = ConSrc.createStatement()
                        readTrg = stmtTrg.executeUpdate(sql_text)

                        stmtTrg.close()
                        Command on Target:

                        technology as oracle apart from that, not given any thing

                        Regards,
                        Phanikanth

                        Edited by: Phanikanth on Feb 27, 2013 5:08 AM
                        • 9. Re: IKM Oracle Incremetal Update(Merge) not handling Duplicate records
                          Bhabani Ranjan Mahapatra
                          Bhabani Ranjan Mahapatra Feb 27, 2013 1:11 PM (in response to Phanikanth)
                          import java.sql as sql
                          import java.lang as lang

                          text = """
                          <%=odiRef.getPrevStepLog("MESSAGE")%>
                          """
                          textsubstr=text[:10]

                          sql_text="insert into ODI_JOB_EXECUTION_DETAILS values(<%=snpRef.getSession("SESS_NO")%>,'#CURRDATE','<%=odiRef.getContext( "CTX_NAME" )%>','"+textsubstr+"',<%=odiRef.getPrevStepLog("INSERT_COUNT")%>,<%=odiRef.getPrevStepLog("ERROR_COUNT")%>)"

                          try:
                          # Source Connection
                          ConSrc = snpRef.getJDBCConnection( "SRC" )


                          stmtTrg = ConSrc.createStatement()
                          readTrg = stmtTrg.executeUpdate(sql_text)
                          finally:
                          stmtTrg.close()


                          there is + around textsubstr like + textsubstr +
                          tk care of indent
                          • 10. Re: IKM Oracle Incremetal Update(Merge) not handling Duplicate records
                            Phanikanth
                            Phanikanth Feb 27, 2013 1:17 PM (in response to Bhabani Ranjan Mahapatra)
                            Hi

                            I have used the same but no luck

                            Error message:

                            org.apache.bsf.BSFException: exception from Jython:
                            SyntaxError: ("mismatched input 'textsubstr' expecting NEWLINE", ('<string>', 43, 93, 'sql_text="insert into ODI_JOB_EXECUTION_DETAILS values(2762360,\'#PVV_GETSCEN_NAME\',\'WCDS1\',\'"textsubstr"\',0,0)"\n'))

                                 at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
                                 at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
                                 at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
                                 at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2473)
                                 at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:48)
                                 at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.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:561)
                                 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:83)
                                 at java.lang.Thread.run(Thread.java:662)
                            Caused by: SyntaxError: ("mismatched input 'textsubstr' expecting NEWLINE", ('<string>', 43, 93, 'sql_text="insert into ODI_JOB_EXECUTION_DETAILS values(2762360,\'#PVV_GETSCEN_NAME\',\'WCDS1\',\'"textsubstr"\',0,0)"\n'))

                                 at org.python.core.PyException.fillInStackTrace(PyException.java:70)
                                 at java.lang.Throwable.<init>(Throwable.java:181)
                                 at java.lang.Exception.<init>(Exception.java:29)
                                 at java.lang.RuntimeException.<init>(RuntimeException.java:32)
                                 at org.python.core.PyException.<init>(PyException.java:46)
                                 at org.python.core.PyException.<init>(PyException.java:43)
                                 at org.python.core.PyException.<init>(PyException.java:39)
                                 at org.python.core.PySyntaxError.<init>(PySyntaxError.java:22)
                                 at org.python.core.ParserFacade.fixParseError(ParserFacade.java:96)
                                 at org.python.core.ParserFacade.parse(ParserFacade.java:203)
                                 at org.python.core.Py.compile_flags(Py.java:1685)
                                 at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:172)
                                 at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
                                 at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
                                 at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
                                 at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)
                                 at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
                                 at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.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)
                                 ... 1 more


                            In Code Tab:

                            import java.sql as sql
                            import java.lang as lang

                            text = """
                            ODI-1226: Step Staging_DS_to_WCDS_DS_PORTALUSER_COMPANY_INF fails after 1 attempt(s).
                            ODI-1240: Flow Staging_DS_to_WCDS_DS_PORTALUSER_COMPANY_INF fails while performing a Control operation. This flow loads target table CDS_PORTALUSER_COMPANY.
                            ODI-1228: Task CDS_PORTALUSER_COMPANY (Control) fails on the target ORACLE connection WCDS_DS.
                            Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "SUB"."SOURCE_SYSTEM_ID": invalid identifier

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

                            """
                            textsubstr=text[:10]

                            sql_text="insert into ODI_JOB_EXECUTION_DETAILS values(2762360,'#PVV_GETSCEN_NAME','WCDS1','"textsubstr"',0,0)"

                            try:
                            # Source Connection
                            ConSrc = snpRef.getJDBCConnection( "SRC" )


                            stmtTrg = ConSrc.createStatement()
                            readTrg = stmtTrg.executeUpdate(sql_text)
                            finally:
                            stmtTrg.close()

                            It is very urgent, please help me


                            Regards,
                            Phanikanth
                            • 11. Re: IKM Oracle Incremetal Update(Merge) not handling Duplicate records
                              Bhabani Ranjan Mahapatra
                              Bhabani Ranjan Mahapatra Feb 27, 2013 1:37 PM (in response to Phanikanth)
                              its clearly visible that you have error with textsubstr. use + sysmbol around the variable. refer this

                              http://dwteam.in/wp-content/uploads/2013/02/jythonmultiline.jpg
                              • 12. Re: IKM Oracle Incremetal Update(Merge) not handling Duplicate records
                                Phanikanth
                                Phanikanth Feb 27, 2013 1:52 PM (in response to Bhabani Ranjan Mahapatra)
                                Hi

                                I am using below code

                                import java.sql as sql
                                import java.lang as lang

                                text = """
                                <%=odiRef.getPrevStepLog("MESSAGE")%>
                                """
                                textsubstr=text[:10]

                                sql_text="insert into ODI_JOB_EXECUTION_DETAILS values(
                                <%=snpRef.getSession("SESS_NO")%>,
                                '#PVV_GETSCEN_NAME',
                                <%=odiRef.getContext( "CTX_NAME" )%>,
                                '"+textsubstr+"',
                                <%=odiRef.getPrevStepLog("INSERT_COUNT")%>,
                                <%=odiRef.getPrevStepLog("ERROR_COUNT")%>)"

                                try:
                                # Source Connection
                                ConSrc = snpRef.getJDBCConnection( "SRC" )


                                stmtTrg = ConSrc.createStatement()
                                readTrg = stmtTrg.executeUpdate(sql_text)
                                finally:
                                stmtTrg.close()


                                but no luck, facing same error and below is my table structure

                                create table ODI_JOB_EXECUTION_DETAILS (
                                SESSION_NO VARCHAR2(30) PRIMARY KEY,
                                SCENARIO_NAME VARCHAR2(100),
                                CONTEXT_NAME VARCHAR2(20),
                                ERR_MESSAGE VARCHAR2(4000),
                                INSERT_COUNT VARCHAR2(10),
                                ERROR_COUNT VARCHAR2(10)
                                );

                                Please help me

                                Error MEssage:

                                org.apache.bsf.BSFException: exception from Jython:
                                SyntaxError: ('mismatched character \'\\n\' expecting \'"\'', ('<string>', 43, 55, 'sql_text="insert into ODI_JOB_EXECUTION_DETAILS values(\n'))

                                     at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
                                     at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
                                     at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
                                     at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2473)
                                     at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:48)
                                     at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.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:561)
                                     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:83)
                                     at java.lang.Thread.run(Thread.java:662)
                                Caused by: SyntaxError: ('mismatched character \'\\n\' expecting \'"\'', ('<string>', 43, 55, 'sql_text="insert into ODI_JOB_EXECUTION_DETAILS values(\n'))

                                     at org.python.core.PyException.fillInStackTrace(PyException.java:70)
                                     at java.lang.Throwable.<init>(Throwable.java:181)
                                     at java.lang.Exception.<init>(Exception.java:29)
                                     at java.lang.RuntimeException.<init>(RuntimeException.java:32)
                                     at org.python.core.PyException.<init>(PyException.java:46)
                                     at org.python.core.PyException.<init>(PyException.java:43)
                                     at org.python.core.PyException.<init>(PyException.java:39)
                                     at org.python.core.PySyntaxError.<init>(PySyntaxError.java:22)
                                     at org.python.core.ParserFacade.fixParseError(ParserFacade.java:96)
                                     at org.python.core.ParserFacade.parse(ParserFacade.java:203)
                                     at org.python.core.Py.compile_flags(Py.java:1685)
                                     at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:172)
                                     at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
                                     at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
                                     at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
                                     at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)
                                     at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
                                     at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.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)
                                     ... 1 more


                                Code Tab:

                                import java.sql as sql
                                import java.lang as lang

                                text = """
                                ODI-1226: Step Staging_DS_to_WCDS_DS_PORTALUSER_COMPANY_INF fails after 1 attempt(s).
                                ODI-1240: Flow Staging_DS_to_WCDS_DS_PORTALUSER_COMPANY_INF fails while performing a Control operation. This flow loads target table CDS_PORTALUSER_COMPANY.
                                ODI-1228: Task CDS_PORTALUSER_COMPANY (Control) fails on the target ORACLE connection WCDS_DS.
                                Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "SUB"."SOURCE_SYSTEM_ID": invalid identifier

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

                                """
                                textsubstr=text[:10]

                                sql_text="insert into ODI_JOB_EXECUTION_DETAILS values(
                                2770360,
                                '#PVV_GETSCEN_NAME',
                                WCDS1,
                                '"+textsubstr+"',
                                0,
                                0)"

                                try:
                                # Source Connection
                                ConSrc = snpRef.getJDBCConnection( "SRC" )


                                stmtTrg = ConSrc.createStatement()
                                readTrg = stmtTrg.executeUpdate(sql_text)
                                finally:
                                stmtTrg.close()


                                Regards,
                                Phanikanth
                                • 13. Re: IKM Oracle Incremetal Update(Merge) not handling Duplicate records
                                  Bhabani Ranjan Mahapatra
                                  Bhabani Ranjan Mahapatra Feb 27, 2013 2:12 PM (in response to Phanikanth)
                                  Probably this much i can help. Download the procedure from my blog. Select the logical schema from command on source to your logical schema
                                  http://dwteam.in/wp-content/uploads/2013/02/DWTEAM.IN_SUBSTRING.rar

                                  Still issues , come to gmail.

                                  Bhabani
                                  • 14. Re: IKM Oracle Incremetal Update(Merge) not handling Duplicate records
                                    Phanikanth
                                    Phanikanth Feb 27, 2013 2:38 PM (in response to Bhabani Ranjan Mahapatra)
                                    Hi

                                    No luck,

                                    Command on Target:

                                    import java.sql as sql
                                    import java.lang as lang

                                    text = """
                                    ODI-1226: Step Staging_DS_to_WCDS_DS_PORTALUSER_COMPANY_INF fails after 1 attempt(s).
                                    ODI-1240: Flow Staging_DS_to_WCDS_DS_PORTALUSER_COMPANY_INF fails while performing a Control operation. This flow loads target table CDS_PORTALUSER_COMPANY.
                                    ODI-1228: Task CDS_PORTALUSER_COMPANY (Control) fails on the target ORACLE connection WCDS_DS.
                                    Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "SUB"."SOURCE_SYSTEM_ID": invalid identifier
                                    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:662)

                                    """
                                    textsubstr=text[:10]

                                    sql_text="insert into ODI_JOB_EXECUTION_DETAILS values
                                    (
                                    <%=snpRef.getSession("SESS_NO")%>,
                                    '<%=odiRef.getPrevStepLog("STEP_NAME")%>',
                                    '<%=odiRef.getContext( "CTX_NAME" )%>',
                                    '"+textsubstr+"',
                                    <%=odiRef.getPrevStepLog("INSERT_COUNT")%>,
                                    <%=odiRef.getPrevStepLog("ERROR_COUNT")%>)"

                                    try:
                                    # Source Connection
                                    ConSrc = snpRef.getJDBCConnection( "SRC" )


                                    stmtTrg = ConSrc.createStatement()
                                    readTrg = stmtTrg.executeUpdate(sql_text)
                                    finally:
                                    stmtTrg.close()


                                    Code tab message:

                                    import java.sql as sql
                                    import java.lang as lang

                                    text = """
                                    ODI-1226: Step Staging_DS_to_WCDS_DS_PORTALUSER_COMPANY_INF fails after 1 attempt(s).
                                    ODI-1240: Flow Staging_DS_to_WCDS_DS_PORTALUSER_COMPANY_INF fails while performing a Control operation. This flow loads target table CDS_PORTALUSER_COMPANY.
                                    ODI-1228: Task CDS_PORTALUSER_COMPANY (Control) fails on the target ORACLE connection WCDS_DS.
                                    Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "SUB"."SOURCE_SYSTEM_ID": invalid identifier
                                    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:662)

                                    """
                                    textsubstr=text[:10]

                                    sql_text="insert into ODI_JOB_EXECUTION_DETAILS values
                                    (
                                    2773360,
                                    'Staging_DS_to_WCDS_DS_PORTALUSER_COMPANY_INF',
                                    'WCDS1',
                                    '"+textsubstr+"',
                                    0,
                                    0)"

                                    try:
                                    # Source Connection
                                    ConSrc = snpRef.getJDBCCone:*nection( "SRC" )


                                    stmtTrg = ConSrc.createStatement()
                                    readTrg = stmtTrg.executeUpdate(sql_text)
                                    finally:
                                    stmtTrg.close()


                                    Error Messag:

                                    org.apache.bsf.BSFException: exception from Jython:
                                    SyntaxError: ('mismatched character \'\\n\' expecting \'"\'', ('<string>', 42, 54, 'sql_text="insert into ODI_JOB_EXECUTION_DETAILS values\n'))

                                         at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
                                         at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
                                         at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
                                         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2473)
                                         at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:48)
                                         at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.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:561)
                                         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:83)
                                         at java.lang.Thread.run(Thread.java:662)
                                    Caused by: SyntaxError: ('mismatched character \'\\n\' expecting \'"\'', ('<string>', 42, 54, 'sql_text="insert into ODI_JOB_EXECUTION_DETAILS values\n'))

                                         at org.python.core.PyException.fillInStackTrace(PyException.java:70)
                                         at java.lang.Throwable.<init>(Throwable.java:181)
                                         at java.lang.Exception.<init>(Exception.java:29)
                                         at java.lang.RuntimeException.<init>(RuntimeException.java:32)
                                         at org.python.core.PyException.<init>(PyException.java:46)
                                         at org.python.core.PyException.<init>(PyException.java:43)
                                         at org.python.core.PyException.<init>(PyException.java:39)
                                         at org.python.core.PySyntaxError.<init>(PySyntaxError.java:22)
                                         at org.python.core.ParserFacade.fixParseError(ParserFacade.java:96)
                                         at org.python.core.ParserFacade.parse(ParserFacade.java:203)
                                         at org.python.core.Py.compile_flags(Py.java:1685)
                                         at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:172)
                                         at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
                                         at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
                                         at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
                                         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)
                                         at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
                                         at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.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)
                                         ... 1 more

                                    Regards,
                                    Phanikanth

                                    Edited by: Phanikanth on Feb 27, 2013 6:37 AM
                                    1 2 Previous Next