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

From: ddf <oratune_at_msn.com>
Date: Thu, 16 May 2013 11:14:08 -0700 (PDT)
Message-ID: <c02a8cce-a935-4885-8d79-5d979b4f18b4_at_googlegroups.com>

On Wednesday, May 15, 2013 3:29:22 PM UTC-6, Gunter Herrmann wrote:
> Hi!
>
>
>
> neilsolent wrote:
>
> > 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.
>
>
>
> 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

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 Received on Thu May 16 2013 - 20:14:08 CEST