Rem 
Rem $Header: dbmsaqad.sql 27-nov-98.00:29:02 arsaxena Exp $
Rem
Rem dbmsaqad.sql
Rem 
Rem  Copyright (c) Oracle Corporation 1996, 1997, 1998. All Rights Reserved.
Rem
Rem    NAME
Rem      dbmsaqad.sql - package which contains the administrative
Rem                     operations of AQ
Rem
Rem    DESCRIPTION
Rem      This file contains the dbms_aqadm package.
Rem
Rem    NOTES
Rem      
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    arsaxena    11/27/98 - add verify_queue_types_no_queue
Rem    ryaseen     11/20/98 - split dbms_aqadm
Rem    ato         10/21/98 - move dbms_aq_import_internal out                 
Rem    schandra    08/14/98 - default max_retries to 5
Rem    ato         07/01/98 - support altering comments in alter_queue
Rem    ryaseen     06/29/98 - add temp functions for rules export/import
Rem    nbhatt      06/02/98 - remove overloaded create_queue_table
Rem    mkamath     06/10/98 - overloading aq$_propaq for upgrade/downgrade
Rem    ato         06/07/98 - move all grants to catqueue                      
Rem    ato         06/03/98 - don't grant dbms_aqadm to PUBLIC                 
Rem    ato         05/08/98 - move create roles out
Rem    ato         05/04/98 - add grant/revoke
Rem    ryaseen     05/07/98 - add alter subscriber                             
Rem    ato         04/30/98 - remove obsoleted functions
Rem    schandra    04/15/98 - move recover propagation
Rem    arsaxena    04/28/98 - add non_persistent_q support
Rem    mkamath     04/26/98 - support for enable, disable and alter schedule
Rem    mkamath     04/26/98 - Moving prop_queue procedures to prvtaqip
Rem    mkamath     04/17/98 - Adding procedure prop_job_submit
Rem    ryaseen     04/22/98 - add rule to add_subscriber call                  
Rem    schandra    04/06/98 - grant select on aq_propagation_status to aq admin
Rem    schandra    02/16/98 - add compatilibity parameter to create_queue_table
Rem    mkamath     04/06/98 - Adding procedures for new scheduling algorithm
Rem    arsaxena    11/09/97 - change propaq to return the next date;
Rem    ato         08/20/97 - grant DBMS_AQ and DBMS_AQADM to execute_catalog_r
Rem    arsaxena    08/07/97 - change default values for propagation
Rem    nbhatt      08/13/97 -  grant v$_aq_statistics to admin
Rem    nbhatt      08/12/97 - grant select on v$aq_statistics & gv$aq_statistic
Rem    nbhatt      07/22/97 - code review changes
Rem    hrizvi      07/23/97 - dont grant package permissions to public
Rem    arsaxena    07/22/97 - Add scheduling functions for propagation
Rem    ato         05/01/97 - remove test_ac_ddl
Rem    schandra    04/29/97 - add types for displaying dequeue history
Rem    nbhatt      04/26/97 - add start_time_manager and stop_time_manager
Rem    ato         04/23/97 - recreate user view after import
Rem    ato         04/22/97 - add dbms_aq_import_internal to aqadm role
Rem    nbhatt      04/22/97 - change recipient->consumer
Rem    nbhatt      04/21/97 - restore the tracking option in create_queue as de
Rem    nbhatt      04/20/97 - final interface change
Rem    esoyleme    04/24/97 - add iot imp/exp
Rem    schandra    04/14/97 - import/export for multiple dequeuers
Rem    nbhatt      04/10/97 - make recipient enumerated type
Rem    ato         03/31/97 - add raw type queue in create_qtable
Rem    schandra    03/25/97 - support grouping by tansaction
Rem    schandra    03/11/97 - use aq_agent in admin interface
Rem    schandra    02/17/97 - add multiple dequeues clause to create_qtable
Rem    ato         04/04/97 - support for autocommit parameter
Rem    nbhatt      03/31/97 - change interface
Rem    ato         03/21/97 - remove object_type_format from create_qtable
Rem    ato         03/21/97 - remove lob_storage and lob_tspace from create_qta
Rem    ato         11/13/96 - add aq_user_role
Rem    pshah       11/07/96 - Changing a create_qtable() 
Rem                           parameter [q_adt => q_object_type]
Rem    pshah       10/21/96 - Grant execute priveleges
Rem    pshah       09/27/96 - Removing roles
Rem    ato         09/26/96 - remove grant execute any type
Rem    ato         09/25/96 - remove test_ac_ddl
Rem    pshah       09/23/96 - Adding test_ac_ddl()
Rem    ato         09/03/96 - create aq_administrator and user roles
Rem    ato         09/03/96 - add synonyms
Rem    pshah       08/28/96 - Adding parameters (ret_time and retention)
Rem                           to alter_q()
Rem    pshah       08/26/96 - Adding logic to create_q() for message retention
Rem    ato         08/20/96 - typed queues
Rem    ato         08/15/96 - add typed queues
Rem    ato         07/29/96 - add aq_import_internal
Rem    pshah       07/10/96 - Adding trusted callout procedure kwqaoprg
Rem    pshah       06/17/96 - Adding lob_storage parameter to create_qtable
Rem    pshah       06/06/96 - Adding an extra parameter to create_q()
Rem    pshah       04/23/96 - Changing 'queue' to 'q'
Rem    pshah       04/14/96 - Interating all admin functions
Rem    pshah       02/13/96 - Administrative Interface definition
Rem    pshah       02/13/96 - Created
Rem
 
CREATE OR REPLACE PACKAGE dbms_aqadm AS

  --------------------
  --  PUBLIC CONSTANT
  --
  -- payload type of the queue table

  -- 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;


  TYPE aq$_subscriber_list_t IS TABLE OF sys.aq$_agent
        INDEX BY BINARY_INTEGER;

  PROCEDURE create_queue_table(
        queue_table                 IN     VARCHAR2,
        queue_payload_type          IN     VARCHAR2,
        storage_clause              IN     VARCHAR2 DEFAULT NULL,
        sort_list                   IN     VARCHAR2 DEFAULT NULL,
	multiple_consumers          IN     BOOLEAN DEFAULT FALSE,
        message_grouping	    IN     BINARY_INTEGER DEFAULT NONE,
        comment            	    IN     VARCHAR2 DEFAULT NULL,
        auto_commit                 IN     BOOLEAN  DEFAULT TRUE,
        primary_instance            IN     BINARY_INTEGER DEFAULT 0,
        secondary_instance          IN     BINARY_INTEGER DEFAULT 0,
	compatible		    IN	   VARCHAR2 DEFAULT NULL);


  PROCEDURE alter_queue_table(
        queue_table                 IN     VARCHAR2,
	comment			    IN     VARCHAR2       DEFAULT NULL,
        primary_instance            IN     BINARY_INTEGER DEFAULT NULL,
        secondary_instance          IN     BINARY_INTEGER DEFAULT NULL); 

  PROCEDURE create_queue(
        queue_name                  IN     VARCHAR2,
        queue_table                 IN     VARCHAR2,
        queue_type                  IN     BINARY_INTEGER DEFAULT NORMAL_QUEUE,
        max_retries                 IN     NUMBER DEFAULT NULL,
        retry_delay                 IN     NUMBER DEFAULT 0,
        retention_time              IN     NUMBER DEFAULT 0,
	dependency_tracking         IN     BOOLEAN DEFAULT FALSE,
        comment                     IN     VARCHAR2 DEFAULT NULL,
        auto_commit                 IN     BOOLEAN DEFAULT TRUE);

  PROCEDURE create_np_queue(
        queue_name                  IN     VARCHAR2,
	multiple_consumers	    IN     BOOLEAN DEFAULT FALSE,
        comment                     IN     VARCHAR2 DEFAULT NULL);

  PROCEDURE drop_queue(
        queue_name                  IN     VARCHAR2,
        auto_commit                 IN     BOOLEAN DEFAULT TRUE);

  PROCEDURE drop_queue_table(
        queue_table                 IN     VARCHAR2,
        force                       IN     BOOLEAN DEFAULT FALSE,
        auto_commit                 IN     BOOLEAN DEFAULT TRUE);

  PROCEDURE start_queue(
        queue_name                  IN     VARCHAR2,
        enqueue                     IN 	   BOOLEAN DEFAULT TRUE,
        dequeue                     IN     BOOLEAN DEFAULT TRUE);
 
  PROCEDURE stop_queue(
        queue_name                  IN     VARCHAR2,
        enqueue                     IN     BOOLEAN DEFAULT TRUE,
        dequeue                     IN     BOOLEAN DEFAULT TRUE,
	wait                        IN     BOOLEAN DEFAULT TRUE);

  PROCEDURE alter_queue(
	queue_name	            IN	   VARCHAR2,
	max_retries		    IN	   NUMBER   DEFAULT NULL,
	retry_delay	            IN	   NUMBER   DEFAULT NULL,
        retention_time              IN     NUMBER   DEFAULT NULL,
        auto_commit                 IN     BOOLEAN  DEFAULT TRUE,
        comment                     IN     VARCHAR2 DEFAULT NULL);

  PROCEDURE add_subscriber(
        queue_name                  IN     VARCHAR2,
	subscriber		    IN	   SYS.AQ$_AGENT,
        rule                        IN     VARCHAR2 DEFAULT NULL);

  PROCEDURE alter_subscriber(
        queue_name                  IN     VARCHAR2,
	subscriber		    IN	   SYS.AQ$_AGENT,
        rule                        IN     VARCHAR2);

  PROCEDURE remove_subscriber(
        queue_name                  IN     VARCHAR2,
	subscriber		    IN     SYS.AQ$_AGENT);

  PROCEDURE grant_type_access(
	user_name		IN	VARCHAR2);

  FUNCTION queue_subscribers(
	queue_name		IN	VARCHAR2)
	RETURN aq$_subscriber_list_t;

  PROCEDURE grant_queue_privilege(
        privilege               IN      VARCHAR2,
        queue_name              IN      VARCHAR2,
        grantee                 IN      VARCHAR2,
        grant_option            IN      BOOLEAN := FALSE);

  PROCEDURE grant_system_privilege(
        privilege               IN      VARCHAR2,
        grantee                 IN      VARCHAR2,
        admin_option      	IN      BOOLEAN := FALSE);

  PROCEDURE revoke_queue_privilege(
        privilege               IN      VARCHAR2,
        queue_name              IN      VARCHAR2,
        grantee                 IN      VARCHAR2);

  PROCEDURE revoke_system_privilege(
        privilege               IN      VARCHAR2,
        grantee                 IN      VARCHAR2);

  PROCEDURE get_type_info(
        schema                      IN     VARCHAR2,
        qname                       IN     VARCHAR2,
        gettds                      IN     BOOLEAN,
        rc                          OUT    BINARY_INTEGER,
        toid                        OUT    RAW,
        version                     OUT    NUMBER,
        tds                         OUT    LONG RAW); 
 
  PROCEDURE verify_queue_types(
	src_queue_name	IN	VARCHAR2,
	dest_queue_name	IN	VARCHAR2,
	destination	IN	VARCHAR2 DEFAULT NULL,
	rc		OUT	BINARY_INTEGER);

  PROCEDURE verify_queue_types_no_queue(
	src_queue_name	IN	VARCHAR2,
	dest_queue_name	IN	VARCHAR2,
	destination	IN	VARCHAR2 DEFAULT NULL,
	rc		OUT	BINARY_INTEGER);

  PROCEDURE recover_propagation(
	schema		IN	VARCHAR2,
	queue_name	IN	VARCHAR2,
	destination	IN	VARCHAR2);

  PROCEDURE schedule_propagation(
	queue_name	            IN	   VARCHAR2,
	destination		    IN	   VARCHAR2 DEFAULT NULL,
	start_time	            IN	   DATE DEFAULT SYSDATE,
        duration                    IN     NUMBER DEFAULT NULL,
        next_time                   IN     VARCHAR2 DEFAULT NULL,
        latency                     IN     NUMBER DEFAULT 60);

  PROCEDURE unschedule_propagation(
	queue_name	            IN	   VARCHAR2,
	destination		    IN	   VARCHAR2 DEFAULT NULL);

  PROCEDURE alter_propagation_schedule(
        queue_name                  IN     VARCHAR2,
        destination                 IN     VARCHAR2 DEFAULT NULL,
        duration                    IN     NUMBER DEFAULT NULL,
        next_time                   IN     VARCHAR2 DEFAULT NULL,
        latency                     IN     NUMBER DEFAULT 60);

  PROCEDURE enable_propagation_schedule(
        queue_name                  IN     VARCHAR2,
        destination                 IN     VARCHAR2 DEFAULT NULL);

  PROCEDURE disable_propagation_schedule(
        queue_name                  IN     VARCHAR2,
        destination                 IN     VARCHAR2 DEFAULT NULL);

  FUNCTION aq$_propaq(
        job                         IN     NUMBER) RETURN DATE;

  -- aq$_propaq is overloaded with 8.0.5 interface to facilitate
  -- upgrade/downgrade
  FUNCTION aq$_propaq(
	job			    IN	   NUMBER,
	next_date		    IN	   DATE,
	qname   	            IN	   VARCHAR2,
	schema		            IN	   VARCHAR2,
        destination                 IN     VARCHAR2 DEFAULT NULL,
        toid_char                   IN     VARCHAR2 DEFAULT NULL,
        version_char                IN     VARCHAR2 DEFAULT NULL,
	start_time	            IN	   VARCHAR2,            
        duration                    IN     VARCHAR2 DEFAULT NULL,
        next_time                   IN     VARCHAR2 DEFAULT NULL,
        latency                     IN     VARCHAR2 DEFAULT '60') RETURN DATE;

  PROCEDURE start_time_manager;

  PROCEDURE stop_time_manager;

  PROCEDURE migrate_queue_table( 
         queue_table               IN      VARCHAR2,
         compatible                IN      VARCHAR2);
 
  --------------------
  --  PRIVATE METHODS
  --
 
  --  PROCEDURE create_base_view(qt_schema    IN VARCHAR2, 
  --                             qt_name      IN VARCHAR2,
  --                             qt_flags     IN NUMBER);
  -- 
  --  PROCEDURE create_qt_dictionary(qt_schema          VARCHAR2, 
  --                                 qt_name            VARCHAR2,
  --                                 user_data_type     INTEGER,
  --                                 qt_flags           INTEGER,
  --                                 sort_cols          VARCHAR2,
  --                                 primary_instance   BINARY_INTEGER,
  --                                 secondary_instance BINARY_INTEGER,
  --                                 comment            VARCHAR2);
  -- 
  --  FUNCTION newq_8_1(qt_flags IN NUMBER) RETURN BOOLEAN;
  --  FUNCTION mcq(qt_flags IN NUMBER) RETURN BOOLEAN; 
  --  FUNCTION scq_8_0(qt_flags IN NUMBER) RETURN BOOLEAN; 
  --  FUNCTION mcq_8_0(qt_flags IN NUMBER) RETURN BOOLEAN; 
  --  FUNCTION scq_8_1(qt_flags IN NUMBER) RETURN BOOLEAN; 
  --  FUNCTION mcq_8_1(qt_flags IN NUMBER) RETURN BOOLEAN; 

END dbms_aqadm;
/

