wood burning stoves 2.0*
The moose likes JDBC and the fly likes adding exception in a procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "adding exception in a procedure" Watch "adding exception in a procedure" New topic
Author

adding exception in a procedure

Ayan Biswas
Ranch Hand

Joined: Jul 10, 2010
Posts: 104
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.


AyanBiswas
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

Have insert_emp1 return a status code indicating success or failure with the number used to specify which error was received.


My Blog: Down Home Country Coding with Scott Selikoff
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1671
    
  14

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.

No more Blub for me, thank you, Vicar.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

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

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Mar 01, 2009
Posts: 1671
    
  14

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.

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: adding exception in a procedure