Wednesday, April 27, 2011

Diff of Unhandled and Handled exception


Step 1 : Create Table

 create table cust_table
 (
   NAME                                VARCHAR2(30),
   ORD_DT                            DATE,
   PUR_DATE                    DATE,
   QUANTITY                        NUMBER,
   ITEM_NO                         VARCHAR2(30)
  );
 
  Step 2 : Try to execute a block without exception part.
 
    begin
                                insert into cust_table(NAME,ORD_DT,PUR_DATE,QUANTITY,ITEM_NO) values('Sivakurunath',sysdate-1,sysdate,100,'ORA0001');
                               
                                insert into cust_table(NAME,ORD_DT,PUR_DATE,QUANTITY,ITEM_NO) values('Sivakumar',sysdate-1,sysdate,100,'ORA0002');
                               
                                update cust_table set NAME='eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'
                                where ITEM_NO='ORA0002';
 end;
 /

  Step 3 : Do select ,Check how many rows inserted and updated.
 
 select * from cust_table;

 Step 4 : Try to execute same block with  exception.
 
  begin
 
 
 
                                insert into cust_table(NAME,ORD_DT,PUR_DATE,QUANTITY,ITEM_NO) values('Sivakurunath',sysdate-1,sysdate,100,'ORA0001');
                               
                                insert into cust_table(NAME,ORD_DT,PUR_DATE,QUANTITY,ITEM_NO) values('Sivakumar',sysdate-1,sysdate,100,'ORA0002');
                               
                                update cust_table set NAME='eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'
                                where ITEM_NO='ORA0001';
                               
 exception
     when others then
        dbms_output.put_line(sqlerrm);
 end;
 /

 step 5 : Do select ,Check how many rows inserted and updated.

 select * from cust_table;


 step 6 : Find in which scenario transaction remain as a part of transaction (step 2 or step 4).

Prepared By
Sivakurunath S
Post a Comment