CODEWITHSUNDEEP
oracle-databaseplsql
gal mor
gal mor

Reputation: 69

raise_application_error reuse of the error

I was wondering if theres a way in which I can decide that from "this day on" a user-defined error can act as a resource I can use over and over.

For example-> Raise_Application_Error (-20343, 'The balance is too low.');

So basically if I can use -20343 as key word(error code) and use again in a different procedure instead of raising it again and again.., Is that possible?

Upvotes: 0

Views: 113

Answers (1)

Littlefoot
Littlefoot

Reputation: 143063

Well, you'll have to raise it, somehow, Oracle can't know what you want to do if balance is too low.

Maybe you could create your own table of exceptions, e.g.

SQL> select * from my_exception;

  ERR_CODE ERR_NAM ERR_MESSAGE
---------- ------- ------------------------------
    -20343 bal_low Balance is too low
    -20344 name_s  Name can not begin with an "S"

Function accepts error code and returns message:

SQL> create or replace function f_myerr (par_err_code in my_exception.err_code%type)
  2    return my_exception.err_message%type
  3  is
  4    retval my_exception.err_message%type;
  5  begin
  6    select err_message
  7      into retval
  8      from my_exception
  9      where err_code = par_err_code;
 10    return retval;
 11  exception
 12    when no_data_found then
 13      return 'Exception does not exist';
 14  end;
 15  /

Function created.

This piece of code simulates "balance too low" error:

SQL> declare
  2    l_balance number;
  3    bal_low   exception;
  4  begin
  5    select sal into l_balance
  6      from emp
  7      where ename = 'JONES';
  8
  9    if l_balance < 5000 then
 10       raise bal_low;
 11    end if;
 12
 13  exception
 14    when bal_low then
 15      raise_application_error(-20343, f_myerr(-20343));
 16  end;
 17  /
declare
*
ERROR at line 1:
ORA-20343: Balance is too low
ORA-06512: at line 15


SQL>

Another PL/SQL procedure might also find out that balance is too low, but you'd have to repeat such a code again, I'm afraid.

You can modify that code (i.e. table, function) to better suit your needs, but - that's what I understood for what you said so far.

Upvotes: 2

Related Questions