Find the answer to your Linux question:
Results 1 to 7 of 7
I am trying capture error code from sql loader from linux shell script. Eventhough, there are errors while executing sqlldr, it is always returing recode zero. why i can not ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Just Joined!
    Join Date
    Oct 2012
    Posts
    24

    can not capture error code from sql loader in shell script


    I am trying capture error code from sql loader from linux shell script.
    Eventhough, there are errors while executing sqlldr, it is always returing recode zero.
    why i can not get error code from sql loader in linux?
    would you mind help me
    thanks a lot in advance
    Here is a part of the script
    Code:
    sqlldr userid=$USR_NM/$PW control=$cntlfile data=$infile bad=$badFile  errors=100 discard=$infile.dsc discardmax=1 log=$logFile direct=true
    
    retcode=`echo $?` 
    case "$retcode" in
            0) echo "SQL*Loader execution successful" ;;
            1) echo "ERROR: SQL*Loader execution exited with EX_FAIL, see logfile" ;;
            2) echo "ERROR: SQL*Loader execution exited with EX_WARN, see logfile" ;;
            3) echo "ERROR: SQL*Loader execution encountered a fatal error" ;;
            *) echo "unknown return code";;
    esac
    
    if [ $retcode -ne 0 ]; then
            echo "ERROR: SQL*Loader execution encountered an error"
            return
    fi
    Here is log file
    Code:
    + [ 0 -ne 0 ]
    + sqlldr userid=user/password control=/temp/invoice.ctl
     data=/opt/appweb/data/invoice.dat
    SQL*Loader-601: For INSERT option, table must be empty.  Error on table 
    invoice_t
    SQL*Loader: Release 11.2.0.3.0 - Production on Fri Apr 19 20:59:49 2013
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    + bad=/temp/logs/invoice.bad
    + errors=100
    + discard=/temp/logs/invoice.dsc
    + discardmax=1
    + log=/temp/logs/invoice.log
    + direct=true
    + echo 0
    + retcode=0
    + echo 'SQL*Loader execution successful'
    SQL*Loader execution successful
    + [ 0 -ne 0 ]

  2. #2
    Linux Enthusiast
    Join Date
    Jan 2005
    Location
    Saint Paul, MN
    Posts
    630
    Do not do:
    Code:
    retcode=`echo $?`
    as it starts a sub-shell to do the echo command. Use:

    Code:
    retcode=$?

  3. #3
    Just Joined!
    Join Date
    Oct 2012
    Posts
    24
    Quote Originally Posted by alf55 View Post
    Do not do:
    Code:
    retcode=`echo $?`
    as it starts a sub-shell to do the echo command. Use:

    Code:
    retcode=$?
    Thanks . I will try and let you know
    Thanks again

  4. #4
    Just Joined!
    Join Date
    Oct 2012
    Posts
    24
    Quote Originally Posted by jimmymj View Post
    Thanks . I will try and let you know
    Thanks again

    Still the same result. why i can not get error record????
    Code:
    + [ 0 -ne 0 ]
    + sqlldr userid=user/password control=/temp/invoice.ctl
     data=/opt/appweb/data/invoice.dat
    SQL*Loader-601: For INSERT option, table must be empty.  Error on table 
    invoice_t
    SQL*Loader: Release 11.2.0.3.0 - Production on Fri Apr 19 20:59:49 2013
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    + bad=/temp/logs/invoice.bad
    + errors=100
    + discard=/temp/logs/invoice.dsc
    + discardmax=1
    + log=/temp/logs/invoice.log
    + direct=true
    + echo 0
    + retcode=0
    + echo 'SQL*Loader execution successful'
    SQL*Loader execution successful
    + [ 0 -ne 0 ]

  5. #5
    Just Joined!
    Join Date
    Oct 2012
    Posts
    24
    Quote Originally Posted by jimmymj View Post
    Thanks . I will try and let you know
    Thanks again

    after change to retcode=$?
    Still the same result. why i can not get error record????
    Code:
    + [ 0 -ne 0 ]
    + sqlldr userid=user/password control=/temp/invoice.ctl
     data=/opt/appweb/data/invoice.dat
    SQL*Loader-601: For INSERT option, table must be empty.  Error on table 
    invoice_t
    SQL*Loader: Release 11.2.0.3.0 - Production on Fri Apr 19 20:59:49 2013
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    + bad=/temp/logs/invoice.bad
    + errors=100
    + discard=/temp/logs/invoice.dsc
    + discardmax=1
    + log=/temp/logs/invoice.log
    + direct=true
    + echo 0
    + retcode=0
    + echo 'SQL*Loader execution successful'
    SQL*Loader execution successful
    + [ 0 -ne 0 ]

  6. #6
    Just Joined!
    Join Date
    Oct 2012
    Posts
    24
    Quote Originally Posted by jimmymj View Post

    after change to retcode=$?
    Still the same result. why i can not get error record????
    Code:
    + [ 0 -ne 0 ]
    + sqlldr userid=user/password control=/temp/invoice.ctl
     data=/opt/appweb/data/invoice.dat
    SQL*Loader-601: For INSERT option, table must be empty.  Error on table 
    invoice_t
    SQL*Loader: Release 11.2.0.3.0 - Production on Fri Apr 19 20:59:49 2013
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    + bad=/temp/logs/invoice.bad
    + errors=100
    + discard=/temp/logs/invoice.dsc
    + discardmax=1
    + log=/temp/logs/invoice.log
    + direct=true
    + echo 0
    + retcode=0
    + echo 'SQL*Loader execution successful'
    SQL*Loader execution successful
    + [ 0 -ne 0 ]
    Thank You!!. I have a typo so it does not work. now it is working.

  7. #7
    Trusted Penguin
    Join Date
    May 2011
    Posts
    4,353
    Glad you're sorted now. Marking as Solved. Note that in the futre you can do so yourself using the Thread Tools link located at the top of this page.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •