3 Replies Latest reply: Aug 26, 2014 1:40 AM by HimanshuP RSS

    Interface Execution issue

    HimanshuP

      Hi All,

       

      I am facing one issue during interface execution after ODI upgrade 10g(10.3.5) to ODI 11g(11.1.1.7.0)

      LKM SQL to SQL (JYTHON) in Load data (JYTHON) step.

      No value has been set for Parameter 5

       

      Error Description :

      org.apache.bsf.BSFException: exception from Jython:

      Traceback (most recent call last):

        File "<string>", line 120, in <module>

        at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:90)

        at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:65)

        at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:94)

        at com.teradata.jdbc.jdbc_3.ifjdbc_4.LocalStatementParameters.addBatchData(LocalStatementParameters.java:91)

        at com.teradata.jdbc.jdbc_3.ifjdbc_4.TeraLocalPreparedStatement.addBatch(TeraLocalPreparedStatement.java:297)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

        at java.lang.reflect.Method.invoke(Method.java:597)

      com.teradata.jdbc.jdbc_4.util.JDBCException: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 12.00.00.105] [Error 858] [SQLState 07004] No value has been set for parameter 5

       

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

        at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)

        at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)

        at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1931)

        at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$2.doAction(StartScenRequestProcessor.java:582)

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

        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: Traceback (most recent call last):

        File "<string>", line 120, in <module>

        at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:90)

        at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:65)

        at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:94)

        at com.teradata.jdbc.jdbc_3.ifjdbc_4.LocalStatementParameters.addBatchData(LocalStatementParameters.java:91)

        at com.teradata.jdbc.jdbc_3.ifjdbc_4.TeraLocalPreparedStatement.addBatch(TeraLocalPreparedStatement.java:297)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

        at java.lang.reflect.Method.invoke(Method.java:597)

      com.teradata.jdbc.jdbc_4.util.JDBCException: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 12.00.00.105] [Error 858] [SQLState 07004] No value has been set for parameter 5

        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.Py.JavaError(Py.java:455)

        at org.python.core.Py.JavaError(Py.java:448)

        at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:177)

        at org.python.core.PyObject.__call__(PyObject.java:355)

        at org.python.core.PyMethod.__call__(PyMethod.java:215)

        at org.python.core.PyMethod.instancemethod___call__(PyMethod.java:221)

        at org.python.core.PyMethod.__call__(PyMethod.java:206)

        at org.python.core.PyObject.__call__(PyObject.java:381)

        at org.python.core.PyObject.__call__(PyObject.java:385)

        at org.python.pycode._pyx6.f$0(<string>:135)

        at org.python.pycode._pyx6.call_function(<string>)

        at org.python.core.PyTableCode.call(PyTableCode.java:165)

        at org.python.core.PyCode.call(PyCode.java:18)

        at org.python.core.Py.runCode(Py.java:1204)

        at org.python.core.Py.exec(Py.java:1248)

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

        at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)

        at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)

        at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1930)

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

        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

      Caused by: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 12.00.00.105] [Error 858] [SQLState 07004] No value has been set for parameter 5

        at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:90)

        at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:65)

        at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:94)

        at com.teradata.jdbc.jdbc_3.ifjdbc_4.LocalStatementParameters.addBatchData(LocalStatementParameters.java:91)

        at com.teradata.jdbc.jdbc_3.ifjdbc_4.TeraLocalPreparedStatement.addBatch(TeraLocalPreparedStatement.java:297)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

        at java.lang.reflect.Method.invoke(Method.java:597)

        at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:175)

        ... 33 more

       

       

      Thanks

        • 1. Re: Interface Execution issue
          2722631

          Can you copy pest the generated code from operator? I hope you have not done any customization in the KM.

          • 2. Re: Interface Execution issue
            Nishikant-Oracle

            Hi

            'LKM SQL to SQL (JYTHON)' is an ODI supplied LKM. I think this must have got upgraded when you migrated the code from 10g to 11g.

            In the new LKM, there must be a new parameter which wasn't available in 10g, and hence is not defined

             

            Can you open this interface and click on the LKM properties, and check which of the mandatory parameter is not defined.

             

            Regards

            Nishikant

            • 3. Re: Interface Execution issue
              HimanshuP

              This is the code its generate; KM is customized.

               

              import java.sql as sql  

              import java.lang as lang 

              from time import clock as now 

                

              try:  

              #  Source Connection

              ConSrc = snpRef.getJDBCConnection( "SRC" )

                 

              #  Target (Destination) Connection

              ConTrg = snpRef.getJDBCConnection( "DEST" )  

                 

              # Statement target + metadata   

              stmtTrg = ConTrg.createStatement()   

              readTrg =  stmtTrg.executeQuery("select * from test.C$_0TBLSCHEMEHEADERS where 1=2")   

              metaDataTrg = readTrg.getMetaData()   

              colLst = ''   

              typeColTrg=''   

              bindVar =''   

              for i in range(metaDataTrg.getColumnCount()):   

                   colLst =  str(colLst) + str(metaDataTrg.getColumnName(i+1)) + ','   

                   typeColTrg = str(typeColTrg)+ str(metaDataTrg.getColumnType(i+1))+','   

              colLst=colLst[:-1]   

                 

              colLst= 'C1_SCHEMEHEADERID, C2_SCHEMEHEADER_NAME, C3_POLICYTYPEID, C4_BYORDER, C5_LARGELOSSTHRESHOLD, C6_SYSTEMID, C7_SPOOLERID, C8_OLDSCHEMEHEADERID'   

              nbCol=colLst.count(',')   

              bindVar=','.join('?'*(nbCol+1))   

              rqteInsert = 'insert into test.C$_0TBLSCHEMEHEADERS  ('+colLst+') values ('+bindVar+')'   

              psmt = ConTrg.prepareStatement(rqteInsert)   

                 

              #Test support BatchUpdates on target connexion 

              try: 

                       isBatch=ConTrg.getMetaData().supportsBatchUpdates() 

                        

              except: 

                       isBatch=0         

              batchSize=3000 

                

              # Statement source + metadata   

              readSrc = ConSrc.createStatement() 

              sqlSrc="""select  TBLSCHEMEHEADERS.SchemeHeaderID  as  C1_SCHEMEHEADERID,TBLSCHEMEHEADERS.Name  as  C2_SCHEMEHEADER_NAME,TBLSCHEMEHEADERS.PolicyTypeID  as  C3_POLICYTYPEID,TBLSCHEMEHEADERS.ByOrder  as  C4_BYORDER,TBLSCHEMEHEADERS.LargeLossThreshold  as  C5_LARGELOSSTHRESHOLD,TBLSCHEMEHEADERS.SystemID  as  C6_SYSTEMID,TBLSCHEMEHEADERS.SpoolerId  as  C7_SPOOLERID,TBLSCHEMEHEADERS.OldSchemeHeaderID  as  C8_OLDSCHEMEHEADERID   from MagentaStaging.dbo.tblSchemeHeaders as TBLSCHEMEHEADERS  where (1=1)       """ 

              readSrc.setFetchSize(3000) 

              rqteSrc = readSrc.executeQuery(sqlSrc)   

              metaDataSrc = rqteSrc.getMetaData()   

                  

              # Set statement   

              nb=None   

              lstPsmt='' 

              nbLine=0 

              psmt.clearBatch()   

              deb=now() 

              print "START OF THE BATCH BEGFORE WHILE"

               

               

              while rqteSrc.next():   

                resLine=''   

                for nb in range (i+1): 

                 colType=metaDataSrc.getColumnType(nb+1) 

                 if colType == sql.Types.VARCHAR: #Code 12   

                    resline=rqteSrc.getString(nb+1)   

                    psmt.setString(nb+1,resline)   

                 elif colType == sql.Types.CHAR: #Code 1   

                    resline=rqteSrc.getString(nb+1)   

                    psmt.setString(nb+1,resline)   

                 elif colType == sql.Types.INTEGER: #Code 4   

                    resline=rqteSrc.getBigDecimal(nb+1)   

                    psmt.setBigDecimal(nb+1,resline)      

                 elif colType == sql.Types.NUMERIC: #Code 2   

                    resline=rqteSrc.getBigDecimal(nb+1)   

                    psmt.setBigDecimal(nb+1,resline)   

                 elif colType == sql.Types.DECIMAL: #Code 3   

                    resline=rqteSrc.getBigDecimal(nb+1)   

                    psmt.setBigDecimal(nb+1,resline)   

                 elif colType == sql.Types.DATE: #Code 91   

                    resline=rqteSrc.getDate(nb+1)   

                    psmt.setDate(nb+1,resline)   

                 elif colType == sql.Types.TIME: #Code 92   

                    resline=rqteSrc.getTime(nb+1)   

                    psmt.setTime(nb+1,resline)   

                 elif colType == sql.Types.TIMESTAMP: #Code 93   

                    resline=rqteSrc.getTimestamp(nb+1)   

                    psmt.setTimestamp(nb+1,resline)   

                 elif colType == sql.Types.BIGINT:   #Code -5   

                    resline=rqteSrc.getBigDecimal(nb+1)   

                    psmt.setBigDecimal(nb+1,resline)      

                 elif colType == sql.Types.BINARY:  #Code -2   

                    resline=rqteSrc.getBytes(nb+1)   

                    psmt.setBytes(nb+1,resline)                 

                 elif colType == sql.Types.BIT: #Code -7   

                    resline=rqteSrc.getBigDecimal(nb+1)   

                    psmt.setBigDecimal(nb+1,resline)   

                 elif colType == sql.Types.DOUBLE: #Code 8   

                    resline=rqteSrc.getBigDecimal(nb+1)   

                    psmt.setBigDecimal(nb+1,resline)       

                 elif colType == sql.Types.LONGVARBINARY: #Code -4   

                    resline=rqteSrc.getBytes(nb+1)   

                    psmt.setBytes(nb+1,resline)   

                 elif colType == sql.Types.LONGVARCHAR:  #Code -1   

                    resline= rqteSrc.getString(nb+1)   

                    psmt.setString(nb+1,resline)   

                 elif colType == sql.Types.OTHER: #Code 1111   

                    resline=rqteSrc.getObject(nb+1)   

                    psmt.setObject(nb+1,resline)   

                 elif colType == sql.Types.REAL: #Code 7   

                    resline=rqteSrc.getFloat(nb+1)   

                    psmt.setFloat(nb+1,resline)   

                 elif colType == sql.Types.SMALLINT: #Code 5   

                    resline=rqteSrc.getBigDecimal(nb+1)   

                    psmt.setBigDecimal(nb+1,resline)      

                 elif colType == sql.Types.TINYINT: #Code -6   

                    resline=rqteSrc.getBigDecimal(nb+1)   

                    psmt.setBigDecimal(nb+1,resline)       

                 elif colType == sql.Types.VARBINARY: #Code -3   

                    resline=rqteSrc.getBytes(nb+1)   

                    psmt.setBytes(nb+1,resline)   

                  

                 resline = '' 

               

               

                #BatchUpdate is supported or execute direct Update 

                if (isBatch): 

                      psmt.addBatch()  

                      if batchSize==nbLine: 

                              psmt.executeBatch() 

                              nbLine=0 

                else: 

                      psmt.executeUpdate()          

                  

                nbLine=nbLine+1 

                       

              if nbLine>0: 

                        #psmt.executeUpdate()

                        psmt.executeBatch() 

                        #ConTrg.commit()

               

               

              finally:   

              print "OK IN FINALLY"

                

              #fin=now() 

              #duree=fin-deb 

              #print "Durée :", duree