1.  Don't code Exception handlers unless you have a clear and compelling reason to do so.  Unless there is a specific problem you are trapping, how can you ever guess what problem might occur?  By leaving out the exception code and by following my no blank lines rule, the line number returned with the exception error points to the exact place the error occurred.

2.  Don't raise your own exceptions in your procedures.  My reasons-- they break the rules of structured programming.  When you RAISE , it is just a disguised GOTO statement, which forces processing immediately to jump to the end of the procedure.

To handle a user-created exception, you have to write special code in three places in your procedure:  you have to declare it (Pragma exception init...), you have to raise it (GOTO the end), and you have to handle it (Exception when...).  It is so much easier and clearer to take care of a special condition in-line in your code, especially for someone else trying to read your code.

To pass back an error condition to a calling procedure, use a parameter -- something like Return_Code.  Then the calling procedure can test the Return_Code value immediately after the call, making all the code easier to follow.  If you use an exception in the caller, it isn't immediately clear to the next person reading your code that the sub procedure can raise such an error.  It just confuses the process.

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips