need 1 record aysychrnous inserts - DBMS_PIPE?

From: Doug C <dcowles_at_i84.net>
Date: Sat, 02 Jun 2001 20:57:07 -0700
Message-ID: <03djhtonjt0m9o8bken36n68eb2mfqor8g@4ax.com>

This is a complete repost with corrections.. I meant autonomous transactions *within* distributed transactions are not supported in 8.1.7 up.. The thread started to develop with a focus on that mistake so I though I would start fresh.  Bug 692232 is the bug for 8.1.7.

Oracle 8.1.7 has layed down the law with autonomous transactions *within distrubted transactions* , and the 4 we are using no longer work. You get an ORA-00164. 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 winthin distributed 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)...

So.. I need an alternative. Submitting the string with a quick dbms_job.submit I thought would work, but it's transaction space seems to be affected by the commit or rollback of the calling package.

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

Looking for the *simplest* way to do this possible. It's really a debugging tool.

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'd like to stay away from writting a shell or Pro*C daemon. I'd like possbily to just call another procedure every couple minutes with a system level dbms_job auto-run, which maybe either unpacks a message or pulls something of a queue and inserts the info..

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

Thanks,
D Received on Sat Jun 02 2001 - 22:57:07 CDT