We have a script that deploys code to our database as part of our application deployments. Currently, if a view or package errors, it ignores that error and moves on. We want it to fail at that point and exit sqlplus. I found the WHENEVER SQLERROR statement, but found that it treats warnings as errors. We need it to continue on a warning but fail on an error. Is this possible?


What oracle version? And can you give more detail of the circumstance? Oracle has methods of trapping errors within a block using the 'exception' statement, which allows you to capture the exact error number and choose an action. If you are deploying code, it would probably be possible to call your compilation task from within an execution block (alter ... compile), and trap any errors and warnings. Sorry about the duplicate, I tried to delete the original and didnt realize someone had at looked at it that quickly.

  • 1
    That does the exact same thing as your previous answer. – Duncan X Simpson Feb 21 at 22:53
  • This is not an answer in its current form. You could turn it into one by describing the use of the exception statement. – Duncan X Simpson Feb 22 at 15:57
  • Its a very broad topic. What version of oracle are you using? And can you give an example of the code? Come to think about it, if you are talking about compile errors, it should also be possible to run the command 'show errors' after each compile, with the output spooled to afile, then grep for the word error in the file. – OracleDBA Feb 22 at 18:32
  • The idea is to stop execution when we run into an error but continue if it's just a warning. Oracle server 12c, client 18 – Duncan X Simpson Feb 22 at 18:38
  • Ok, it sounds like you are talking about when you get an error compiling the procedure or package. So after each package or view compilation, you can do show err which will show the error information. To automate that, you would write a small pl/sql block that reads through the user_error tables where the name column is the name of your object, and the attribute is error. If you get a row back, exit. docs.oracle.com/cd/B19306_01/server.102/b14237/… – OracleDBA Feb 22 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.