Re: need 1 record asynchrnous inserts - DBMS_PIPE? DBMS_AQ? something else?

Comments in-line.I
n article <skiihtcr0348tgenpa80r83k60hopc777g_at_4ax.com>, "Doug C" <dcowles_at_i84.net> wrote:

> Oracle 8.1.7 has layed down the law with autonomous transactions, and
> the 4 we are using no longer work. All they do is insert a little
> record into a log table regardless of whether the calling package is
> commited or rolled back. Autonomous transactions are not supported, and
> according to Oracle they might not be available in Oracle 9 either.
> This is in the release notes for 8.1.5, but hey, developers don't always
> listen. (and then they leave)...
>

Excuse me? Are you saying 8.1.7 does NOT support autonomous transactions?? According to the 8.1.7 application developers guide, they ARE supported. Whatever gave you the idea they weren't?

...stuff deleted....
> So.. I thought about DBMS_PIPE.. Surfed the ususal spots, and it looks
> like it's more common to have some sort of daemon running in pro*c or a
> shell script..
>

This is how we currently do it. We write to DBMS_PIPE, and we have a Pro*C daemon that pciks up what's on the pipe and writes it to the table.
> There are four situations where the following code *used* to work on
> 8.1.5
>
> procedure write_log is
> pragma autonomous_transaction;
> being
> insert into ofs_intf_log values
> ('DMCTEST','D',0,SYSDATE,SYSDATE,0,0,0,0,1,'F','B',3); commit;
> exception
> when others dbms_output.put_line ('Others error in write_log'); end
> write_log..
>
> In other words, pretty simple insert statements, but of slightly varying
> width, also all on the same table. Sometimes they will contain
> variables so a string would require the || concat operator and so
> forth...
>

I see no reason they shouldn't work now. Nor am I aware of any reason autonomous transactions should disappear in 9i.

...stuff deleted......

> I'll keep reading.. but I thought someone may have been there before...
>

You should try reading the application developers guide. Try:
http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76939/adg08sql.htm#8266

Hope this helps,

-Mark Received on Sat Jun 02 2001 - 14:25:56 CDT