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