Monday, June 6, 2011

Exception


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
Post a Comment