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

From: Doug C <dcowles_at_i84.net>
Date: Sat, 02 Jun 2001 13:30:19 -0700
Message-ID: <skiihtcr0348tgenpa80r83k60hopc777g@4ax.com>

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

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 - 15:30:19 CDT