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