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).
