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

> 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.