6 Replies Latest reply: Jan 18, 2010 5:48 AM by mfinlay RSS

    LKM File to Oracle (SQLLDR) error in step "Call sqlldr"

    612686
      ODI version 10.1.3.5
      Agent runs on RedHat Linux

      We load data using "LKM File to Oracle (SQLLDR)" knowledge module. Step "Call sqlldr" ends with error: "OS command returned 2". Although this step fails, work table is filled with records. Some records were found as bad, but most of them valid. Since work table is filled, this excludes the case, which was previously mentioned in forum - problems with TNS or user password.

      In interface, in LKM settings, I have edited the following options:
      LOA_DISCARDMAX = 999 (this number was not exceeded)
      LOA_ERRORS = 999 (this number was not exceeded)

      Thnx in advance,

      Karlis
        • 1. Re: LKM File to Oracle (SQLLDR) error in step "Call sqlldr"
          Sutirtha Roy
          Check the following

          Each time interface fails on "Call sqlldr" loading step with the error message: OS command returned 2.
          .
          At the same time:
          - .ctl file is generated with the correct value for ERRORS parameter (taken from KM option specified in the interface)
          - data loaded from the source file to the work table (C$_ table), .err file contains incorrect records that were ignored
          .
          It seems that SQLLDR exits with exit code "2" if any errors occur during the loading phase, even if the number of errors is less than the value specified
          in ERRORS parameter in .ctl file (LOA_ERRORS)
          .
          So it's not possible to load data from source file which potentially could contain some records causing errors during SQLLDR execution, LOA_ERRORS is
          taken into account but OdiOSCommand fails since SQLLDR exit code isn't "0".

          If the above holds true for your case then you are hitting Bug 8560194

          What happens if you run the .ctl file from sqlldr ?
          • 2. Re: LKM File to Oracle (SQLLDR) error in step "Call sqlldr"
            612686
            You are 100% correct.
            If I run the same thing from command line (with CTL generated by ODI), it executes perfectly.
            • 3. Re: LKM File to Oracle (SQLLDR) error in step "Call sqlldr"
              Sutirtha Roy
              Ok ,

              Unfortunately Bug 8560194 is not fixed for ODI 10.1.3.5 version .
              • 4. Re: LKM File to Oracle (SQLLDR) error in step "Call sqlldr"
                612686
                Then I guess we need to change a file, which we intend to load, to have 100% loadable records for sql loader.
                Unfortunatelly I don't have access to bug 8560194 description in metalink - is there any other workarounds?
                Anyway, thanks.
                • 5. Re: LKM File to Oracle (SQLLDR) error in step "Call sqlldr"
                  mfinlay
                  We're hitting the exact same error in the same version of ODI, I can't believe a simple SQL Loader (Oracle's own product) like this doesn't work. We have a flat file with a couple of footer records which I was expecting we could let be discarded / error in SQL Loader, but no such luck.

                  I've raised an SR, but will try a work around with external tables.

                  Matt
                  • 6. Re: LKM File to Oracle (SQLLDR) error in step "Call sqlldr"
                    mfinlay
                    Hi there,

                    a suggested workaround is:

                    1. Duplicate your LKM
                    2. change the Name to "LKM File to Oracle (SQLLDR) CUST"
                    3. on the Description field enter the Information that this is a customized LKM
                    4. On the Details tab open the "Call sqlldr" command
                    5. On the Command on Target Tab change the technology to Jython and delete the whole code on the command field.
                    6. Use the following command for this field:
                    --------------------------------------------------------

                    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"<%=odiRef.getSrcTablesList("[WORK_SCHEMA]/[TABLE_NAME].log", "")%>", 'r')
                    try:
                    for line in f.readlines():
                    if line.find("MAXIMUM ERROR COUNT EXCEEDED")>0 :
                    raise line
                    finally:
                    f.close()

                    ctlfile = r"""<%=odiRef.getSrcTablesList("[WORK_SCHEMA]/[TABLE_NAME].ctl","")%>"""
                    logfile = r"""<%=odiRef.getSrcTablesList("[WORK_SCHEMA]/[TABLE_NAME].log", "")%>"""
                    outfile = r"""<%=odiRef.getSrcTablesList("[WORK_SCHEMA]/[TABLE_NAME].out", "")%>"""

                    loadcmd = r"""sqlldr <%=odiRef.getInfo("DEST_USER_NAME")%>/<%=odiRef.getInfo("DEST_PASS")%>@<%=odiRef.getInfo("DEST_DSERV_NAME")%> control="%s" log="%s" > "%s" """ % (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()

                    --------------------------------------------------------

                    This was suggested as an unsupported workaround by Oracle. I had to hack up the jython code a bit to get this working in my environment, but it does the job.

                    Matt