Re: asynchronous PL/SQL development; DBMS_AQ ??

Hi Cosmin,

You can use dbms_pipe to communicate and dbms_lock to synchronize.

I used them to create a parallel stats gathering package for standard edition Oracle. There would be a master process that distributes work amongs multiple "worker" processes.

-- 
Christo Kutrovsky
Senior Database/System Administrator
The Pythian Group - www.pythian.com
I blog at http://www.pythian.com/blogs/

On 4/27/07, Cosmin Ioan <cosmini_at_bridge-tech.com> wrote:


> hi Alberto,  thx much for the reply --

>

> what I'm actually looking for is an extremely fast/responsive AQ mechanism

> for a scenario where we have a big module that could be (written) in a

> modular fashion. This module contains many snippets of code that could be

> written asynchronously, but at the end, I need to retrieve data, bits and

> pieces, from each sub-procedure.

>

> This **entire** main procedure may take 1-3 seconds, but in a large OLTP

> fast-transact environment, squeezing/parallelizing any operations that could

> be deemed asynchronous could be a life-saver.

>

> don't think that dbms_job is fast enough to account for milli or

> microseconds.... pooling every second or so, is definitely something we

> can't entertain....

>

> Any thoughts?

> thx,

> Cos

>

> Alberto Dell'Era <alberto.dellera_at_gmail.com> wrote:

> If I have understood what you want - I usually use dbms_job

> for this, in a nutshell

>

> create table remaining (counter int);

> insert into remaining (counter) values (2);

> dbms_job.submit ( ... proc_1 );

> dbms_job.submit ( ... proc_2 );

> commit;

>

> each proc will decrement counter upon completion (ok/ko).

>

> Then in the "main thread" (the session that fired the jobs)

> i simply poll, say every second, table "remaining" until counter is zero.

>

> You can get as sophisticated as you want from here (logging

> proc errors in the table as well is quite useful for example).

>

> I think that examples of this technique can be found on asktom,

> I can search there for you if you want.

>

> hth

> alberto

>

> On 4/27/07, Cosmin Ioan wrote:

> > hi all,

> > what's the best & most robust package these days, whether Oracle supplied

> > (DBMS_AQ?) or third party, to do asynchronous PL/SQL development (9i or

> > 10g). I have various scenarios, multiple procedures that I'd like to fire

> > at once and upon their validated completion to do some further

> computations.

> >

> > thx much,

> > Cos

> >

> >

> >

>

>

> --

> Alberto Dell'Era

> "dulce bellum inexpertis"

>

>



-- 
Christo Kutrovsky
Senior Database/System Administrator
The Pythian Group - www.pythian.com
I blog at http://www.pythian.com/blogs/
--
http://www.freelists.org/webpage/oracle-l