PL/SQL Documentation queue

This documentation contains info on how to manipulate scheduler queues with PL/SQL.
All queue procedures and functions are located in the SO_QUEUE package.

Back to top 

Create and edit queues

Queues can be created with the PL/SQL procedure SO_QUEUE.QUEUE_CREATE_OR_REPLACE. The same procedure is used for changing queue properties.

If a new queue is created and no value for the description is given the queue's description will be the same as the name.
procedure queue_create_or_replace(
    p_queue_name         in varchar2,
    p_queue_description  in varchar2 default null,
    p_queue_limit        in integer default null,
    p_minimum_priority   in integer default null,
    p_maximum_priority   in integer default null,
    p_open_on_creation   in boolean default true);
  
Parameter Description
p_queue_name The scheduler queue name.
The name is not case sensitive.
p_queue_description The queue description.
The description is case sensitive.
p_queue_limit The scheduler queue limit; the maximum number of active scheduler tasks in the queue.
If a value lower than zero is passed zero is assumed. A value higher than 9999 is converted to 9999.
If for a new queue no value is given the default limit will be 9999.
p_queue_minimum_priority The minimum priority of the queue.
If a value lower than zero is passed zero is assumed. A value higher than 99 is converted to 99.
If for a new queue no value is given the default minimum priority will be zero.
p_queue_maximum_priority The maximum priority of the queue.
If a value lower than zero is passed zero is assumed. A value higher than 99 is converted to 99.
If for a new queue no value is given the default maximum priority will be 99.
p_open_on_creation Indicates if the queue status must be open when a new queue is created.
Default a new queue is open on creation.
This parameter is ignored if the queue already exists.
Error Description
OSC-10013
USER "%1" IS NOT REGISTERED
OSC-10020
SCHEDULER USER "%1" HAS READ ACCESS
OSC-10030
SCHEDULER USER "%1" HAS NO ADMINISTRATOR PRIVILEGES
OSC-10034
QUEUE NAME IS NULL
OSC-10040
QUEUE NAME "%1" TOO LONG

Example 1

In this example a new queue BATCH is created. Because no parameters are used the queue is open after creation, the queue limit 9999, the minimum priority zero and the maximum priority 99. If the queue BATCH already exists the original queue is not changed, it will keep it's original settings.

begin
  so_queue.queue_create_or_replace('batch');
end;
/
  

Example 2

In this example the scheduler queue limit is changed to ten. If the queue BATCH did not exist a new queue with limit ten is created.

begin
  so_queue.queue_create_or_replace(
      p_queue_name => 'batch',
      p_queue_limit => 10);
end;
/
  
Back to top 

Open queues

The PL/SQL procedure SO_QUEUE.QUEUE_OPEN is used if one or more queues must be opened.

The queue name can contain wildcards.
procedure queue_open(
    p_queue_name in varchar2);
  
Parameter Description
p_queue_name The scheduler queue name.
The name is not case sensitive.
Error Description
OSC-10013
USER "%1" IS NOT REGISTERED
OSC-10020
SCHEDULER USER "%1" HAS READ ACCESS
OSC-10030
SCHEDULER USER "%1" HAS NO ADMINISTRATOR PRIVILEGES

If a queue with the given name (or wildcard) does not exist no error is raised.

Example 3

In this example all queues are opened.

begin
  so_queue.queue_open(
      p_queue_name => '%');
end;
/
  
Back to top 

Close queues

The PL/SQL procedure SO_QUEUE.QUEUE_CLOSE is used if one or more queues must be closed.

The queue name can contain wildcards.
procedure queue_close(
    p_queue_name in varchar2);
  
Parameter Description
p_queue_name The scheduler queue name.
The name is not case sensitive.
Error Description
OSC-10013
USER "%1" IS NOT REGISTERED
OSC-10020
SCHEDULER USER "%1" HAS READ ACCESS
OSC-10030
SCHEDULER USER "%1" HAS NO ADMINISTRATOR PRIVILEGES

If no queue with the given name (or wildcard) exists no error is raised.

Example 4

In this example the queue BATCH is closed.

begin
  so_queue.queue_closed(
      p_queue_name => 'batch');
end;
/
  
Back to top 

Delete queues

The PL/SQL procedure SO_QUEUE.QUEUE_DELETE is used if one or more queues needs to be deleted.

The queue name can contain wildcards.
procedure queue_delete(
    p_queue_name in varchar2);
  
Parameter Description
p_queue_name The scheduler queue name.
The name is not case sensitive.
Error Description
OSC-10013
USER "%1" IS NOT REGISTERED
OSC-10020
SCHEDULER USER "%1" HAS READ ACCESS
OSC-10030
SCHEDULER USER "%1" HAS NO ADMINISTRATOR PRIVILEGES
OSC-10035
CANNOT DELETE QUEUE "%1"; USED AS DEFAULT IN "%2" SCRIPT(S)
OSC-10036
CANNOT DELETE QUEUE "%1"; USED IN "%2" TASK(S)

If no queue with the given name (or wildcard) exists no error is raised.
If an error is raised no queues are deleted.
The procedure always performs a database commit.

Example 5

In this example the queue SCHEDULER is not deleted because a scheduler script exists for which this queue is specified as the default scheduler script queue.

SQL>  exec so_queue.queue_delete('scheduler');
BEGIN so_queue.queue_delete('scheduler'); END;

*
ERROR at line 1:
ORA-20000: OSC-10035 CANNOT DELETE QUEUE "SCHEDULER"; USED AS DEFAULT
IN 4 SCRIPT(S)
ORA-06512: at "PLSQLSCH.SO_SERVER", line 5213
ORA-06512: at "PLSQLSCH.SO_QUEUE", line 29
ORA-06512: at line 1

SQL>
Back to top 

Queue server log

When a queue is created, changed or deleted the action is written to the scheduler server log.
The log can be queried with the database view SO_VW_SCHEDULER_LOG.
Next an example what is logged after the queue BATCH is created by the scheduler user PLSQLSCH.

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
column queue_name format a5
column log_text format a37
select log_date,queue_name,log_text 
from   so_vw_scheduler_log
where  queue_name='BATCH'
/

LOG_DATE            QUEUE LOG_TEXT
------------------- ----- -------------------------------------
2007-02-08 09:57:20 BATCH Queue "batch" created by PLSQLSCH.
2007-02-08 09:57:21 BATCH Status is open.
2007-02-08 09:57:21 BATCH Queue limit 999 tasks.
2007-02-08 09:57:21 BATCH Mininum priority 0.
2007-02-08 09:57:21 BATCH Maximum priority 99.
  
Back to top