Oracle DBMS_AQ
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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 19c
What's New In 20c-21c

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-2021 Daniel A. Morgan All Rights Reserved
  DBSecWorx