Certain dynamic sql statements make stored procedures "forget" transaction?

From: neilsolent <n_at_solenttechnology.co.uk>
Date: Tue, 14 May 2013 10:09:45 -0700 (PDT)
Message-ID: <ec8dba61-3811-4264-859a-57a5ea111f76_at_e9g2000vbg.googlegroups.com>



Hi

Pleae see the code below. The grant statement causes the stored procedure to fail to roll back when raise_application_error is called.
(Presumably this is because the grant includes a commit?)
Is there a way to workaround this - that is rollback the changes in the stored procedure when a subsequent exception is thrown after such a bit of dynamic sql?

Thanks very much for any help - Neil


create table example
(

        example int
);

CREATE PROCEDURE sp_example
AS
BEGIN

	BEGIN
		insert into example(example) values (1);

		RAISE_APPLICATION_ERROR(-20001, 'Abort 1');

	COMMIT;
	END;

END;
/

CREATE PROCEDURE sp_example2
AS
BEGIN

	declare sql_v varchar(100);
	BEGIN
		insert into example(example) values (2);

		sql_v := 'grant ssf_admin to ssf_user1';
		execute immediate sql_v;

		RAISE_APPLICATION_ERROR(-20001, 'Abort 2');

	COMMIT;
	END;

END;
/

SQL> exec sp_example;
BEGIN sp_example; END;

*
ERROR at line 1:

ORA-20001: Abort 1
ORA-06512: at "SSF.SP_EXAMPLE", line 7
ORA-06512: at line 1


SQL> select * from example;

no rows selected

SQL> exec sp_example2;
BEGIN sp_example2; END;

*
ERROR at line 1:

ORA-20001: Abort 2
ORA-06512: at "SSF.SP_EXAMPLE2", line 11
ORA-06512: at line 1


SQL> select * from example;

   EXAMPLE


         2 Received on Tue May 14 2013 - 19:09:45 CEST

Original text of this message