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

From: ddf <oratune_at_msn.com>
Date: Tue, 14 May 2013 10:49:58 -0700 (PDT)
Message-ID: <d990d83b-8097-4f09-90db-faf37c0cfa98_at_googlegroups.com>

On Tuesday, May 14, 2013 11:09:45 AM UTC-6, neilsolent wrote:
> 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

DDL such as GRANT, CREATE, ALTER issue commits before and after the desired statement executes, preventing any sort of successful rollback. You will need to place the insert statement AFTER the dynamic sql if you want a rollback to occur:

SQL> create table example
  2 (
  3 example int
  4 );

Table created.

SQL>
SQL> CREATE PROCEDURE sp_example
  2 AS
  3 BEGIN

  4          BEGIN
  5                  insert into example(example) values (1);
  6
  7                  RAISE_APPLICATION_ERROR(-20001, 'Abort 1');
  8
  9          COMMIT;
 10          END;

 11 END;
 12 /

Procedure created.

SQL>
SQL> CREATE PROCEDURE sp_example2
  2 AS
  3 BEGIN

  4          declare sql_v varchar(100);
  5          BEGIN
  6                  insert into example(example) values (2);
  7
  8                  sql_v := 'grant ssf_admin to ssf_user1';
  9                  execute immediate sql_v;
 10
 11                  RAISE_APPLICATION_ERROR(-20001, 'Abort 2');
 12
 13          COMMIT;
 14          END;

 15 END;
 16 /

Procedure created.

SQL>
SQL> exec sp_example;
BEGIN sp_example; END;

*
ERROR at line 1:

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

SQL>
SQL> select * from example;

no rows selected

SQL>
SQL> exec sp_example2;
BEGIN sp_example2; END;

*
ERROR at line 1:

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

SQL>
SQL> select * from example;

   EXAMPLE

---

         2

1 row selected.

SQL>
SQL> truncate table example;

Table truncated.

SQL>
SQL> CREATE OR REPLACE PROCEDURE sp_example2   2 AS
  3 BEGIN

  4          declare sql_v varchar(100);
  5          BEGIN
  6                  sql_v := 'grant ssf_admin to ssf_user1';
  7                  execute immediate sql_v;
  8
  9                  insert into example(example) values (2);
 10
 11                  RAISE_APPLICATION_ERROR(-20001, 'Abort 2');
 12
 13          COMMIT;
 14          END;

 15 END;
 16 /

Procedure created.

SQL>
SQL> exec sp_example2
BEGIN sp_example2; END;

*
ERROR at line 1:

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

SQL>
SQL> select * From example;

no rows selected

SQL> David Fitzjarrell Received on Tue May 14 2013 - 19:49:58 CEST