Oracle DBMS_AQ
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Advanced Queuing queue management
AUTHID CURRENT_USER
Constants
Name Data Type Value
DELAY
NO_DELAY BINARY_INTEGER 0
DEQUEUE_MODE
BROWSE BINARY_INTEGER 1
LOCKED BINARY_INTEGER 2
REMOVE BINARY_INTEGER 3
REMOVE_NODATA BINARY_INTEGER 4
EXPIRATION
NEVER BINARY_INTEGER -1
NAMESPACE
NAMESPACE_ANONYMOUS BINARY_INTEGER 0
NAMESPACE_AQ BINARY_INTEGER 1
NAVIGATION
FIRST_MESSAGE BINARY_INTEGER 1
NEXT_MESSAGE BINARY_INTEGER 3
NEXT_TRANSACTION BINARY_INTEGER 2
NTFN GROUPING CLASS
NTFN_GROUPING_CLASS_TIME BINARY_INTEGER 1
NTFN GROUPING REPEAT COUNT
NTFN_GROUPING_FOREVER BINARY_INTEGER -1
NTFN GROUPING TYPE
NTFN_GROUPING_TYPE_LAST BINARY_INTEGER 2
NTFN_GROUPING_TYPE_SUMMARY BINARY_INTEGER 1
SEQUENCE
BEFORE BINARY_INTEGER 2
TOP BINARY_INTEGER 3
STATE
BUFFERED_EXPIRED BINARY_INTEGER 10
DEFERRED BINARY_INTEGER 8
EXPIRED BINARY_INTEGER 3
PROCESSED BINARY_INTEGER 2
READY BINARY_INTEGER 0
WAITING BINARY_INTEGER 1
VISIBILITY
IMMEDIATE BINARY_INTEGER 1
ON_COMMIT BINARY_INTEGER 2
WAIT
FOREVER BINARY_INTEGER -1
NO_WAIT BINARY_INTEGER 0
Data Types CREATE OR REPLACE TYPE sys.aq$_sig_prop AS OBJECT (
signature   RAW(2000),
canalgo     VARCHAR2(2000),
digalgo     VARCHAR2(2000),
sigalgo     VARCHAR2(2000),
certificate VARCHAR2(2000),
digval      RAW(2000));
/

TYPE dequeue_options_t IS RECORD (
consumer_name  VARCHAR2(30)   DEFAULT NULL,
dequeue_mode   BINARY_INTEGER DEFAULT REMOVE,
navigation     BINARY_INTEGER DEFAULT NEXT_MESSAGE,
visibility     BINARY_INTEGER DEFAULT ON_COMMIT,
wait           BINARY_INTEGER DEFAULT FOREVER,
msgid          RAW(16)        DEFAULT NULL,
correlation    VARCHAR2(128)  DEFAULT NULL,
deq_condition  VARCHAR2(4000) DEFAULT NULL,
signature      sys.aq$_sig_prop   DEFAULT NULL,
transformation VARCHAR2(60)   DEFAULT NULL,
delivery_mode  PLS_INTEGER    DEFAULT PERSISTENT);
/

TYPE enqueue_options_t IS RECORD (
visibility         BINARY_INTEGER DEFAULT ON_COMMIT,
relative_msgid     RAW(16)        DEFAULT NULL,
sequence_deviation BINARY_INTEGER DEFAULT NULL,
transformation     VARCHAR2(60)   DEFAULT NULL);
/

TYPE message_properties_t IS RECORD (
priority        BINARY_INTEGER DEFAULT 1,
delay           BINARY_INTEGER DEFAULT NO_DELAY,
expiration      BINARY_INTEGER DEFAULT NEVER,
correlation     VARCHAR2(128)  DEFAULT NULL,
attempts        BINARY_INTEGER,
recipient_list  aq$_recipient_list_t,
exception_queue VARCHAR2(51)   DEFAULT NULL,
enqueue_time    DATE,
state           BINARY_INTEGER,
sender_id       aq$_agent      DEFAULT NULL,
original_msgid  RAW(16)        DEFAULT NULL);
/

TYPE sys.aq$_recipient_list_t IS TABLE OF sys.aq$_agent
INDEX BY BINARY_INTEGER;
/
Dependencies
ANYDATA DBMS_AQIN DBMS_PRVTAQIM
AQ$_AGENT DBMS_AQJMS DBMS_PRVTAQIP
AQ$_BND DBMS_AQJMS_INTERNAL DBMS_SCHEDULER
AQ$_BND_ARRAY DBMS_AQ_INV DBMS_STATS
AQ$_JMS_MESSAGE DBMS_AQ_LIB DBMS_STATS_INTERNAL
AQ$_POST_INFO_LIST DBMS_ASSERT DBMS_STREAMS_ADM_IVK
AQ$_QUEUES DBMS_CHANGE_NOTIFICATION DBMS_SYSTEM
AQ$_QUEUE_TABLES DBMS_GSM_UTILITY KUPC$QUE_INT
AQ$_RECEIPIENTS DBMS_ISCHED LTAQ
AQ$_REG_INFO_LIST DBMS_ISCHEDFW PRVT_ILM
AQ$_SIG_PROP DBMS_ISCHED_REMOTE_ACCESS SCHEDULER$NTFY_SVC_METRICS
DBMS_AQADM_SYS DBMS_LOGSTDBY SCHEDULER$_JOB_EVENT_HANDLER
DBMS_AQADM_SYSCALLS DBMS_PREPLUGIN_BACKUP SDO_TRKR
Documented Yes: Packages and Types Reference
Exceptions
Error Code Reason
ORA-24033 Message does not match criteria for any recipient or subscriber to the queue
ORA-25235 End of a transaction group
ORA-25254 time-out in LISTEN while waiting for a message
First Available 8.0
Security Model Owned by SYS with EXECUTE granted to MDSYS, SYSTEM, WMSYS, and the AQ_ADMINISTRATOR_ROLE, AQ_USER_ROLE, EXECUTE_CATALOG_ROLE, GSMADMIN_INTERNAL, GSMCATUSER and OEM_MONITOR roles
Source {ORACLE_HOME}/rdbms/admin/dbmsaq.plb
Subprograms
AQ$_BACKGROUND_OPER BIND_AGENT ENQUEUE_INT_SHARD
AQ$_BACKGROUND_OPER_PAS CREATE_DEQUEUE_JOB ENQUEUE_INT_SHARD_JMS
AQ$_DELETE_DIOT_TAB DEQUEUE ENQUEUE_INT_UNSHARDED
AQ$_DELETE_HIST_TAB DEQUEUE_ARRAY ENQUEUE_INT_UNSHARDED_PAS
AQ$_DELETE_TIOT_TAB DEQUEUE_INTERNAL LISTEN
AQ$_DEQUEUE DEQUEUE_INTERNAL_PAS POST
AQ$_ENQUEUE DISABLE_DEQUEUE_JOB REGISTER
AQ$_INSERT_DIOT_TAB DROP_DEQUEUE_JOB REGISTRATION_REPLICATION
AQ$_INSERT_HIST_TAB ENABLE_DEQUEUE_JOB SEEK
AQ$_INSERT_TIOT_TAB ENQUEUE UNBIND_AGENT
AQ$_UPDATE_HIST_TAB ENQUEUE_ARRAY UNREGISTER
AQ$_UPDATE_HIST_TAB_EX    
 
AQ$_BACKGROUND_OPER
Undocumented dbms_aq.aq$_background_oper(
schema         IN VARCHAR2,
queue_name     IN VARCHAR2,
table_name     IN VARCHAR2,
msgid          IN RAW,
operation_time IN TIMESTAMP,
action         IN BINARY_INTEGER,
subid          IN BINARY_INTEGER,
intflag        IN BINARY_INTEGER);
TBD
 
AQ$_BACKGROUND_OPER_PAS
Undocumented dbms_aq.aq$_background_oper_pas;
exec dbms_aq.aq$_background_oper_pas;
 
AQ$_DELETE_DIOT_TAB
Undocumented dbms_aq.aq$_delete_diot_tab(
schema IN VARCHAR2,
queue  IN VARCHAR2,
delsql IN VARCHAR2,
subid  IN NUMBER,
name   IN VARCHAR2,
qid    IN NUMBER,
msgid  IN RAW);
TBD
 
AQ$_DELETE_HIST_TAB
Undocumented dbms_aq.aq$_delete_hist_tab(
schema  IN VARCHAR2,
queue   IN VARCHAR2,
delsql  IN VARCHAR2,
msgid   IN RAW,
subid   IN NUMBER,
name    IN VARCHAR2,
address IN NUMBER);
TBD
 
AQ$_DELETE_TIOT_TAB
Undocumented dbms_aq.aq$_delete_tiot_tab(
schema   IN VARCHAR2,
qt_name  IN VARCHAR2,
nextdate IN TIMESTAMP,
txid     IN VARCHAR2,
msgid    IN RAW);
TBD
 
AQ$_DEQUEUE
Undocumented

Overload 1		 dbms_aq.aq$_dequeue(
queue_name        IN  VARCHAR2,
subscriber        IN  VARCHAR2,
msgid             IN  RAW,
correlation       IN  VARCHAR2,
dequeue_mode      IN  BINARY_INTEGER,
nativation        IN  BINARY_INTEGER,
visibility        IN  BINARY_INTEGER,
wait              IN  BINARY_INTEGER,
enqueue_time      OUT DATE,
state             OUT BINARY_INTEGER,
out_msgid         OUT RAW,
out_correlation   OUT VARCHAR2,
priority          OUT BINARY_INTEGER,
delay             OUT BINARY_INTEGER,
expiration        OUT BINARY_INTEGER,
attempts          OUT BINARY_INTEGER,
exception_queue   OUT VARCHAR2,
remote_recipients OUT dbms_aq.aq$_recipient_list_t,
sender_name       OUT VARCHAR2,
sender_addr       OUT VARCHAR2,
sender_protocol   OUT BINARY_INTEGER,
orginal_msgid     OUT RAW,
payload_type      IN  BINARY_INTEGER,
raw_user_data     OUT RAW,
object_user_data  OUT sys.standard.<ADT_1>,
deq_condition     IN  VARCHAR2,
signature         IN  sys.aq$_sig_prop,
out_sign          OUT sys.aq$_sig_prop,
transformation    IN  VARCHAR2,
bufmesg_prop      IN  BINARY_INTEGER,
lcr2xml_convert   IN  BOOLEAN);
TBD
Overload 2 dbms_aq.aq$_dequeue(
queue_name        IN  VARCHAR2,
subscriber        IN  VARCHAR2,
msgid             IN  RAW,
correlation       IN  VARCHAR2,
dequeue_mode      IN  BINARY_INTEGER,
nativation        IN  BINARY_INTEGER,
visibility        IN  BINARY_INTEGER,
wait              IN  BINARY_INTEGER,
enqueue_time      OUT DATE,
state             OUT BINARY_INTEGER,
out_msgid         OUT RAW,
out_correlation   OUT VARCHAR2,
priority          OUT BINARY_INTEGER,
delay             OUT BINARY_INTEGER,
expiration        OUT BINARY_INTEGER,
attempts          OUT BINARY_INTEGER,
exception_queue   OUT VARCHAR2,
remote_recipients OUT dbms_aq.aq$_recipient_list_t,
sender_name       OUT VARCHAR2,
sender_addr       OUT VARCHAR2,
sender_protocol   OUT BINARY_INTEGER,
orginal_msgid     OUT RAW,
payload_type      IN  BINARY_INTEGER,
raw_user_data     OUT RAW,
object_user_data  OUT sys.standard.<ADT_1>,
deq_condition     IN  VARCHAR2,
signature         IN  sys.aq$_sig_prop,
out_sign          OUT sys.aq$_sig_prop,
transformation    IN  VARCHAR2,
bufmesg_prop      IN  BINARY_INTEGER,
lcr2xml_convert   IN  BOOLEAN,
user_property     OUT sys.anydata);
TBD
Overload 3 dbms_aq.aq$_dequeue(
queue_name        IN  VARCHAR2,
subscriber        IN  VARCHAR2,
msgid             IN  RAW,
correlation       IN  VARCHAR2,
dequeue_mode      IN  BINARY_INTEGER,
nativation        IN  BINARY_INTEGER,
visibility        IN  BINARY_INTEGER,
wait              IN  BINARY_INTEGER,
enqueue_time      OUT DATE,
state             OUT BINARY_INTEGER,
out_msgid         OUT RAW,
out_correlation   OUT VARCHAR2,
priority          OUT BINARY_INTEGER,
delay             OUT BINARY_INTEGER,
expiration        OUT BINARY_INTEGER,
attempts          OUT BINARY_INTEGER,
exception_queue   OUT VARCHAR2,
remote_recipients OUT dbms_aq.aq$_recipient_list_t,
sender_name       OUT VARCHAR2,
sender_addr       OUT VARCHAR2,
sender_protocol   OUT BINARY_INTEGER,
orginal_msgid     OUT RAW,
payload_type      IN  BINARY_INTEGER,
raw_user_data     OUT RAW,
object_user_data  OUT sys.standard.<ADT_1>,
deq_condition     IN  VARCHAR2,
signature OBJECT  IN  sys.aq$_sig_prop,
out_sign OBJECT   OUT sys.aq$_sig_prop,
transformation    IN  VARCHAR2,
bufmesg_prop      IN  BINARY_INTEGER,
lcr2xml_convert   IN  BOOLEAN,
user_property     OUT sys.anydata,
delivery_mode     IN  BINARY_INTEGER,
out_delivery_mode OUT BINARY_INTEGER);
TBD
 
AQ$_ENQUEUE
Undocumented dbms_aq.aq$_enqueue(
queue_name         IN  VARCHAR2,
sender_name        IN  VARCHAR2,
sender_addr        IN  VARCHAR2,
sender_protocol    IN  BINARY_INTEGER,
orginal_msgid      IN  RAW,
correlation        IN  VARCHAR2,
visibility         IN  BINARY_INTEGER,
priority           IN  BINARY_INTEGER,
delay              IN  BINARY_INTEGER,
expiration         IN  BINARY_INTEGER,
relative_msgid     IN  RAW,
sequence_deviation IN  BINARY_INTEGER,
exception_queue    IN  VARCHAR2,
recipients         IN  dbms_aq.aq$_recipient_list_t,
payload_type       IN  BINARY_INTEGER,
raw_user_data      IN  RAW,
object_user_data   IN  sys.standard.<ADT_1>,
msgid              OUT RAW,
signature          IN  sys.aq$_sig_prop,
transformation     IN  VARCHAR2,
bufmesg_prop       IN  BINARY_INTEGER,
anydata_convert    IN  BOOLEAN,
lcr2xml_convert    IN  BOOLEAN,
user_property      IN  sys.anydata,
delivery_mode      IN  BINARY_INTEGER);
TBD
 
AQ$_INSERT_DIOT_TAB
Undocumented dbms_aq.aq$_insert_iot_tab(
schema    IN VARCHAR2,
queue     IN VARCHAR2,
inssql    IN VARCHAR2,
subid     IN NUMBER,
name      IN VARCHAR2,
qid       IN NUMBER,
eqtime    IN TIMESTAMP,
step_no   IN NUMBER,
priority  IN NUMBER,
eq_txid   IN VARCHAR2,
sender_id IN NUMBER,
txn_step  IN NUMBER,
chn_no    IN NUMBER,
lord_no   IN NUMBER,
flags     IN NUMBER,
msgid     IN RAW);
TBD
 
AQ$_INSERT_HIST_TAB
Undocumented dbms_aq.aq$_insert_hist_tab(
schema  IN VARCHAR2,
queue   IN VARCHAR2,
inssql  IN VARCHAR2,
msgid   IN RAW,
subid   IN NUMBER,
name    IN VARCHAR2,
address IN NUMBER);
TBD
 
AQ$_INSERT_TIOT_TAB
Undocumented dbms_aq.aq$_insert_tiot_tab(
schema   IN VARCHAR2,
qt_name  IN VARCHAR2,
nextdate IN TIMESTAMP,
txid     IN VARCHAR2,
msgid    IN RAW,
action   IN NUMBER);
TBD
 
AQ$_UPDATE_HIST_TAB
Undocumented dbms_aq.aq$_update_hist_tab(
schema     IN VARCHAR2,
queue      IN VARCHAR2,
updsql     IN VARCHAR2,
deq_time   IN TIMESTAMP WITH TIME ZONE,
deq_txid   IN VARCHAR2,
deq_user   IN VARCHAR2,
deq_uid    IN NUMBER,
prop_msgid IN RAW,
rt_count   IN NUMBER,
msgid      IN RAW,
subid      IN NUMBER,
sub_name   IN VARCHAR2,
address    IN NUMBER,
flag       IN NUMBER);
TBD
 
AQ$_UPDATE_HIST_TAB_EX
Undocumented dbms_aq.aq$_update_hist_tab_ex(
schema  IN VARCHAR2,
qt_name IN VARCHAR2,
msgid   IN RAW);
TBD
 
BIND_AGENT
Creates an entry for an AQ agent in the LDAP directory dbms_aq.bind_agent(
agent                IN sys.aq$_agent,
certificate_location IN VARCHAR2 DEFAULT NULL);
TBD
 
CREATE_DEQUEUE_JOB
Undocumented dbms_aq.create_dequeue_job(
job_name      OUT VARCHAR2,
queue_name    IN  VARCHAR2,
consumer_name IN  VARCHAR2,
callback      IN  VARCHAR2,
ctx           IN  RAW,
comments      IN  VARCHAR2);
TBD
 
DEQUEUE (new 21c overload)
Dequeues a message from the specified queue

Overload 1		 dbms_aq.dequeue(
queue_name         IN  VARCHAR2,
dequeue_options    IN  dbms_aq.dequeue_options_t,
message_properties OUT dbms_aq.message_properties_t,
payload            OUT sys.standard.<OPAQUE_1>
msgid              OUT RAW);
TBD
Overload 2 dbms_aq.dequeue(
queue_name         IN  VARCHAR2,
dequeue_options    IN  dbms_aq.dequeue_options_t,
message_properties OUT dbms_aq. message_properties_t,
payload            OUT sys.standard.<ADT_1>
msgid              OUT RAW);
TBD
Overload 3 dbms_aq.dequeue(
queue_name         IN  VARCHAR2,
dequeue_options    IN  dbms_aq.dequeue_options_t,
message_properties OUT dbms_aq.message_properties_t,
payload            OUT RAW
msgid              OUT RAW);
See AQ Demo 1 Linked At Page Bottom
Overload 4 dbms_aq.dequeue(
queue_name         IN  VARCHAR2,
dequeue_options    IN  sys.dbms_aq.dequeue_options_t,
message_properties OUT sys.dbms_aq.message_properties_t,
payload            OUT JSON,
msgid              OUT RAW);
TBD
 
DEQUEUE_ARRAY
Dequeues an array of messages from the specified queue

Overload 1		 dbms_aq.dequeue_array(
queue_name               IN  VARCHAR2,
dequeue_options          IN  dbms_aq.dequeue_options_t,
array_size               IN  BINARY_INTEGER,
message_properties_array OUT dbms_aq.message_properties_array_t,
payload_array            OUT sys.standard.<COLLECTION_1>,
msgid_array              OUT dbms_aq.msgid_array_t)
RETURN PLS_INTEGER;
See ENQUEUE_ARRAY Demo Below
Overload 2 dbms_aq.dequeue_array(
queue_name               IN  VARCHAR2,
dequeue_options          IN  dbms_aq.dequeue_options_t,
array_size               IN  BINARY_INTEGER,
message_properties_array OUT dbms_aq.message_properties_array_t,
payload_array            OUT sys.standard.<COLLECTION_1>,
msgid_array              OUT dbms_aq.msgid_array_t,
error_array              OUT dbms_aq.error_array_t)
RETURN PLS_INTEGER;
TBD
 
DEQUEUE_INTERNAL
Undocumented dbms_aq.aq$_delete_tiot_tab(
qname         IN VARCHAR2,
consumer_name IN VARCHAR2,
msgid         IN RAW);
TBD
 
DEQUEUE_INTERNAL_PAS
Undocumented dbms_aq.dequque_internal_pas;
exec dbms_aq.dequeue_internal_pas;
 
DISABLE_DEQUEUE_JOB
Undocumented dbms_aq.disable_dequeue_job(
job_name      IN VARCHAR2,
queue_name    IN VARCHAR2,
consumer_name IN VARCHAR2);
TBD
 
DROP_DEQUEUE_JOB
Undocumented dbms_aq.aq$_drop_dequeue_job(
job_name      IN VARCHAR2,
queue_name    IN VARCHAR2,
consumer_name IN VARCHAR2);
TBD
 
ENABLE_DEQUEUE_JOB
Undocumented dbms_aq.enable_dequeue_job(
job_name      IN VARCHAR2,
queue_name    IN VARCHAR2,
consumer_name IN VARCHAR2);
TBD
 
ENQUEUE (new 21c overload)
Adds a message to the specified queue

Overload 1		 dbms_aq.enqueue(
queue_name         IN  VARCHAR2,
enqueue_options    IN  dbms_aq.enqueue_options_t,
message_properties IN  dbms_aq.message_properties_t,
payload            IN  sys.standard.<OPAQUE_1>,
msgid              OUT RAW);
TBD
Overload 2 dbms_aq.enqueue(
queue_name         IN  VARCHAR2,
enqueue_options    IN  dbms_aq.enqueue_options_t,
message_properties IN  dbms_aq.message_properties_t,
payload            IN  sys.standard.<ADT_1>,
msgid              OUT RAW);
See AQ Demo 1 Linked At Page Bottom
Overload 3 dbms_aq.enqueue(
queue_name         IN  VARCHAR2,
enqueue_options    IN  dbms_aq.enqueue_options_t,
message_properties IN  dbms_aq.message_properties_t,
payload            IN  RAW,
msgid              OUT RAW);
See AQ Demo 1 Linked At Page Bottom
Overload 4 dbms_aq.enqueue(
queue_name         IN  VARCHAR2,
enqueue_options    IN  sys.dbms_aq.enqueue_options_t,
message_properties IN  sys.dbms_aq.message_properties_t,
payload            IN  JSON
msgid              OUT RAW);
TBD
 
ENQUEUE_ARRAY
Adds an array of messages to the specified queue

Overload 1		 dbms_aq.enqueue_array(
queue_name               IN  VARCHAR2,
enqueue_options          IN  dbms_aq.enqueue_options_t,
array_size               IN  PLS_INTEGER,
message_properties_array IN  dbms_aq.message_properties_array_t,
payload_array            IN  sys.standard.<COLLECTION_1>,
msgid_array              OUT dbms_aq.msgid_array_t)
RETURN PLS_INTEGER;
set serveroutput on

CREATE OR REPLACE TYPE message as OBJECT (
data VARCHAR2(10));
/

CREATE OR REPLACE TYPE message_tbl AS TABLE OF message;
/

DECLARE
 app1 sys.aq$_agent;
BEGIN
  dbms_aqadm.create_queue_table(queue_table => 'uw_q_tab',
  multiple_consumers => TRUE, queue_payload_type => 'message',
  compatible => '9.2.0.0.0');

  dbms_aqadm.create_queue('uw_q', 'uw_q_tab');

  dbms_aqadm.start_queue(queue_name => 'uw_q', dequeue => TRUE,
  enqueue => TRUE);

  app1 := sys.aq$_agent('sub1', NULL, NULL);
  dbms_aqadm.add_subscriber('uw_q',app1);
END;
/

DECLARE
 enqopt     dbms_aq.enqueue_options_t;
 msgproparr dbms_aq.message_properties_array_t;
 msgprop    dbms_aq.message_properties_t;
 payloadarr message_tbl;
 msgidarr   dbms_aq.msgid_array_t;
 retval     PLS_INTEGER;
BEGIN
  payloadarr := message_tbl(
  message('00000'),
  message('11111'),
  message('22222'),
  message('33333'),
  message('44444'),
  message('55555'),
  message('66666'),
  message('77777'),
  message('88888'),
  message('99999'));

  msgproparr := dbms_aq.message_properties_array_t(msgprop,
  msgprop, msgprop, msgprop, msgprop, msgprop, msgprop, msgprop,
  msgprop, msgprop);

  retval := dbms_aq.enqueue_array('UWCLASS.UW_Q', enqopt,
  10, msgproparr, payloadarr, msgidarr);

  COMMIT;

  dbms_output.put_line('Enqueued ' || retval || ' messages');

  FOR i IN 1..retval
  LOOP
    dbms_output.put_line('Message ' || i || ' payload: ' || payloadarr(i).data);
  END LOOP;
END;
/

CREATE OR REPLACE TYPE message_arr AS VARRAY(2000) OF message;
/

DECLARE
 deqopt dbms_aq.dequeue_options_t;

 msgproparr dbms_aq.message_properties_array_t := dbms_aq.message_properties_array_t();

 payloadarr message_arr := message_arr();
 msgidarr dbms_aq.msgid_array_t;
 retval PLS_INTEGER;
BEGIN
  payloadarr.extend(10);
  msgproparr.extend(10);
  deqopt.consumer_name := 'SUB1';

  retval := dbms_aq.dequeue_array( queue_name => 'UWCLASS.UW_Q',
  dequeue_options => deqopt, array_size => payloadarr.count,
  message_properties_array => msgproparr, payload_array =>
  payloadarr, msgid_array => msgidarr);

  COMMIT;

  dbms_output.put_line('Dequeued ' || retval || ' messages');

  FOR i IN 1..retval
  LOOP
    dbms_output.put_line ('Message ' || i || ' payload: ' || payloadarr(i).data);
  END LOOP;
END;
/

DECLARE
 app1 sys.aq$_agent;
BEGIN
  app1 := sys.aq$_agent('sub1', NULL, NULL);
  dbms_aqadm.remove_subscriber('uw_q',app1);

  dbms_aqadm.stop_queue( queue_name => 'uw_q');

  dbms_aqadm.drop_queue_table('uw_q_tab', TRUE);

  EXECUTE IMMEDIATE 'DROP TYPE message_tbl';
  EXECUTE IMMEDIATE 'DROP TYPE message';
END;
/
Overload 2 dbms_aq.enqueue_array(
queue_name               IN  VARCHAR2,
enqueue_options          IN  dbms_aq.enqueue_options_t,
array_size               IN  PLS_INTEGER,
message_properties_array IN  dbms_aq.message_properties_array_t,
payload_array            IN  sys.standard.<COLLECTION_1>,
msgid_array              OUT dbms_aq.msgid_array_t,
error_array              OUT dbms_aq.error_array_t)
RETURN PLS_INTEGER;
TBD
 
ENQUEUE_INT_SHARD
Undocumented

Overload 1		 dbms_aq.enqueue_int_shard(
qname           IN VARCHAR2,
visibility      IN BINARY_INTEGER,
delivery_mode   IN BINARY_INTEGER,
priority        IN BINARY_INTEGER,
delay           IN BINARY_INTEGER,
expiration      IN BINARY_INTEGER,
correlation     IN VARCHAR2,
exception_queue IN VARCHAR2,
delivery_time   IN TIMESTAMP WITH TIME ZONE,
old_msgid       IN RAW,
payload         IN RAW,
msgid           IN RAW,
enq_time        IN TIMESTAMP WITH TIME ZONE,
versionflg      IN BINARY_INTEGER,
delay_shard     IN BINARY_INTEGER);
TBD
Overload 2 dbms_aq.enqueue_int_shard(
qname           IN VARCHAR2,
visibility      IN BINARY_INTEGER,
delivery_mode   IN BINARY_INTEGER,
priority        IN BINARY_INTEGER,
delay           IN BINARY_INTEGER,
expiration      IN BINARY_INTEGER,
correlation     IN VARCHAR2,
exception_queue IN VARCHAR2,
delivery_time   IN TIMESTAMP WITH TIME ZONE,
old_msgid       IN RAW,
payload         IN BLOB,
msgid           IN RAW,
enq_time        IN TIMESTAMP WITH TIME ZONE,
versionflg      IN BINARY_INTEGER,
delay_shard     IN BINARY_INTEGER);
TBD
Overload 3 dbms_aq.enqueue_int_shard(
qname           IN VARCHAR2,
visibility      IN BINARY_INTEGER,
delivery_mode   IN BINARY_INTEGER,
priority        IN BINARY_INTEGER,
delay           IN BINARY_INTEGER,
expiration      IN BINARY_INTEGER,
correlation     IN VARCHAR2,
exception_queue IN VARCHAR2,
delivery_time   IN TIMESTAMP WITH TIME ZONE,
old_msgid       IN RAW,
payload         IN sys.standard.<ADT_1>,
msgid           IN RAW,
enq_time        IN TIMESTAMP WITH TIME ZONE,
versionflg      IN BINARY_INTEGER,
delay_shard     IN BINARY_INTEGER);
TBD
Overload 4 dbms_aq.enqueue_int_shard(zzz
 
TBD
 
ENQUEUE_INT_SHARD_JMS
Undocumented dbms_aq.enqueue_int_shard_jms(
qname              IN VARCHAR2,
visibility         IN BINARY_INTEGER,
delivery_mode      IN BINARY_INTEGER,
priority           IN BINARY_INTEGER,
delay              IN BINARY_INTEGER,
expiration         IN BINARY_INTEGER,
correlation        IN VARCHAR2,
exception_queue    IN VARCHAR2,
delivery_time      IN TIMESTAMP WITH TIME ZONE,
old_msgid          IN RAW,
jms_type           IN BINARY_INTEGER,
header_property    IN VARCHAR2,
user_property_text IN VARCHAR2,
user_property_clob IN CLOB,
payload_RAW        IN RAW,
payload_BLOB       IN BLOB,
msgid              IN RAW,
enq_time           IN TIMESTAMP WITH TIME ZONE,
versionflg         IN BINARY_INTEGER,
delay_shard        IN BINARY_INTEGER);
TBD
 
ENQUEUE_INT_UNSHARDED
Undocumented

Overload 1		 dbms_aq.enqueue_int_unsharded(
qname           IN VARCHAR2,
visibility      IN BINARY_INTEGER,
relative_msgid  IN RAW,
seq_deviation   IN BINARY_INTEGER,
delivery_mode   IN BINARY_INTEGER,
priority        IN BINARY_INTEGER,
delay           IN BINARY_INTEGER,
expiration      IN BINARY_INTEGER,
correlation     IN VARCHAR2,
exception_queue IN VARCHAR2,
sender_name     IN VARCHAR2,
sender_address  IN VARCHAR2,
sender_protocol IN BINARY_INTEGER,
original_msgid  IN RAW,
user_property   IN sys.anydata,
payload         IN sys.standard.<OPAQUE_1>,
recipients      IN sys.AQ$_recipients,
msgid           IN RAW,
enq_time        IN TIMESTAMP,
enq_uid         IN VARCHAR2,
step_no         IN BINARY_INTEGER);
TBD
Overload 2 dbms_aq.enqueue_int_unsharded(
qname           IN VARCHAR2,
visibility      IN BINARY_INTEGER,
relative_msgid  IN RAW,
seq_deviation   IN BINARY_INTEGER,
delivery_mode   IN BINARY_INTEGER,
priority        IN BINARY_INTEGER,
delay           IN BINARY_INTEGER,
expiration      IN BINARY_INTEGER,
correlation     IN VARCHAR2,
exception_queue IN VARCHAR2,
sender_name     IN VARCHAR2,
sender_address  IN VARCHAR2,
sender_protocol IN BINARY_INTEGER,
original_msgid  IN RAW,
user_property   IN sys.anydata,
payload         IN sys.standard.<ADT_1>,
recipients      IN VARRAY SYS aq$_recipients,
msgid           IN RAW,
enq_time        IN TIMESTAMP,
enq_uid         IN VARCHAR2,
step_no         IN BINARY_INTEGER);
TBD
Overload 3 dbms_aq.enqueue_int_unsharded(
qname           IN VARCHAR2,
visibility      IN BINARY_INTEGER,
relative_msgid  IN RAW,
seq_deviation   IN BINARY_INTEGER,
delivery_mode   IN BINARY_INTEGER,
priority        IN BINARY_INTEGER,
delay           IN BINARY_INTEGER,
expiration      IN BINARY_INTEGER,
correlation     IN VARCHAR2,
exception_queue IN VARCHAR2,
sender_name     IN VARCHAR2,
sender_address  IN VARCHAR2,
sender_protocol IN BINARY_INTEGER,
original_msgid  IN RAW,
user_property   IN sys.anydata,
payload         IN RAW,
recipients      IN sys.aq$_recipients,
msgid           IN RAW,
enq_time        IN TIMESTAMP,
enq_uid         IN VARCHAR2,
step_no         IN BINARY_INTEGER);
TBD
Overload 4 dbms_aq.enqueue_int_unsharded(
qname           IN VARCHAR2,
visibility      IN BINARY_INTEGER,
relative_msgid  IN RAW,
seq_deviation   IN BINARY_INTEGER,
delivery_mode   IN BINARY_INTEGER,
priority        IN BINARY_INTEGER,
delay           IN BINARY_INTEGER,
expiration      IN BINARY_INTEGER,
correlation     IN VARCHAR2,
exception_queue IN VARCHAR2,
sender_name     IN VARCHAR2,
sender_address  IN VARCHAR2,
sender_protocol IN BINARY_INTEGER,
original_msgid  IN RAW,
user_property   IN sys.anydata,
payload         IN BLOB,
recipients      IN sys.aq$recipients,
msgid           IN RAW,
enq_time        IN TIMESTAMP,
enq_uid         IN VARCHAR2,
step_no         IN BINARY_INTEGER);
TBD
 
ENQUEUE_INT_UNSHARDED_PAS
Undocumented dbms_aq.enquque_int_sharded_pas;
exec dbms_aq.enqueue_int_sharded_pas;
 
LISTEN
Listens on one or more queues on behalf of a list of agents

Overload 1		 dbms_aq.listen(
agent_list IN  dbms_aq.aq$_agent_list_t,
wait       IN  BINARY_INTEGER DEFAULT dbms_aq.forever,
agent      OUT sys.aq$_agent);
See AQ Demo 1 Linked At Page Bottom
Overload 2 dbms_aq.listen(
agent_list            IN  dbms_aq.aq$_agent_list_t,
wait                  IN  BINARY_INTEGER DEFAULT dbms_aq.forever,
listen_delivery_mode  IN  PLS_INTEGER DEFAULT dbms_aq.persistent,
agent                 OUT sys.aq$_agent,
message_delivery_mode OUT PLS_INTEGER);
See AQ Demo 1 Linked At Page Bottom
 
POST
Posts to a list of anonymous subscriptions that allows all clients registered for the subscriptions to get notifications dbms_aq.post(
post_list  IN sys.aq$_post_info_list,
post_count IN NUMBER);
TBD
 
REGISTER
Registers an email address, user-defined PL/SQL procedure, or HTTP URL for message notification dbms_aq.register(reg_list IN sys.aq$_reg_info_list, reg_count IN NUMBER);
DECLARE
 reginfo1 sys.aq$_reg_info;
 reginfo2 sys.aq$_reg_info;
 reginfo3 sys.aq$_reg_info;
 reginfo4 sys.aq$_reg_info;
 reginfo5 sys.aq$_reg_info;
 reginfo6 sys.aq$_reg_info;
 reginfolist sys.aq$_reg_info_list;
BEGIN
  -- register for p raw q default pres
  reginfo1 := sys.aq$_reg_info('PUBSUB1.EVENTS:ADMIN', 1,
  'plsql://plsqlregproc1',HEXTORAW('FF'));

  -- register for p raw q xml pres
  reginfo2 := sys.aq$_reg_info('PUBSUB1.EVENTS:ADMIN', 1,
  'plsql://plsqlregproc1?PR=1',HEXTORAW('FF'));

  -- register for p ADT q default pres
  reginfo3 := sys.aq$_reg_info('PUBSUB1.ADTEVENTS:ADMIN', 1,
  'plsql://plsqlregproc2',HEXTORAW('FF'));

  -- register for p ADT q xml pres
  reginfo4 := sys.aq$_reg_info('PUBSUB1.ADTEVENTS:ADMIN', 1,
  'plsql://plsqlregproc2?PR=1',HEXTORAW('FF'));

  -- for np q raw and adt can be enqueued into the same queue
  -- register for np raw and ADT q default pres
  reginfo5 := sys.aq$_reg_info('PUBSUB1.NONPEREVENTS:ADMIN', 1,
  'plsql://plsqlregproc1',HEXTORAW('FF'));

  -- register for np raw and ADT q xml pres
  reginfo6 := sys.aq$_reg_info('PUBSUB1.NONPEREVENTS:ADMIN', 1,
  'plsql://plsqlregproc2?PR=1',HEXTORAW('FF'));

  reginfolist := sys.aq$_reg_info_list(reginfo1);
  reginfolist.EXTEND;
  reginfolist(2) := reginfo2;
  reginfolist.EXTEND;
  reginfolist(3) := reginfo3;
  reginfolist.EXTEND;
  reginfolist(4) := reginfo4;
  reginfolist.EXTEND;
  reginfolist(5) := reginfo5;
  reginfolist.EXTEND;
  reginfolist(6) := reginfo6;

  dbms_aq.register(reginfolist, 6);

  COMMIT;
END;
/
 
REGISTRATION_REPLICATION
Undocumented dbms_aq.registration_replication(
opcode             IN BINARY_INTEGER,
regid              IN NUMBER,
subname            IN VARCHAR2,
location           IN VARCHAR2,
usr                IN VARCHAR2,
uctx               IN RAW,
ns                 IN BINARY_INTEGER,
prest              IN BINARY_INTEGER,
version            IN BINARY_INTEGER,
status             IN BINARY_INTEGER,
uctxtype           IN BINARY_INTEGER,
qosflgs            IN BINARY_INTEGER,
paycbk             IN VARCHAR2,
timeout            IN BINARY_INTEGER,
regtime            IN TIMESTAMP WITH TIME ZONE,
ntfngrpclass       IN BINARY_INTEGER,
ntfngrpvalue       IN BINARY_INTEGER,
ntfngrptype        IN BINARY_INTEGER,
ntfngrpstarttime   IN TIMESTAMP WITH TIME ZONE,
ntfngrprepeatcount IN BINARY_INTEGER,
grpnfy             IN BINARY_INTEGER,
osesskey           IN RAW);
TBD
 
SEEK (new 21c)
Undocumented dbms_aq.seek(
queue_name        IN  VARCHAR2,
consumer_name     IN  VARCHAR2,
seek_input_array  IN  dbms_aq.seek_input_array_t,
skip_option       IN  BINARY_INTEGER,
redequeue_all     IN  BOOLEAN,
seek_output_array OUT dbms_aq.seek_output_array_t);
TBD
 
UNBIND_AGENT
Removes the entry for an AQ agent from the LDAP server dbms_aq.unbind_agent(agent IN sys.aq$_agent);
TBD
 
UNREGISTER
Unregisters a subscription which turns off notifications dbms_aq.unregister(
reg_list  IN sys.aq$_reg_info_list,
reg_count IN NUMBER);
DECLARE
 reginfo1 sys.aq$_reg_info;
 reginfo2 sys.aq$_reg_info;
 reginfo3 sys.aq$_reg_info;
 reginfo4 sys.aq$_reg_info;
 reginfo5 sys.aq$_reg_info;
 reginfo6 sys.aq$_reg_info;
 reginfolist sys.aq$_reg_info_list;
BEGIN
  -- register for p raw q default pres
  reginfo1 := sys.aq$_reg_info('PUBSUB1.EVENTS:ADMIN', 1,
  'plsql://plsqlregproc1',HEXTORAW('FF'));

  -- register for p raw q xml pres
  reginfo2 := sys.aq$_reg_info('PUBSUB1.EVENTS:ADMIN', 1,
  'plsql://plsqlregproc1?PR=1',HEXTORAW('FF'));

  -- register for p ADT q default pres
  reginfo3 := sys.aq$_reg_info('PUBSUB1.ADTEVENTS:ADMIN', 1,
  'plsql://plsqlregproc2',HEXTORAW('FF'));

  -- register for p ADT q xml pres
  reginfo4 := sys.aq$_reg_info('PUBSUB1.ADTEVENTS:ADMIN', 1,
  'plsql://plsqlregproc2?PR=1',HEXTORAW('FF'));

  -- for np q raw and ADT can be enqueued into the same queue
  -- register for np raw and adt q default pres
  reginfo5 := sys.aq$_reg_info('PUBSUB1.NONPEREVENTS:ADMIN', 1,
  'plsql://plsqlregproc1',HEXTORAW('FF'));

  -- register for np raw and ADT q xml pres
  reginfo6 := sys.aq$_reg_info('PUBSUB1.NONPEREVENTS:ADMIN', 1,
  'plsql://plsqlregproc2?PR=1',HEXTORAW('FF'));

  reginfolist := sys.aq$_reg_info_list(reginfo1);
  reginfolist.EXTEND;
  reginfolist(2) := reginfo2;
  reginfolist.EXTEND;
  reginfolist(3) := reginfo3;
  reginfolist.EXTEND;
  reginfolist(4) := reginfo4;
  reginfolist.EXTEND;
  reginfolist(5) := reginfo5;
  reginfolist.EXTEND;
  reginfolist(6) := reginfo6;

  dbms_aq.unregister(reginfolist, 6);

  COMMIT;
END;
/

Related Topics
Advanced Queuing Demo 1
Advanced Queuing RAC Demo
Built-in Functions
Built-in Packages
DBMS_ALERT
DBMS_AQADM
DBMS_AQADM_INV
DBMS_AQADM_VAR
DBMS_AQELM
DBMS_AQIN
DBMS_AQ_BQVIEW
DBMS_AQ_EXP_CMT_TIME_TABLES
DBMS_AQ_EXP_DEQUEUELOG_TABLES
DBMS_AQ_EXP_HISTORY_TABLES
DBMS_AQ_EXP_INDEX_TABLES
DBMS_AQ_EXP_QUEUE_TABLES
DBMS_AQ_EXP_TIMEMGR_TABLES
DBMS_AQ_EXP_ZECURITY
DBMS_AQ_IMPORT_INTERNAL
DBMS_AQ_IMP_ZECURITY
DBMS_AQ_IND_MON
DBMS_AQ_INV
DBMS_AQ_SUB
DBMS_AQ_SYS_EXP_ACTIONS
DBMS_AQ_SYS_EXP_INTERNAL
DBMS_AQ_SYS_IMP_INTERNAL
DBMS_JOB
DBMS_PRVTAQIP
DBMS_SERVER_ALERT
DBMS_TEQK
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx