3 Replies Latest reply: Sep 5, 2012 3:29 AM by user5647282 RSS

    Oracle Error Handling in Shell Scripts

    user5647282
      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
          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
            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
              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