• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

adding exception in a procedure

 
Ayan Biswas
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have insert_emp1 return a status code indicating success or failure with the number used to specify which error was received.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.


Alternatively, you can define your own exception in PL/SQL and handle it how you want.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Scott Selikoff wrote:
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?

(Just my two cents.)
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic