What is Exception ?
Whenever error occurred in executable area (ie. in begin block) then exception will raise and program will terminate .
Difference Between Calling and Called program
----------------------------------------------
A program contain many programs inside then it is called as calling program.
A program called by another program then it is called as called program.
Exception Works
================
1.If exception handled in both calling and called environment .Then any exception arrives inside the called program then exception will be handled by itself
only(ie. called program) and program will not terminate until exception raised in calling environment or execute completely.
2.While handling exception in calling environment but not in called environment .Then any exception arrives inside the called program then exception will be
handled by called one and program terminates immediately.
Check With Following Examples
=================================
Step 1: Drop Table If Exists
+++++++++++++++++++++++++++++
drop table error;
drop table tab_1;
drop table tab_2;
drop table tab_3;
Step 2: Create Table
+++++++++++++++++++++
create table error ( value number, status varchar2(1000),st_date timestamp,nam_tab varchar2(50));
create table tab_1 (a number , dup_num number, name varchar2(30));
create table tab_2 (a number , dup_num number, name varchar2(30));
create table tab_3 (a number , dup_num number, name varchar2(30));
Step 3: Drop Sequence
+++++++++++++++++++++++++++++
drop sequence test_seq;
Step 4 : create Sequence
++++++++++++++++++++++++
create sequence test_seq increment by 1 start with 1 maxvalue 100 nocache nocycle;
Step 5:= Create Packages use exception in both calling and called environment
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
create or replace package pack_one
as
procedure p1(p_a number);
end pack_one;
/
create or replace package body pack_one
as
procedure p1(p_a number)
as
v_num number;
v_error varchar2(4000);
begin
select dup_num
into v_num
from tab_1
where a = p_a;
dbms_output.put_line('Step 1 ..........................');
pack_two.p2(v_num);
dbms_output.put_line('Step 2 ..........................');
pack_three.p3(v_num);
dbms_output.put_line('Step 3 ..........................');
Exception
when others then
v_error := substr(sqlerrm,1,100);
insert into error values(test_seq.nextval ,v_error,sysdate,'pack_one.p1');
end p1;
end pack_one;
/
create or replace package pack_two
as
procedure p2(p_a number);
end pack_two;
/
create or replace package body pack_two
as
procedure p2(p_a number)
as
v_num number;
v_error varchar2(4000);
begin
select dup_num
into v_num
from tab_2
where a = p_a;
Exception
when others then
v_error := substr(sqlerrm,1,100);
insert into error values(test_seq.nextval ,v_error,sysdate,'pack_one.p2');
end p2;
end pack_two;
/
create or replace package pack_three
as
procedure p3(p_a number);
end pack_three;
/
create or replace package body pack_three
as
procedure p3(p_a number)
as
v_num number;
v_error varchar2(4000);
begin
select dup_num
into v_num
from tab_3
where a = p_a;
Exception
when others then
v_error := substr(sqlerrm,1,100);
insert into error values(test_seq.nextval ,v_error,sysdate,'pack_one.p3');
end p3;
end pack_three;
/
Step 6: Truncate Table rows only
+++++++++++++++++++++++++++++++++
Truncate table tab_1;
Truncate table tab_2;
Truncate table tab_3;
Step 7 : Insert Few Rows
++++++++++++++++++++++++++
Insert into tab_1 values(1,20,'Hello');
Insert into tab_2 values(2,3,'Hi');
Insert into tab_3 values(20,1,'Wow');
set serveroutput on
execute pack_one.p1(1);
Select * from error;
Step 8 : Create Package by writing Exception only in calling package (eg.pack_one)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
create or replace package pack_one
as
procedure p1(p_a number);
end pack_one;
/
create or replace package body pack_one
as
procedure p1(p_a number)
as
v_num number;
v_error varchar2(4000);
v_seq number;
begin
select dup_num
into v_num
from tab_1
where a = p_a;
dbms_output.put_line('Step 1 ..........................');
pack_two.p2(v_num);
dbms_output.put_line('Step 2 ..........................');
pack_three.p3(v_num);
dbms_output.put_line('Step 3 ..........................');
Exception
when others then
v_error := substr(sqlerrm,1,100);
insert into error values(test_seq.nextval ,v_error,sysdate,'pack_one.p1');
end p1;
end pack_one;
/
create or replace package pack_two
as
procedure p2(p_a number);
end pack_two;
/
create or replace package body pack_two
as
procedure p2(p_a number)
as
v_num number;
v_error varchar2(4000);
begin
select dup_num
into v_num
from tab_2
where a = p_a;
end p2;
end pack_two;
/
create or replace package pack_three
as
procedure p3(p_a number);
end pack_three;
/
create or replace package body pack_three
as
procedure p3(p_a number)
as
v_num number;
v_error varchar2(4000);
begin
select dup_num
into v_num
from tab_3
where a = p_a;
end p3;
end pack_three;
/
Step 9: Truncate Table rows only
+++++++++++++++++++++++++++++++++
Truncate table tab_1;
Truncate table tab_2;
Truncate table tab_3;
Step 10 : Insert Few Rows
++++++++++++++++++++++++++
Insert into tab_1 values(1,20,'Hello');
Insert into tab_2 values(2,3,’Hi’);
Insert into tab_3 values(20,1,'Wow');
set serveroutput on
execute pack_one.p1(1);
Select * from error;
Prepared By
S Sivakurunath
