René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Exceptions in PL/SQL

set feedback off
set serveroutput on

create table foo (
  a  varchar2(10),
  b  varchar2(10),
  i  number
);

insert into foo values ('xxx','yyy',1);
insert into foo values ('zzz','aaa',1);
insert into foo values ('qqq','mmm',3);

commit;

declare
  l_a foo.a%type;
  l_b foo.b%type;
begin
  select a,b into l_a, l_b from foo where i=1;
  dbms_output.put_line('a: ' || l_a || ', b: ' || l_b);
exception 
  when too_many_rows then
    dbms_output.put_line('*** Exc: too many rows');
  when no_data_found then
    dbms_output.put_line('*** Exc: no data');
end;
/

declare
  l_a foo.a%type;
  l_b foo.b%type;
begin
  select a,b into l_a, l_b from foo where i=2;
  dbms_output.put_line('a: ' || l_a || ', b: ' || l_b);
exception 
  when too_many_rows then
    dbms_output.put_line('*** Exc: too many rows');
  when no_data_found then
    dbms_output.put_line('*** Exc: no data');
end;
/

declare
  l_a foo.a%type;
  l_b foo.b%type;
begin
  select a,b into l_a, l_b from foo where i=3;
  dbms_output.put_line('a: ' || l_a || ', b: ' || l_b);
exception 
  when too_many_rows then
    dbms_output.put_line('*** Exc: too many rows');
  when no_data_found then
    dbms_output.put_line('*** Exc: no data');
end;
/

drop table foo;
Use System defined Exception finder to find system defined exceptions.

Named exceptions

An exception can be given a name. In order to create such a named exceptions, two steps are necessary: first, it needs a line that reads exception_name exception. Then, this exception must be connected to a number with the pragma exception_init(exception_name, exc-number). The number must be in the range -20999 through -20000 (these are negative numbers!).
create or replace package exc as

  some_exc exception;

  pragma exception_init(some_exc, -20005);

  procedure raise_exc;
end exc;
/

create or replace package body exc as

  procedure raise_exc is begin 
    raise some_exc;
  end raise_exc;

end exc;
/
create or replace package use_exc as
  procedure do;
end use_exc;
/

create or replace package body use_exc as

  procedure do is begin

    exc.raise_exc;

    exception 
      when exc.some_exc then dbms_output.put_line('some_exc');
      when others       then dbms_output.put_line('others'  );
    
  end do;

end use_exc;
/
set serveroutput on
begin
  use_exc.do;
end;
/
The output:
some_exc

Predefined exceptions

PL/SQL predefines some exceptions:
  • access_into_null
    Corresponds to ORA-06530
  • case_not_found
    Corresponds to ORA-06592
  • collection_is_null
    Corresponds to ORA-06531
  • cursor_already_open
    Corresponds to ORA-06511
  • dup_val_on_index
    Corresponds to ORA-00001
  • invalid_cursor
    Corresponds to ORA-01001
  • invalid_number
    Corresponds to ORA-01722
  • login_denied
    Corresponds to ORA-01017
  • no_data_found
    Corresponds to ORA-01403
  • not_logged_on
    Corresponds to ORA-01012
  • program_error
    Corresponds to ORA-06501
  • rowtype_mismatch
    Corresponds to ORA-06504
  • self_is_null
    Corresponds to ORA-?????
  • storage_error
    Corresponds to ORA-06500
  • subscript_beyond_count
    Corresponds to ORA-06533
  • subscript_outside_limit
    Corresponds to ORA-06532
  • sys_invalid_rowid
    Corresponds to ORA-01410
  • timeout_on_resource
    Corresponds to ORA-00051
  • too_many_rows
    Corresponds to ORA-01422
  • value_error
    Corresponds to ORA-06502
    See safe_to_number. save_to_number() is a function that converts a string into a number and catches the value_error exception which is thrown if a string is not convertable into a number.
  • zero_divide
    Corresponds to ORA-01476
There's no correspondance to ORA-20005 (child record found)
These exceptions are defined in dbms_standard using the exception_init pragma.