0

I need some advise to create simple mechanism for queueing.

Note that: "Consumer", "Producer" and "Product" words are from "producer–consumer problem" terminology.

I have couple processes - "Producer" (PL/SQL procedures) that can generate some "Product" (I mean that this can be anything like row or any ID).

Then this information I want to put into some FIFO queue (this can be table).

After that, I want to define for example 10 or even more (nice to be parametrized) "Consumers" (one PL/SQL procedure that can consume received "product" from queue).

Any idea how to do this ?

WBAR
  • 4,924
  • 7
  • 47
  • 81
  • 4
    AQ - aka Oracle Advanced Queuing. It is build-in messaging system inside Oracle database. – ibre5041 Nov 14 '13 at 11:04
  • Can You provide some examples then I will accept Your answer – WBAR Nov 14 '13 at 11:35
  • Attempting to implement this in a straight RDBMS is somewhat problematic, because SQL is expressly written for dealing with entire _sets_ of records; among other things, there's no inherent 'order' to the rows in a table (any order due to clustered indices and the like is a non-guaranteed optimization, and can't be relied upon). Note that it is possible to create something like this (and large-scale messaging systems are likely backed by a DB), but it takes quite a bit of work to make it reliable (generally to failure _outside_ of the DB). – Clockwork-Muse Nov 14 '13 at 11:47

1 Answers1

1

Look at official docs, plus you can find many howtos on the internet.

http://docs.oracle.com/cd/B10501_01/appdev.920/a96587/apexampl.htm

Also have to point that AQ can act as an Java JMS provider. So in Weblogic you can use AQ as a provider of the "generic" Java Messaging System standard.

ibre5041
  • 4,903
  • 1
  • 20
  • 35
  • I need pure PL/SQL solution and I can't understand how to make consumer. I want to put object in the Queue and the Queue when it comes create process (up to parametrized). Similar functionality provides http://docs.oracle.com/javase/6/docs/api/java/util/concurrent/ThreadPoolExecutor.html but in java and something like in PL/SQL would be worderful – WBAR Nov 14 '13 at 13:33
  • 1
    Simply create a repeatable scheduler job. Let this job start when database boots (db startup trigger). This job will have an infinite loop in it, and will wait till something appears in the queue. – ibre5041 Nov 14 '13 at 13:59
  • 1
    YOu can do this all in PL/SQL and register an async pl/sql procedure that will be executed when there is something in the queue. – OldProgrammer Nov 14 '13 at 15:21