2 Replies Latest reply on Oct 2, 2013 11:17 AM by DecaXD

    (Procedure) fails on the source ORACLE connection ODI_STAGE. Caused By: java.sql.SQLException: Missing IN or OUT parameter at index:: 1

    c42fedbd-f2b3-45e5-9705-43ac64f15c30

      hi,

       

      trying to execute ODI procedure having two steps:

      1.odisqlunload

       

      OdiSqlUnload "-FILE=C:\oracle\CI_TXN.txt" "-DRIVER=oracle.jdbc.OracleDriver" "-URL=jdbc:oracle:thin:@localhost:1521:MARSHYOG" "-USER=RJNEW" "-PASS=aIyaWmnJsp72aBEQnyDf" "-FILE_FORMAT=VARIABLE" "-FIELD_SEP=," "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"

      select TXN_DETAIL_ID,TXN_HEADER_ID,TXN_SOURCE_CD,TXN_REC_TYPE_CD,EXT_TXN_NBR,CIS_DIVISION,ACCT_ID,UDF_AMT_1 as AMOUNT,UDF_CURRENCY_CD_1 as CURRENCY

      from RJNEW.CI_TXN_DETAIL where BO_STATUS_CD='COMP' and TXN_HEADER_ID='10'

       

      2.jython code

       

      import string

      import java.sql as sql

      import java.lang as lang

      import re

      sourceConnection = odiRef.getJDBCConnection("SRC")

      output_write=open('C:/oracle/CI_TXN.txt','r+')

      myStmt = sourceConnection.createStatement()

      my_query = "select TXN_DETAIL_ID,TXN_HEADER_ID,TXN_SOURCE_CD,TXN_REC_TYPE_CD,EXT_TXN_NBR,CIS_DIVISION,ACCT_ID,UDF_AMT_1 as AMOUNT,UDF_CURRENCY_CD_1 as CURRENCY from RJNEW.CI_TXN_DETAIL where BO_STATUS_CD='COMP' and TXN_HEADER_ID='10'"

      my_query=my_query.upper()

      if string.find(my_query, '*') > 0:

          myRs = myStmt.executeQuery(my_query)

          md=myRs.getMetaData()

          collect=[]

          i=1

          while (i <= md.getColumnCount()):

              collect.append(md.getColumnName(i))

          i += 1

       

          header=','.join(map(string.strip, collect))

      elif string.find(my_query,'||') > 0:

          header = my_query[7:string.find(my_query, 'FROM')].replace("||','||",',')

      else:

          header = my_query[7:string.find(my_query, 'FROM')]

       

      print header

      old=output_write.read()

      output_write.seek(0)

      output_write.write (header+'\n'+old)

       

      sourceConnection.close()

      output_write.close()

       

       

      proc executing 1st step but got stuck in 2nd step error saying

      (Procedure) fails on the source ORACLE connection ODI_STAGE.

      Caused By: java.sql.SQLException: Missing IN or OUT parameter at index:: 1

          at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1890)

       

      Thanks,

      yogesh