Package gb_advq

Package gb_advq_util

Oracle AQ utility package that wraps the dbms_aqadm functionality and adds convenience procedures/functions related to AQ.

Program units

p_enqueue_message	Enqueues an xml message.
p_create_queue	Creates AQ queue/topic.
p_add_subscriber	Adds a subscriber to a Destination(Queue/Topic).
p_remove_subscriber	Remove a subscriber associated with a Queue/Topic.
p_restart_queue	Restarts a Queue/Topic.
p_drop_queue	Drops a Queue/Topic and the underlying Queue table.
p_drop_queue_table	Drop the queue table and force all queues to be stopped and dropped by the system.
p_display_subscribers	Displays subscribers associated with a Destination (Queue/Topic).
p_purge_messages	Purge messages associated to a Destination (Queue/Topic) for a subscriber.
p_display_messages	Display message header info for a particular queue.
p_enqueue_msg_fragments	Enqueues a g_msg_fragments message to AQ single consumer queue.
p_dequeue_msg_fragments	Dequeues a g_msg_fragments message from AQ single consumer queue.
p_dequeue_msg_fragments_condit	Dequeues a g_msg_fragments message conditionally from AQ single consumer queue.
P_PURGE_ENTIRE_QUEUE	Purge the g_msg_fragments messages in single consumer Queue
P_HANDSHAKE_MSG_FRAGMENTS_ENQ	To handle the passoff of g_msg_fragments from Oracle*Form to enqueue procedure for AQ single consumer queue
P_HANDSHAKE_MSG_FRAGMENTS_DEQ1	Handle the passoff of g_msg_fragments from conditional dequeue procedure for AQ single consumer queue into Oracle*Form.
f_get_mf_misc_01_value	Returns the mf_misc_01 value of type VARCHAR2 during conditional dequeue
f_queue_exists	Checks whether a Destination (Queue/Topic) exists.
f_queue_table_exists	Checks whether a Queue/Topic table exists.
f_queue_enabled_for_purge	Returns True if the passed Queue Name is available for dequeuing
f_get_message_count	Returns the number of messages for a particular destination.
F_USE_AQ_AND_NOT_PIPES	Based on GTVSDAX returns Y or N.
F_DBA_QUEUE_TABLE_EXISTS	Returns True if the passed Queue Table has been established
F_COUNT_QTABLE_ROWS	Returns number of g_msg_fragments message for a given Queue Table
F_GET_UNIQUE_TOKEN	Returns a unique identification for use with conditional dequeunig

p_enqueue_message

PROCEDURE p_enqueue_message(p_xml                    CLOB,
                            p_bulk_synchronization   BOOLEAN,
                            p_bulk_sync_code         VARCHAR2,
                            p_entity_list            string_nt,
                            p_vpd_inst_code          VARCHAR2,
                            p_sync_publisher_enabled BOOLEAN,
                            p_message_id             NUMBER)

Enqueues an xml message.

Parameters

	p_xml	Business entity data to be enqueued
	p_bulk_synchronization	Indicates if entities are being synchronized in bulk
	p_bulk_sync_code	Bulk Synchronization code that is the target system name
	p_entity_list	Unique list of busines entities that are contained in the xml
	p_vpd_inst_code	VPD Institution code
	p_sync_publisher_enabled	Indicator determining if entities need to be sync'd/published by the gateway

p_create_queue

PROCEDURE p_create_queue(p_queue_table        IN VARCHAR2,
                         p_payload_type       IN VARCHAR2,
                         p_queue_name         IN VARCHAR2,
                         p_queue_type         IN INTEGER := dbms_aqadm.normal_queue,
                         p_multiple_consumers IN BOOLEAN)

Creates AQ queue/topic.

Parameters

	p_queue_table	Name of table that holds the Queue/Topic data
	p_queue_table	Payload that the Queue/Topic holds
	p_queue_name	Queue Name
	p_queue_type	Queue type (Noraml/Exception)
	p_multiple_consumers	Indicator to differentiate between a Queue/Topic

p_add_subscriber

PROCEDURE p_add_subscriber(p_queue_name      IN VARCHAR2,
                           p_subscriber_name IN VARCHAR2)

Adds a subscriber to a Destination(Queue/Topic).

Parameters

	p_queue_name	Queue to which a subscriber is being added
	p_subscriber_name	Name of Subscriber being added

p_remove_subscriber

PROCEDURE p_remove_subscriber(p_queue_name      IN VARCHAR2,
                              p_subscriber_name IN VARCHAR2)

Remove a subscriber associated with a Queue/Topic.

Parameters

	p_queue_name	Queue/Topic whose subscriber is being removed
	p_subscriber_name	Name of subscriber being removed.

p_restart_queue

PROCEDURE p_restart_queue(p_queue_name IN VARCHAR2,
                          p_wait       IN BOOLEAN := TRUE)

Restarts a Queue/Topic.

Parameters

	p_queue_name	Queue/Topic to restart
	p_wait	Indicator to determine whether to wait for any outstanding transactions to complete.

p_drop_queue

PROCEDURE p_drop_queue(p_queue_table IN VARCHAR2,
                       p_queue_name  IN VARCHAR2 := '%',
                       p_enqueue     IN BOOLEAN := TRUE,
                       p_dequeue     IN BOOLEAN := TRUE,
                       p_wait        IN BOOLEAN := TRUE)

Drops a Queue/Topic and the underlying Queue table. Dropping the Queue table will delete all messages from the Queue table.

Parameters

	p_queue_table	Queue table that holds the Queue/Topic data
	p_queue_name	Queue/Topic name
	p_enqueue	Indicator to disable Queueing on this queue
	p_dequeue	Indicator to disable DeQueueing on this queue
	p_wait	Indicator to determine whether to wait for any outstanding transactions to complete

p_drop_queue_table

PROCEDURE p_drop_queue_table(p_queue_table IN VARCHAR2,
                             p_force       IN BOOLEAN := FALSE,
                             p_auto_commit IN BOOLEAN := TRUE)

Drop the queue table and force all queues to be stopped and dropped by the system. This will delete all the messages from the queue table.

Parameters

p_queue_table

Queue/Topic table that holds the Queue/Topic data

p_force

Controls dropping of Queue table.

	FALSE	Drop action will not succeed unless all queues associated with this Queue table are dropped
	TRUE	Force drop this queue table. Remaining queues will be automatically stopped and dropped.

p_auto_commit

Commit control.

	FALSE	Commits the current transaction before the operation is carried out. Operation becomes persistent when the call returns.
	TRUE	Force Drop action becomes part of the current transaction thereby taking affect only when calling session issues a commit.

p_display_subscribers

PROCEDURE p_display_subscribers(p_queue_name IN VARCHAR2)

Displays subscribers associated with a Destination (Queue/Topic).

Parameters

p_queue_name

Name of Queue

p_purge_messages

PROCEDURE p_purge_messages(p_queue      VARCHAR2,
                           p_subscriber VARCHAR2)

Purge messages associated to a Destination (Queue/Topic) for a subscriber.

Parameters

	p_queue_name	Name of Queue
	p_subscriber	Name of Subscriber

p_display_messages

PROCEDURE p_display_messages(p_queue_table IN VARCHAR2,
                             p_queue_name  IN VARCHAR2)

Display message header info for a particular queue.

Parameters

	p_queue_table	Queue table name
	p_queue_name	Name of Queue

p_enqueue_msg_fragments

PROCEDURE p_enqueue_msg_fragments(p_queue_name    IN VARCHAR2,
                                  p_msg_fragments IN g_msg_fragments,
                                  p_delivery_mode IN NUMBER := 1)

Enqueues a g_msg_fragments message to AQ single consumer queue.

Parameters

	p_queue_name	Name of the Queue
	p_msg_fragments	contains the payload of message fragments to enqueue
	p_delivery_mode	indicates a PERSISTENT or BUFFERED message. Persistent is default.

p_dequeue_msg_fragments

PROCEDURE p_dequeue_msg_fragments(p_queue_name    IN VARCHAR2,
                                  p_max_wait      IN NUMBER,
                                  p_msg_fragments OUT g_msg_fragments,
                                  p_remove_nodata IN VARCHAR2 DEFAULT 'N')

Dequeues a g_msg_fragments message from AQ single consumer queue.

Parameters

	p_queue_name	Name of the Queue
	p_max_wait	if not null, contains number in seconds to listen for a message to dequeue
	p_msg_fragments	contains the payload of message fragments dequeued
	p_remove_nodata	default is 'N', if 'Y' will set dequeue_mode to remove without need for payload

p_dequeue_msg_fragments_condit

PROCEDURE p_dequeue_msg_fragments_condit(p_queue_name    IN VARCHAR2,
                                         p_condit_value  IN VARCHAR2,
                                         p_max_wait      IN NUMBER,
                                         p_wait_factor   IN NUMBER,
                                         p_msg_fragments OUT g_msg_fragments)

Dequeues a g_msg_fragments message conditionally from AQ single consumer queue.

Parameters

	p_queue_name	Name of the Queue
	p_condit_value	string value evaluated against g_msg_fragments.mf_misc_01 value.
	p_max_wait	seconds (multiplied by a factor, p_wait_factor) to conditionally listen for a message to dequeue
	p_wait_factor	with p_max_wait seconds to establish duration to conditionally listen for a message to dequeue
	p_msg_fragments	contains the payload of message fragments dequeued

P_PURGE_ENTIRE_QUEUE

PROCEDURE p_purge_entire_queue(p_queue_name     IN VARCHAR2,
                               p_items_in_queue IN NUMBER)

Purge the g_msg_fragments messages in single consumer Queue

Parameters

	p_queue_name	Name of the Queue
	p_items_in_queue	indicates number of messages in the Queue and how many to purge

P_HANDSHAKE_MSG_FRAGMENTS_ENQ

PROCEDURE p_handshake_msg_fragments_enq(p_queue_name    IN VARCHAR2,
                                        p_return_status OUT VARCHAR2,
                                        p_mf_misc_01    IN VARCHAR2,
                                        p_mf_01         IN sys.anydata,
                                        p_mf_02         IN sys.anydata DEFAULT NULL,
                                        p_mf_03         IN sys.anydata DEFAULT NULL,
                                        p_mf_04         IN sys.anydata DEFAULT NULL,
                                        p_mf_05         IN sys.anydata DEFAULT NULL,
                                        p_mf_06         IN sys.anydata DEFAULT NULL,
                                        p_mf_07         IN sys.anydata DEFAULT NULL,
                                        p_mf_08         IN sys.anydata DEFAULT NULL,
                                        p_mf_09         IN sys.anydata DEFAULT NULL,
                                        p_mf_10         IN sys.anydata DEFAULT NULL)

To handle the passoff of g_msg_fragments from Oracle*Form to enqueue procedure for AQ single consumer queue

Parameters

	p_queue_name	Name of the Queue
	p_return_status	Status of the execution of this procedure. A '0' indicates success.
	p_mf_misc_01	miscellanous message fragment
	p_mf_01	message fragment 01
	p_mf_02	message fragment 02
	p_mf_03	message fragment 03
	p_mf_04	message fragment 04
	p_mf_05	message fragment 05
	p_mf_06	message fragment 06
	p_mf_07	message fragment 07
	p_mf_08	message fragment 08
	p_mf_09	message fragment 09
	p_mf_10	message fragment 10

P_HANDSHAKE_MSG_FRAGMENTS_DEQ1

PROCEDURE p_handshake_msg_fragments_deq1(p_queue_name    IN VARCHAR2,
                                         p_condit        IN VARCHAR2,
                                         p_max_wait      IN NUMBER,
                                         p_wait_factor   IN NUMBER,
                                         p_return_status OUT VARCHAR2,
                                         p_response      OUT VARCHAR2)

Handle the passoff of g_msg_fragments from conditional dequeue procedure for AQ single consumer queue into Oracle*Form. This is specific to GURJOBS_RTN_Q handshake

Parameters

	p_queue_name	Name of the Queue
	p_condit	condition value that is compared to mf_misc_01 for dequeue
	p_max_wait	seconds (multiplied by a factor, p_wait_factor) to conditionally listen for a message to dequeue
	p_wait_factor	with p_max_wait seconds to establish duration to conditionally listen for a message to dequeue
	p_return_status	Status of the execution of this procedure. A '0' indicates success.
	p_response	for gurjobs_rtn_q this is from p_mf_misc_01 message fragment

f_get_mf_misc_01_value

FUNCTION f_get_mf_misc_01_value(p_msg_fragments IN g_msg_fragments)
  RETURN VARCHAR2

Returns the mf_misc_01 value of type VARCHAR2 during conditional dequeue

Parameters

p_msg_fragments

item from the Queue user_data, payload

f_queue_exists

FUNCTION f_queue_exists(p_queue_name IN VARCHAR2) RETURN BOOLEAN

Checks whether a Destination (Queue/Topic) exists.

Parameters

p_queue_name

Name of Queue

f_queue_table_exists

FUNCTION f_queue_table_exists(p_queue_table IN VARCHAR2) RETURN BOOLEAN

Checks whether a Queue/Topic table exists.

Parameters

p_queue_table

Name of Queue

f_queue_enabled_for_purge

FUNCTION f_queue_enabled_for_purge(p_queue_name IN VARCHAR2) RETURN BOOLEAN

Returns True if the passed Queue Name is available for dequeuing

Parameters

p_queue_name

Name of Queue

f_get_message_count

FUNCTION f_get_message_count(p_queue_table IN VARCHAR2,
                             p_queue_name  IN VARCHAR2) RETURN INTEGER

Returns the number of messages for a particular destination.

Parameters

	p_queue_table	Name of Queue table that holds data for a Destination (Queue/Topic)
	p_queue_name	Name of Queue

F_USE_AQ_AND_NOT_PIPES

FUNCTION f_use_aq_and_not_pipes(p_code       IN VARCHAR2,
                                p_group_code IN VARCHAR2) RETURN BOOLEAN

Based on GTVSDAX returns Y or N. Y indicates to use AQ alternative communication mechanism. N indicates to continue to use DBMS_PIPE. N is returned if the row is not found. If database 11g (or higher) is being used, function will pull the return value from the result cache

Parameters

	p_code	value for GTVSDAX code lookup (AQ4PIPES)
	p_group_code	value for GTVSDAX group code (GURJOBS,SSO,GOKOUTD,GOKOUTP)

F_DBA_QUEUE_TABLE_EXISTS

FUNCTION f_dba_queue_table_exists(p_queue_table IN VARCHAR2) RETURN BOOLEAN

Returns True if the passed Queue Table has been established

Parameters

p_queue_table

Name of the Queue Table

F_COUNT_QTABLE_ROWS

FUNCTION f_count_qtable_rows(p_queue_table IN VARCHAR2,
                             p_query_str   OUT VARCHAR2) RETURN NUMBER

Returns number of g_msg_fragments message for a given Queue Table

Parameters

	p_queue_table	Name of the Queue Table
	p_query_str	constructed query string. established for debug need.

F_GET_UNIQUE_TOKEN

FUNCTION f_get_unique_token RETURN VARCHAR2

Returns a unique identification for use with conditional dequeunig