6 Replies Latest reply: Feb 28, 2011 8:25 AM by 780131 RSS

    LKM File to Oracle (SQlLDR) giving errors

    827650
      Hi,

      I am using ODI to load data from an ascii file ( pipe separated) to an Oracle Table. So I created a Interface with LKM as "File to Oracle (SQlLDR)" and IKM as "SQL Control Append". CKM is "CKM Oracle".

      I am using ODI Studio 11g with jdk 1.6 and am trying this on my windows XP machine. I also have sqlldr installed on my local machine. But still the interface fails with an error. The interface works if i use "LKM File to SQL" but i want to use SQLLDR because i will have high volumes coming in.

      Can you please help.

      Interface Error on Step Call sqlldr :

      org.apache.bsf.BSFException: exception from Jython:
      Traceback (innermost last):
      File "<string>", line 34, in ?
      Load Error: See K:\Project Galaxy\Tech Team\ODIDataFiles/NIN_GL_BALANCES.log for details

      at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
      at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:345)
      at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:169)
      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2374)
      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java:1615)
      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java:1580)
      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java:2755)
      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2515)
      at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:534)
      at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:449)
      at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1954)
      at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1818)
      at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$2.doAction(StartScenRequestProcessor.java:559)
      at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:224)
      at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor.doProcessStartScenTask(StartScenRequestProcessor.java:481)
      at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$StartScenTask.doExecute(StartScenRequestProcessor.java:1040)
      at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:114)
      at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
      at java.lang.Thread.run(Thread.java:619)


      Jython Code :

      import java.lang.String
      import java.lang.Runtime as Runtime
      from jarray import array
      import java.io.File
      import os
      import re
      import javaos



      def reportnbrows():

      f = open(r"K:\Project Galaxy\Tech Team\ODIDataFiles/NIN_GL_BALANCES.log";, 'r')
      try:
      for line in f.readlines():
      if line.find("MAXIMUM ERROR COUNT EXCEEDED")>=0 :
      raise line
      finally:
      f.close()



      ctlfile = r"""K:\Project Galaxy\Tech Team\ODIDataFiles/NIN_GL_BALANCES.ctl";""
      logfile = r"""K:\Project Galaxy\Tech Team\ODIDataFiles/NIN_GL_BALANCES.log";""
      outfile = r"""K:\Project Galaxy\Tech Team\ODIDataFiles/NIN_GL_BALANCES.out";""
      oracle_sid=''
      if len('')>0: oracle_sid = '@'+''

      loadcmd = r"""sqlldr apps/<@=snpRef.getInfo("DEST_PASS") @>%s control="%s" log="%s" > "%s" """ % (oracle_sid,ctlfile, logfile, outfile)

      rc = os.system(loadcmd)

      if rc <> 0 and rc <> 2:
      raise "Load Error", "See %s for details" % logfile

      if rc==2:
      reportnbrows()