This discussion is archived
3 Replies Latest reply: Sep 5, 2012 1:29 AM by user5647282 RSS

Oracle Error Handling in Shell Scripts

user5647282 Newbie
Currently Being Moderated
I need to manage 2 diferente class of errors :
Oracle Errors(produced in compilation time) and
Operating Syste Error(e.g. No Datbase conection ORA-1017,etc) my shell its KSH.


Please can you help me how can I manage then?

this my alternative but is not correct ;

#creating conexion with sql
exit | sqlplus -s $USERPV_DB/$PWDPV_DB @$VORDSQLPATH/ord.extractor_porven.sql $VFDESDE $VFHASTA > $VORDDATOS_PATH/ord.extractor_porven$VDATE.dat 2>> $VLOG

     
     #Evaluating last sentence (sqlplus . . . . )
     VERROR=$?
     
     #Si VERROR=0 should stop process execution and alert with echo ".."Oracle error handling before compilation time
     
     if [ $VERROR  != 0 ]
     then
     echo "value of VERROR are:$VERROR"
     echo "`date +"$V_FORMATDATE"` DATA EXTRACTION WAS NOT SUCESSFUL ERROR BEFORE COMPILATION TIME" >> $VLOG 2> /dev/null
     " Here show VERROR
     
     else #Oracle error handling in compilation time
     echo "`date +"$V_FORMATDATE"` DATA EXTRACTION WAS NOT SUCESSFUL ERROR BEFORE COMPILATION TIME " >> $VLOG
     " Here show VERROR
     
else if [ $VERROR  = 0 ]
" DATA EXTRACTION WAS SUCESSFUL"

fi

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


Would apreciate your help its very urgent.

Best Regards
Antonio
  • 1. Re: Oracle Error Handling in Shell Scripts
    EdStevens Guru
    Currently Being Moderated
    user5647282 wrote:
    I need to manage 2 diferente class of errors :
    Oracle Errors(produced in compilation time) and
    Operating Syste Error(e.g. No Datbase conection ORA-1017,etc) my shell its KSH.


    Please can you help me how can I manage then?

    this my alternative but is not correct ;

    #creating conexion with sql
    exit | sqlplus -s $USERPV_DB/$PWDPV_DB @$VORDSQLPATH/ord.extractor_porven.sql $VFDESDE $VFHASTA > $VORDDATOS_PATH/ord.extractor_porven$VDATE.dat 2>> $VLOG
    Piping the output of 'exit' to sqlplus????????? what do you expect from this?
         
         #Evaluating last sentence (sqlplus . . . . )
         VERROR=$?
    any error returned by sqlplus as $? would be a fatal error of sqlplus itself, not any error returned by processing a sql statement. If your script ord.extractor_porven.sql were to generate, say, an ORA-00001, that is NOT an error in sqlplus and so does not return to the OS as an error of sqlplus.
         
         #Si VERROR=0 should stop process execution and alert with echo ".."Oracle error handling before compilation time
         
         if [ $VERROR  != 0 ]
         then
         echo "value of VERROR are:$VERROR"
         echo "`date +"$V_FORMATDATE"` DATA EXTRACTION WAS NOT SUCESSFUL ERROR BEFORE COMPILATION TIME" >> $VLOG 2> /dev/null
         " Here show VERROR
         
         else #Oracle error handling in compilation time
         echo "`date +"$V_FORMATDATE"` DATA EXTRACTION WAS NOT SUCESSFUL ERROR BEFORE COMPILATION TIME " >> $VLOG
         " Here show VERROR
         
    else if [ $VERROR  = 0 ]
    " DATA EXTRACTION WAS SUCESSFUL"

    fi

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


    Would apreciate your help its very urgent.
    there is no "urgent" here.
    "Urgent" means one of two things -

    1) people are dying, or

    2) you have a customer-facing, revenue-producing production system that is down.
    (And to get some perspective on the second case, keep the first in mind.)

    For the first, you call whatever civil emergency service seems appropriate.

    For the second, you open an SR with Oracle - which requires a paid-up support contract. For them to consider your problem "urgent", you will need to demonstrate that your problem falls under item #2. I seriously doubt your problem fits that criteria.

    Best Regards
    Antonio
  • 2. Re: Oracle Error Handling in Shell Scripts
    rukbat Guru Moderator
    Currently Being Moderated
    Moderator Action:
    This thread is moved from the Oracle Linux forum,
    to the PL/SQL forum, where you have all your other error-in-script postings.
  • 3. Re: Oracle Error Handling in Shell Scripts
    user5647282 Newbie
    Currently Being Moderated
    Well thanks in advance, what I mean with urgent, it's posibility to be fired if program was not sucessfull
    finally I was fired, but ,this is our life we must learn a shell script in less than 2 weeks, otherwise we are
    not a good programmer.
    No problem, earth's still in movement.

    Thanks
    Antonio

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points