I have the following package,with 2 procedure insert_emp1() (to insert data), get_emp1 (to retrieve data) and a function validate_sal(),that checks if the salary is between 2000 and 5000.if salary is out of bound the insert_emp1() will generate an error message.but how would I add this exception to the procedure ..The code for the package(with out exception block) is given below.
Depends how you plan to use this function, but I don't like using "magic" return values, as it's easier and more maintainable to use the existing PL/SQL exception constructs, just as you would in Java.
If you want to raise an exception within the PL/SQL (which may result in a Java exception that you can trap eventually), then you can either raise an existing pre-defined exception e.g.
You would then either let this exception propagate back to your calling function and handle it there somehow, or you could handle it within the function e.g.
chris webster wrote:I don't like using "magic" return values, as it's easier and more maintainable to use the existing PL/SQL exception constructs, just as you would in Java.
True, but I'm extremely database agnostic. The more generic a database query/procedure is, without sacrificing performance, the better.
PL/SQL is capable of providing stack traces for exceptions like Java does. As someone who spent years maintaining systems incapable of producing stack traces, I'd say that forfeiting stack traces is a grand sacrifice (but of course that also depends on the total complexity of the stored procedures, if stored procedures do not call one another for example, the need is not that pressing).
Additionally, all decent databases should provide mechanism for throwing exceptions from stored procedures, and stored procedures are not portable anyway. Why avoid PL/SQL exceptions then?
Scott Selikoff wrote:True, but I'm extremely database agnostic. The more generic a database query/procedure is, without sacrificing performance, the better.
Perhaps, but as he's already using PL/SQL, he might as well use it properly. Anyway, a true agnostic might equally argue against using Java-specific exceptions and just do everything via C/Unix-style return codes in case you want to change your implementation language . But if people really want a nice fat stack trace all over their screen, you can get a PL/SQL stack trace as well using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, although I've never had reason to use this myself.