CODEWITHSUNDEEP
oracle-databaseplsql
Petr Kostroun
Petr Kostroun

Reputation: 455

oracle how to catch raise application error

How to write exception handler for raise_application_error call? Im completly stuck on it. I know how to rise and catch custom exception, but I dont know, how to catch error with raise_application_error with other catch block then "when others". Thank you for hint

Upvotes: 2

Views: 2548

Answers (2)

Nicola
Nicola

Reputation: 314

You can check sqlcode inside your "when others"

for example:

create or replace function square( n number) return number is
begin
  if n <= 10 then
    return n*n;
  else
    raise_application_error(-20001, 'too big');
  end if;
end;


declare
  myNumber number(10);
begin
  myNumber := square(300);
  dbms_output.put_line(myNumber);
exception
  when others then  
    if sqlcode=-20001 then
      dbms_output.put_line('beyond the limit '||sqlerrm);
    else
      dbms_output.put_line(sqlerrm);
    end if;
end;

Upvotes: 0

William Robertson
William Robertson

Reputation: 16001

Something like this?

declare
    no_bananas_on_tuesday exception;
    pragma exception_init(no_bananas_on_tuesday, -20123);
begin
    raise_application_error(-20123, 'Bananas are not available on a Tuesday');
exception
    when no_bananas_on_tuesday then
        dbms_output.put_line('It''s Tuesday but user requested a banana.');
end;
  1. The call above generates an ORA-20123 error.
  2. We define a custom exception no_bananas_on_tuesday associated with code -20123.
  3. When an ORA-20123 exception occurs, we can catch it with a no_bananas_on_tuesday exception handler.

Upvotes: 4

Related Questions