Some info on scheduler chains here
https://oracle-base.com/articles/10g/scheduler-enhancements-10gr2 For queueing, here is a simple example:
SQL> create or replace type payload as object
2 ( message varchar2(200) );
3 /
Type created.
SQL>
SQL> begin
2 dbms_aqadm.create_queue_table (
3 queue_table => 'myqueue_tab',
4 queue_payload_type => 'payload'
5 );
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> begin
2 dbms_aqadm.create_queue (
3 queue_name => 'myqueue',
4 queue_table => 'myqueue_tab'
5 );
6
7 dbms_aqadm.start_queue (
8 queue_name => 'myqueue'
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
Now that my queue is created, I have a program that I want to run ...but only when (say) Stage 1 is complete. So I *start* that program, but it will wait on a message to arrive:
SQL> set serverout on
SQL> declare
2 l_dequeue_options dbms_aq.dequeue_options_t;
3 l_message_properties dbms_aq.message_properties_t;
4 l_message_handle raw(16);
5 l_payload payload;
6
7 begin
8 dbms_aq.dequeue(
9 queue_name => 'myqueue',
10 dequeue_options => l_dequeue_options,
11 message_properties => l_message_properties,
12 payload => l_payload,
13 msgid => l_message_handle
14 );
15
16 dbms_output.put_line(l_payload.message);
17
18 end;
19 /
(waiting.....)
Now the other parts of my application are running etc, and when they are done, they will pop a message on the queue.
SQL> declare
2 l_enqueue_options dbms_aq.enqueue_options_t;
3 l_message_properties dbms_aq.message_properties_t;
4 l_message_handle raw(16);
5 l_payload payload;
6 begin
7 l_payload := payload('Stage 1 complete');
8 dbms_aq.enqueue(
9 queue_name => 'myqueue',
10 enqueue_options => l_enqueue_options,
11 message_properties => l_message_properties,
12 payload => l_payload,
13 msgid => l_message_handle
14 );
15 end;
16 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
I flick over the other session, and voila ....
...
8 dbms_aq.dequeue(
9 queue_name => 'myqueue',
10 dequeue_options => l_dequeue_options,
11 message_properties => l_message_properties,
12 payload => l_payload,
13 msgid => l_message_handle
14 );
15
16 dbms_output.put_line(l_payload.message);
17
18 end;
19 /
Stage 1 complete
PL/SQL procedure successfully completed.
SQL>
There are also more advanced capabilities, eg, notification, which you can see an example of here:
https://asktom.oracle.com/pls/asktom/asktom.search?tag=advanced-queuing-plsql-notification