The
dbms_aqadm package supports configuring and
managing Oracle Stream Advanced Queuing such as
Queue Tables, commonly referred to as AQ. This
package and some OEM screens/utilities built on
top of it are the only interfaces for these
types of objects. There are no native DDL
commands for them, so there is no such thing as
CREATE TABLE for queue tables. One must use this
package. Dbms_aqadm is a rather large
and complex PL/SQL package. Thus many DBAs may
simply prefer to use the OEM screens for
managing queues.
There are a few enumerated
constants that one must know to use this
package:
-- retention
INFINITE
CONSTANT BINARY_INTEGER := -1;
-- message grouping
TRANSACTIONAL
CONSTANT BINARY_INTEGER := 1;
NONE
CONSTANT BINARY_INTEGER := 0;
-- queue type
NORMAL_QUEUE
CONSTANT BINARY_INTEGER :=
0;
EXCEPTION_QUEUE
CONSTANT BINARY_INTEGER := 1;
NON_PERSISTENT_QUEUE
CONSTANT BINARY_INTEGER := 2;
-- non-repudiation
properties
NON_REPUDIATE_SENDER
CONSTANT BINARY_INTEGER := 1;
NON_REPUDIATE_SNDRCV
CONSTANT BINARY_INTEGER := 2;
-- protocols (note that
FTP is not supported yet so it is not part
of anyp).
TTC
CONSTANT BINARY_INTEGER := 0;
HTTP
CONSTANT BINARY_INTEGER := 1;
SMTP
CONSTANT BINARY_INTEGER := 2;
FTP
CONSTANT BINARY_INTEGER := 4;
ANYP
CONSTANT BINARY_INTEGER := HTTP +
SMTP;
LOGMINER_PROTOCOL
CONSTANT BINARY_INTEGER := 1;
LOGAPPLY_PROTOCOL
CONSTANT BINARY_INTEGER := 2;
TEST_PROTOCOL
CONSTANT BINARY_INTEGER := 3;
-- Constants for LDAP
connection factory type
AQ_QUEUE_CONNECTION
CONSTANT BINARY_INTEGER := 1;
AQ_TOPIC_CONNECTION
CONSTANT BINARY_INTEGER := 2;
-- Constants for delivery
mode
PERSISTENT
CONSTANT BINARY_INTEGER := 1 ;
BUFFERED
CONSTANT BINARY_INTEGER := 2 ;
PERSISTENT_OR_BUFFERED
CONSTANT BINARY_INTEGER := 3 ;
-- subscriber properties.
QUEUE_TO_QUEUE_SUBSCRIBER
CONSTANT BINARY_INTEGER := 8;
-- Constants for get/set_replay_info
LAST_ENQUEUED
CONSTANT BINARY_INTEGER := 0;
LAST_ACKNOWLEDGED
CONSTANT BINARY_INTEGER := 1;
Add_alias_to_ldap is a procedure that
creates an alias to an object like queue, agent
or connection factory which is placed under the
database in the LDAP hierarchy. Of course, the
object must exist prior to creating an alias for
it.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
ALIAS
|
VARCHAR2
|
IN
|
|
|
OBJ_LOCATION
|
VARCHAR2
|
IN
|
|
Table 6.59:
Add_alias_to_ldap
Parameters
Add_subscriber is a procedure that adds a
subscriber to queue. The rule parameter
is a conditional expression string much like a
WHERE clause and must be properly (double)
quoted.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
QUEUE_NAME
|
VARCHAR2
|
IN
|
|
|
SUBSCRIBER
|
SYS.AQ$_AGENT
|
IN
|
|
|
RULE
|
VARCHAR2
|
IN
|
NULL
|
|
TRANSFORMATION
|
VARCHAR2
|
IN
|
NULL
|
|
QUEUE_TO_QUEUE
|
BOOELAN
|
IN
|
FALSE
|
|
DELIVERY_MODE
|
PLS_INTEGER
|
IN
|
DBMS_AQADM.PERSISTENT
|
Table 6.60:
Add_subscriber Parameters
Alter_aq_agent is a procedure that modifies
an AQ agent’s registration for internet access;
however, for access through HTTP, the
certificate location is not required.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
AGENT_NAME
|
VARCHAR2
|
IN
|
|
|
CERTIFICATE_LOCATION
|
VARCHAR2
|
IN
|
NULL
|
|
ENABLE_HTTP
|
BOOLEAN
|
IN
|
FALSE
|
|
ENABLE_SMTP
|
BOOLEAN
|
IN
|
FALSE
|
|
ENABLE_ANYP
|
BOOELAN
|
IN
|
FALSE
|
Table 6.61:
Alter_aq_agent Parameters
Alter_propagation_schedule is a procedure
used to alter the parameters of a propagation
schedule. If the
destination parameter for the destination
database link is NULL, then the destination is
the local database and all messages are
propagated to other local database queues.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
QUEUE_NAME
|
VARCHAR2
|
IN
|
|
|
DESTINATION
|
VARCHAR2
|
IN
|
NULL
|
|
DURATION
|
NUMBER
|
IN
|
NULL
|
|
NEXT_TIME
|
VARCHAR2
|
IN
|
NULL
|
|
LATENCY
|
NUMBER
|
IN
|
60
|
|
DESTINATION_QUEUE
|
VARCHAR2
|
IN
|
NULL
|
Table 6:62:
Alter_propagation_schedule
Alter_queue is a procedure that modifies a
queue’s properties. Note that a message is
automatically transferred to an exception queue
if
retry_count > Max_retries.
RETRY_COUNT.
Also note that the
auto_commit
parameter has been deprecated.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
QUEUE_NAME
|
VARCHAR2
|
IN
|
|
|
MAX_RETRIES
|
NUMBER
|
IN
|
NULL
|
|
RETRY_DELAY
|
NUMBER
|
IN
|
NULL
|
|
RETENTION_TIME
|
NUMBER
|
IN
|
NULL
|
|
AUTO_COMMIT
|
BOOLEAN
|
IN
|
TRUE
|
|
COMMENT
|
VARCHAR2
|
IN
|
NULL
|
Table 6.63:
Alter_queue Parameters
Alter_queue_table is a procedure that
modifies a queue table’s properties. The primary
instance performs the monitor scheduling and
propagation for the queues in the queue table.
The secondary instance is strictly for failover,
i.e. when the primary instance is unavailable.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
QUEUE_TABLE
|
VARCHAR2
|
IN
|
|
|
COMMENT
|
VARCHAR2
|
IN
|
NULL
|
|
PRIMARY_INSTACNE
|
BINARY_INTEGER
|
IN
|
NULL
|
|
SECONDARY_INSTANCE
|
BINARY_INTEGER
|
IN
|
NULL
|
Table 6.64:
Alter_queue_table Parameters
