PL/SQL Exception Handling - III

PL/SQL exception Handling-III

User defined exceptions

These are invoked using
-RAISE
-RAISE_APPLICATION_ERROR Procedure

--take a table and insert few rows
create table XX_TEST
(
ref_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;
/

create or replace procedure user_excp_proc
as
cnt number;
my_excp exception;
begin
select count(*) into cnt
from xx_test
where name='dotnet';
if cnt = 0 then
raise my_excp;
dbms_output.put_line('next line in code after excpetion');
end if;
exception
when my_excp then
dbms_output.put_line('No subject ');
when others then
dbms_output.put_line(sqlerrm);
end;
/

begin
user_excp_proc;
end;
/

user defined exceptions using
RAISE_APPLICATION_ERROR

Syntax :

raise_application_error(
error_number, message[, {TRUE | FALSE}]);
where error_number is a negative integer in the range -20000..-20999 and message
is a character string up to 2048 bytes long. If the optional third parameter is TRUE, the
error is placed on the stack of previous errors. If the parameter is FALSE (the default),
the error replaces all previous errors. RAISE_APPLICATION_ERROR is part of package
DBMS_STANDARD, and as with package STANDARD, you need not qualify references to
it.

create or replace procedure user_excp_proc
as
cnt number;
begin
select count(*) into cnt
from xx_test
where name='dotnet';
if cnt = 0 then
raise_application_error(-20001,'no subject');
dbms_output.put_line('next line in code after excpetion');
end if;
exception
when others then
dbms_output.put_line('user exception..');
end;
/

begin
user_excp_proc;
end;
/