vikrant

• Jan 21st, 2007

 

all the transactions done by that executable section will be rolled back

raghav_sy

 Profile Answers by raghav_sy 

• Feb 7th, 2007

 

well the answer is, what ever done in executable section will be lost. u can see the exapmle:created table d as below:SQL> create table d ( sl number(4), name varchar2(5));now do the following operation:SQL> begin insert into d values ('1223123123','geekinterview'); --making insert to fail here commit; exception --catching the exception here when others then commit; end; /PL/SQL procedure successfully completed.now if we use :SQL> select * from d; no rows selectedso even the pl/sql procedure completed successfully no values were saved in d.this is one case where we are giving invalid values for both the fields(in insert stmt.). now see the below procedure:SQL> begin insert into d values ('1223123123','RSY'); --first value is incorrect, second is correct one commit; exception when others then commit; end; /PL/SQL procedure successfully completed.SQL> select * from d; no rows selectedeven in this case, nothinghas been inserted in table d. even though value for name field is a valid data.now there is one possible case:SQL> begin insert into d values ('123','RSY'); --both values are correct commit; --commiting first time insert into d values ('1223123123','RSY'); --entering values to fail insert commit; exception --handling exception when others then commit; end; /PL/SQL procedure successfully completed.SQL> select * from d; SL NAME--------- ----- 123 RSYso here the first insert has been committed, but at the time of second insert the condition fails, so second insert is roll backed. but it will not affect data that has been already committed.ok take carecheersRSY

raghav_sy

 Profile Answers by raghav_sy 

• Feb 8th, 2007

 

hi ,again am posting the same answer, i do not know why the answer is not displayed with proper formatting. ok once more i will try:well the answer is, what ever done in executable section will be lost. u can see the exapmle:created table d as below:SQL> create table d ( sl number(4), name varchar2(5));now do the following operation:SQL> begin insert into d values ('1223123123','geekinterview'); --making insert to fail here commit; exception --catching the exception here when others then commit; end; /PL/SQL procedure successfully completed.now if we use :SQL> select * from d; no rows selectedso even the pl/sql procedure completed successfully no values were saved in d.this is one case where we are giving invalid values for both the fields(in insert stmt.). now see the below procedure:SQL> begin insert into d values ('1223123123','RSY'); --first value is incorrect, second is correct one commit; exception when others then commit; end; /PL/SQL procedure successfully completed.SQL> select * from d; no rows selectedeven in this case, nothinghas been inserted in table d. even though value for name field is a valid data.now there is one possible case:SQL> begin insert into d values ('123','RSY'); --both values are correct commit; --commiting first time insert into d values ('1223123123','RSY'); --entering values to fail insert commit; exception --handling exception when others then commit; end; /PL/SQL procedure successfully completed.SQL> select * from d; SL NAME --------- ----- 123 RSYso here the first insert has been committed, but at the time of second insert the condition fails, so second insert is roll backed. but it will not affect data that has been already committed.ok take carecheersRSY

geetha24

 Profile Answers by geetha24 Questions by geetha24

• Feb 9th, 2007

 

Whenever the exception is raised ..all the transaction made before will be commited. If the exception is not raised then all the transaction will be rolled back.

ae_sathis

 Profile Answers by ae_sathis 

• Feb 24th, 2008

 

SQL> create table d ( sl number(4), name varchar2(5));

now do the following operation:

SQL> begin insert into d values ('1223123123','geekinterview');

--making insert to fail here commit; exception

--catching the exception here when others then
commit;
end;
/

PL/SQL procedure successfully completed.

now if we use :

SQL> select * from d;

no rows selected so even the pl/sql procedure completed successfully no values were saved in d.
this is one case where we are giving invalid values for both the fields in insert stmt.).

now see the below procedure:

SQL> begin insert into d values ('1223123123','RSY');

--first value is incorrect, second is correct one commit;

exception when others then
commit;
end;
/

PL/SQL procedure successfully completed.

SQL> select * from d;

no rows selected even in this case, nothing has been inserted in table d.

even though value for name field is a valid data. now there is one possible case:

SQL> begin insert into d values ('123','RSY');

--both values are correct commit;

--committing first time insert into d values ('1223123123','RSY');
--entering values to fail insert commit; exception
--handling exception when others then commit;
end;
/

PL/SQL procedure successfully completed.

SQL> select * from d;

SL NAME

--------- -----

123 RSY

so here the first insert has been committed, but at the time of second insert the condition fails, so second insert is roll backed. but it will not affect data that has been already committed.
ok take care
cheers
RSY