2

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?

Dessa Simpson
  • 491
  • 7
  • 25

1 Answers1

-2

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.

OracleDBA
  • 27
  • 4
  • 1
    That does the exact same thing as your previous answer. – Dessa Simpson Feb 21 '19 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. – Dessa Simpson Feb 22 '19 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 '19 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 – Dessa Simpson Feb 22 '19 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. https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1052.htm#i1577005 – OracleDBA Feb 22 '19 at 19:45