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

neilsolent

unread,

May 14, 2013, 1:09:45 PM5/14/13

to

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

ddf

unread,

May 14, 2013, 1:49:58 PM5/14/13

to

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

neilsolent

unread,

May 15, 2013, 12:38:23 AM5/15/13

to

Thanks, makes sense.

This will be for auditing purposes eventually - the insert will be of
a log record in an audit table if a user ran any procedure and changed
anything in the database.
Armed with your advice, I know now to insert the audit table record
just before the execute statement and to limit myself to just one
execute per procedue, and as the last entry in the procedure. Then
either: (A) the execute statement fails and the audit record is rolled
back (and user changed nothing) or (B) the next execute statement was
successful (changed the database) and the audit record is committed.
In this way audit log records represent actual database changes (which
is my requirement) - syntax errors etc are omitted.

ddf

unread,

May 15, 2013, 9:24:25 AM5/15/13

to

You have that backwards -- look at the correctly functioning example again and you will see the insert is AFTER the execute immediate, not before. YOUR example does it the way you stated and it won't rollback the insert on error.

David Fitzjarrell

neilsolent

unread,

May 15, 2013, 11:34:38 AM5/15/13

to

> You have that backwards -- look at the correctly functioning example again and you will see the insert is AFTER the execute immediate, not before. YOUR example does it the way you stated and it won't rollback the insert on error.
>

Actually, I think I do want the audit log insert before the execute ..
If the execute of the grant fails, then there is no commit, and
exception is raised and everything is rolled back (including the
insert into the audit log), isn't it? That's what my tests seem to
show.

If I put the audit log insert after the update I fear I risk the
following (highly unlikely) scenario:

1. execute of grant succeeds (and therefore commits - can't be rolled
back)
2. insert into audit log fails (some real-time issue with Oracle
server - out of disk space let's say).
We then have a change made to the database without any accompanying
audit log record.

ddf

unread,

May 15, 2013, 2:51:02 PM5/15/13

to

On Wednesday, May 15, 2013 9:34:38 AM UTC-6, neilsolent wrote:
> > You have that backwards -- look at the correctly functioning example again and you will see the insert is AFTER the execute immediate, not before. YOUR example does it the way you stated and it won't rollback the insert on error.
>
> >
>
>
>
> Actually, I think I do want the audit log insert before the execute ..
>
> If the execute of the grant fails, then there is no commit, and
>
> exception is raised and everything is rolled back (including the
>
> insert into the audit log), isn't it? That's what my tests seem to
>
> show.
>
>

You're not testing properly:

SQL> exec sp_example2;
BEGIN sp_example2; END;

*
ERROR at line 1:

ORA-01919: role 'SSF_ADMIN_T' does not exist
ORA-06512: at "GRIBNAUT.SP_EXAMPLE2", line 9

ORA-06512: at line 1

SQL>
SQL> select * from example;

EXAMPLE
----------
2

SQL>

The grant failed but the commit BEFORE the grant was executed prevents the rollback. The only commit that failed was the commit after the grant was sttempted to be executed.

>
> If I put the audit log insert after the update I fear I risk the
>
> following (highly unlikely) scenario:
>
>
>
> 1. execute of grant succeeds (and therefore commits - can't be rolled
>
> back)

Certainly it can by using REVOKE.

>
> 2. insert into audit log fails (some real-time issue with Oracle
>
> server - out of disk space let's say).
>
> We then have a change made to the database without any accompanying
>
> audit log record.

No, you code this to revoke the grant if an error interrupts the insert:

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

5 sql_v varchar(100);
6 bad_storage exception;
7 pragma exception_init(bad_storage, -20001);
8 BEGIN

9 insert into example(example) values (2);
10

11 sql_v := 'grant ssf_admin to ssf_user1';
12 execute immediate sql_v;
13
14 RAISE_APPLICATION_ERROR(-20001, 'Abort 2');
15
16 COMMIT;
17
18 exception when bad_storage then
19 sql_v := 'revoke ssf_admin from ssf_user1';
20 execute immediate sql_v;
21 delete from example where example=2;
22
23 END;
24 END;
25 /

Procedure created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> exec sp_example2

PL/SQL procedure successfully completed.

SQL>
SQL> select * From example;

no rows selected

SQL>

SQL> select granted_role
2 from user_role_privs
3 where username = 'SSF_USER1'
4 and granted_role = 'SSF_ADMIN';

no rows selected

SQL>

The example is rigged to emulate a failed insert but I think you get the idea. The grant is gone by virtue of the exception handler.

David Fitzjarrell

neilsolent

unread,

May 15, 2013, 3:36:48 PM5/15/13

to

Ah OK - I didn't notice you said there is a commit before as well as
after the DDL statements.
That does make it harder to cater for all possible errors.
As you say - better to do the audit record insert after the DDL in
that case.

Gunter Herrmann

unread,

May 15, 2013, 5:29:22 PM5/15/13

to

Hi!

Inserts to log files should always be made as an autonomous transaction.
So that log will never be rolled back.

CREATE OR REPLACE procedure UTILITY.do_protocol (p_text varchar2) as
pragma autonomous_transaction;
begin
insert into protocol (text, ins_timestamp)
values (p_text, systimestamp);
commit;
end;
/

HTH

Gunter

Mladen Gogala

unread,

May 15, 2013, 6:16:50 PM5/15/13

to

On Wed, 15 May 2013 17:29:22 -0400, Gunter Herrmann wrote:

> Inserts to log files should always be made as an autonomous transaction.
> So that log will never be rolled back.

Or, you can use DBMS_SYSTEM.KSDWRT(2,....) which will make them
impossible to roll back, as the destination is not a relational database.
I believe it's even cheaper than to write into a table. Note to myself:
test this with 11G where alert log is an XML file.

--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

Mladen Gogala

unread,

May 15, 2013, 6:50:18 PM5/15/13

to

On Wed, 15 May 2013 22:16:50 +0000, Mladen Gogala wrote:

> Note to myself:
> test this with 11G where alert log is an XML file.

It works:

<msg time='2013-05-15T18:46:47.803-04:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='oradb.home.com' host_addr='192.168.1.181'
module='sql...@oradb.home.com (TNS V1-V3)'
pid='4217'>
<txt>Mladen: test write
</txt>
</msg>

DBMS_SYSTEM.KSDWRT(2,'....') still writes to the log file, even after the
log file became an XML thing.

neilsolent

unread,

May 16, 2013, 11:48:44 AM5/16/13

to

> > Inserts to log files should always be made as an autonomous transaction.
> > So that log will never be rolled back.
>
> Or, you can use DBMS_SYSTEM.KSDWRT(2,....) which will make them
> impossible to roll back, as the destination is not a relational database.
> I believe it's even cheaper than to write into a table. Note to myself:
> test this with 11G where alert log is an XML file.

But I want the insert to the audit log to roll back, if the operation
was rolled back.
When the audit log is examined, I want a record to mean an operation
was done, rather than merely attempted (or syntactically wrong, etc).

ddf

unread,

May 16, 2013, 2:14:08 PM5/16/13

to

Not necesarily. The OP WANTS to rollback log entries if the grant fails to execute. Also he wants to revoke the grant if the insert fails for some reason.

Rules of thumb exist so that those who use them can occasionally hit themselves on that thumb.

David Fitzjarrell