Rollback whole procedure

[pratikbarot]

Below is my procedure....
There is no table called "pratik" in my database so that the procedure give error.
so that exception is fire ......but it cant rollback 2 insert statement that is written above DDL statement...
Here i want to rollback the 2 insert statement also....

can any one help me.....
Plz

CREATE OR REPLACE procedure temp
as
begin
insert into t values(1);
insert into t values(2);
EXECUTE IMMEDIATE 'DROP TABLE PRATIK';
insert into t values(4);
EXCEPTION
WHEN OTHERS THEN
Rollback;
end;

[mkkgupta]

DDL commands are auto commit so when you are doing rollback it givs error.
thus u cant rollback those 2 insert statemnts.

Below is my procedure....
There is no table called "pratik" in my database so that the procedure give error.
so that exception is fire ......but it cant rollback 2 insert statement that is written above DDL statement...
Here i want to rollback the 2 insert statement also....

can any one help me.....
Plz

CREATE OR REPLACE procedure temp
as
begin
insert into t values(1);
insert into t values(2);
EXECUTE IMMEDIATE 'DROP TABLE PRATIK';
insert into t values(4);
EXCEPTION
WHEN OTHERS THEN
Rollback;
end;

[SQLNAVIGATOR]

DDL commands are auto commit so when you are doing rollback it givs error.
thus u cant rollback those 2 insert statemnts.

hi , Insert is not a DDL command.You cna rollback if you set save point after each Insert and rollback up to save point.


cheers
SN

[saravanankm]

Hi

U can't Rollback Above two insert Command Because Drop is Auto comit DDL
Command.
This is Possible to Rollback insert Command

CREATE OR REPLACE procedure temp
as
begin
insert into PRATIK values(4);
EXECUTE IMMEDIATE 'DROP TABLE PRATIK';
insert into t1 values(1);
insert into t1 values(2);
insert into PRATIK values(4);
EXCEPTION
WHEN OTHERS THEN
Rollback;
end;