PL/SQL Exception Handling -II (Pragma Excpetion_init)

PL/SQL Exception Handling - II : Pragma Excpetion_init usage

Internally defined exceptions(ORA-n errors). These are raised implicitly.
However we have only few predefined error numbers associated with names as we discussed in
PL/SQL Exception Handling - I. The other predefined errors numbers are not associated with any predefined names.
In this we can assign names for those internal excpetions which have no names using
PRAGMA EXCEPTION_INIT (exception_name, error_code).


Let us take an example,


create table xx_test
(
id number(4),
name varchar2(20),
mobile number(10)
);

begin
insert into xx_test values (1001,'Oracle',12345);
insert into xx_test values (1002,'Jave',54321);
commit;
end;
/

Now we write a procedure which updates the name column of xx_test table with value higher than it's limit.
So obviously an excpetion will be raised. As we donot have such internal excpetion with a name.
catch it using OTHERS handler.

create or replace procedure xx_test_exception
as
BEGIN
update xx_test
set name='annnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnmmmm'
where id=1001;
commit;
EXCEPTION
WHEN others THEN
dbms_output.put_line(sqlcode||'-'||sqlerrm);
END;
/
run the procedure
SQL> begin
  2  xx_test_exception;
  3  end;
  4  /
-12899-ORA-12899: value too large for column "APPS"."XX_TEST"."NAME" (actual:
47, maximum: 20)
PL/SQL procedure successfully completed.


So now we use pragma exception_init and assign a name to the error no -12899
and now it will not go to others handler. See the modifed procedure
Note : to define the pragma, define in 2 steps

var_len_small excpetion; then
PRAGMA EXCEPTION_INIT(var_len_small, -12899);

create or replace procedure xx_test_exception
as
var_len_small EXCEPTION;
PRAGMA EXCEPTION_INIT(var_len_small, -12899);
BEGIN
update xx_test
set name='annnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnmmmm'
where id=1001;
commit;
EXCEPTION
when var_len_small then
dbms_output.put_line('trying to assign value which is higher than var limit');
WHEN others THEN
dbms_output.put_line(sqlcode||'-'||sqlerrm);
END;
/
see the output now,
SQL> begin
  2  xx_test_exception;
  3  end;
  4  /
trying to assign value which is higher than var limit
PL/SQL procedure successfully completed.

Exception Handling - III