Certain dynamic sql statements make stored procedures "forget" transaction?
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