5 Replies Latest reply on May 22, 2013 1:42 PM by ryan.w

    ODI SQL LDR issue

    968552
      Hi Guys,
      I am trying to load one csv file using sqlldr LKM and facing issue at Call Sqlldr step

      Error Message is:

      org.apache.bsf.BSFException: exception from Jython:
      Traceback (most recent call last):
      File "<string>", line 22, in <module>
      Load Error: See Y:/BTLB_PLANS Product Codes ( A CODE LIST).log for details

           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: Traceback (most recent call last):
      File "<string>", line 22, in <module>
      Load Error: See Y:/BTLB_PLANS Product Codes ( A CODE LIST).log for details

           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.doRaise(PyException.java:219)
           at org.python.core.Py.makeException(Py.java:1166)
           at org.python.core.Py.makeException(Py.java:1170)
           at org.python.pycode._pyx0.f$0(<string>:59)
           at org.python.pycode._pyx0.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: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


      and Code:
      import java.lang.String
      import java.lang.Runtime as Runtime
      from jarray import array
      import java.io.File
      import os
      import re


      ctlfile = r"""Y:/BTLB_PLANS Product Codes ( A CODE LIST).ctl"""
      logfile = r"""Y:/BTLB_PLANS Product Codes ( A CODE LIST).log"""
      outfile = r"""Y:/BTLB_PLANS Product Codes ( A CODE LIST).out"""
      oracle_sid=''
      if len('UREPDEV')>0: oracle_sid = '@'+'UREPDEV'


      loadcmd = r"""sqlldr 'REDW_DA_OWNER/<@=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

      # Init Vars
      nbIns = 0
      nbRej = 0
      nbNull = 0
      strprt = ""
      maxAllowedError = r"""0"""
      c = 0
      flag = 0

      # Open log file
      f = open(logfile, "r")
      try:
           lines = f.readlines()
           for line in lines:
                if line.rstrip().upper().endswith(r"""REDW_DA_OWNER.TC$_0TEST:""".upper()):
                     flag = 1
                     c = 0
                if flag == 1:
                     if c > 0 and c <= 4:
                          if c == 1 :
                               nbIns = int(re.findall("\d+", line)[0])
                          elif c == 2:
                               nbRej = int(re.findall("\d+", line)[0])
                          elif c == 4:
                               nbNull = int(re.findall("\d+", line)[0])
                               break
                c+=1
           strprt = "\n\tIns:\t%s\n\tReject:\t%s\n\tNullField:\t%s" % (nbIns, nbRej, nbNull)
      finally:
           f.close()


      # if some rows has been rejected due to invalide data, check KM option LOA_ERRORS
      if rc == 2:
           if nbRej > int(maxAllowedError):
                raise strprt
                break

      I am using LKM File to Oracle(SQLLDR)

      Thanks in advance
        • 1. Re: ODI SQL LDR issue
          mRainey
          What does the error log say? Also, may be a good idea to remove spaces/special characters from the file name.

          Regards,
          Michael Rainey
          • 2. Re: ODI SQL LDR issue
            968552
            error is saying:

            org.apache.bsf.BSFException: exception from Jython:
            Traceback (most recent call last):
            File "<string>", line 22, in <module>
            Load Error: See Y:/<file_name>.log for details

            and no log file is created for that.

            Few more things to add.

            We have 2 different boxes. 1. ODI box (different server address)
            2. DB box (different server address)

            and Sql developer is installed on DB box. I am able to use SQLLoader utility through SQL Developer.

            But when I am using ODI to use sql ldr through LKM SQL to Oracle (Sqlldr).
            It is getting failed at 6th step i.e. Call sqlldr.
            Error is : org.apache.bsf.BSFException: exception from Jython:
            Traceback (most recent call last):
            File "<string>", line 22, in <module>
            Load Error: See<file_name>.log for details

            but to my surprise all files were created at the folder except .log file.

            Can anybody help me to solve this issue.

            This is very urgent.

            Is this because sqlldr is not accessible to ODI then how .ctl & .bad files are created.

            I will really appreciate your help. it is bit urgent to solve this issue.

            Edited by: 965549 on Oct 28, 2012 10:36 AM
            • 3. Re: ODI SQL LDR issue
              mRainey
              The user that is running the agent, which is executing the Interface and calling the SQL Loader utility, will need access to do SQL Loader. Either provide access if the agent is on a remote machine or install the agent on the source (where SQL Loader is installed) and execute the Interface using this agent.

              Regards,
              Michael Rainey
              • 4. Re: ODI SQL LDR issue
                NagarajY
                Check whether the sql loader client is installed and tnsnames.ora is pointing to the right database. Try to load the file using sql loader
                Try re-starting the agent .

                Regards,
                Yellanki
                • 5. Re: ODI SQL LDR issue
                  ryan.w
                  Is your agent being managed by OPMN? If so, make sure that the ORACLE_HOME and TNS_ADMIN environment variables are set in the OPMN XML configuration file. You may also need to append the PATH variable with $ORACLE_HOME/bin in the same configuration file.

                  --
                  Ryan