• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

adding exception in a procedure

 
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.

 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic