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

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 Received on Wed May 15 2013 - 23:29:22 CEST