Rem
Rem $Header: rdbms/admin/dbmsxstr.sql st_server_thoang_bug-7580152/1 2008/11/22 22:58:48 thoang Exp $
Rem
Rem dbmsxstr.sql
Rem
Rem Copyright (c) 2001, 2008, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      dbmsxstr.sql - DBMS XStream Package
Rem
Rem    DESCRIPTION
Rem      This package contains the higher level APIs for creating
Rem      XStream outbound and inbound servers.
Rem
Rem    NOTES
Rem      Requires AQ and dbms_streams* related packages to have been 
Rem      previously installed.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    thoang      11/20/08 - Change column_table to column_list
Rem    rihuang     10/28/08 - Change signature for add_subset_outbound_rules
Rem    thoang      03/15/08 - Created
Rem

----------------------------------------------------------------------
-- XStream Admin API 
----------------------------------------------------------------------

CREATE OR REPLACE PACKAGE dbms_xstream_adm AUTHID CURRENT_USER AS 

  -- Procedure create_outbound creates an outbound server, necessary queue 
  -- and associated capture to allow clients to stream out LCRs from the
  -- specified source database.   
  --
  -- If no table or schema specified then the outbound server will stream
  -- all DDL and DML changes.
  --
  PROCEDURE create_outbound(
    server_name             IN VARCHAR2,
    source_database         IN VARCHAR2 DEFAULT NULL,
    table_names             IN VARCHAR2 DEFAULT NULL,
    schema_names            IN VARCHAR2 DEFAULT NULL,
    capture_user            IN VARCHAR2 DEFAULT NULL,
    connect_user            IN VARCHAR2 DEFAULT NULL,
    comment                 IN VARCHAR2 DEFAULT NULL);

  PROCEDURE create_outbound(
    server_name             IN VARCHAR2,
    source_database         IN VARCHAR2 DEFAULT NULL,
    table_names             IN DBMS_UTILITY.UNCL_ARRAY,
    schema_names            IN DBMS_UTILITY.UNCL_ARRAY,
    capture_user            IN VARCHAR2 DEFAULT NULL,
    connect_user            IN VARCHAR2 DEFAULT NULL,
    comment                 IN VARCHAR2 DEFAULT NULL);

/*  NOT SUPPORTING OUT PARAMETERS FOR NOW
  
  PROCEDURE create_outbound(
    server_name             IN VARCHAR2,
    source_database         IN VARCHAR2 DEFAULT NULL,
    table_names             IN DBMS_UTILITY.UNCL_ARRAY,
    schema_names            IN DBMS_UTILITY.UNCL_ARRAY,
    capture_user            IN VARCHAR2 DEFAULT NULL,
    connect_user            IN VARCHAR2 DEFAULT NULL,
    comment                 IN VARCHAR2 DEFAULT NULL,  
    ddl_table_rule_names    OUT DBMS_UTILITY.UNCL_ARRAY,
    dml_table_rule_names    OUT DBMS_UTILITY.UNCL_ARRAY,
    ddl_schema_rule_names   OUT DBMS_UTILITY.UNCL_ARRAY,  
    dml_schema_rule_names   OUT DBMS_UTILITY.UNCL_ARRAY);

  PROCEDURE create_outbound(
    server_name             IN VARCHAR2,
    source_database         IN VARCHAR2 DEFAULT NULL,
    table_names             IN VARCHAR2 DEFAULT NULL,
    schema_names            IN VARCHAR2 DEFAULT NULL,
    capture_user            IN VARCHAR2 DEFAULT NULL,
    connect_user            IN VARCHAR2 DEFAULT NULL,
    comment                 IN VARCHAR2 DEFAULT NULL, 
    ddl_table_rule_names    OUT VARCHAR2,
    dml_table_rule_names    OUT VARCHAR2,
    ddl_schema_rule_names   OUT VARCHAR2,
    dml_schema_rule_names   OUT VARCHAR2);

*/

  PROCEDURE alter_outbound(
    server_name             IN VARCHAR2,
    table_names             IN DBMS_UTILITY.UNCL_ARRAY,
    schema_names            IN DBMS_UTILITY.UNCL_ARRAY,
    add                     IN BOOLEAN DEFAULT TRUE,
    capture_user            IN VARCHAR2 DEFAULT NULL,
    connect_user            IN VARCHAR2 DEFAULT NULL,
    comment                 IN VARCHAR2 DEFAULT NULL,  
    inclusion_rule          IN BOOLEAN  DEFAULT TRUE);


  PROCEDURE alter_outbound(
    server_name             IN VARCHAR2,
    table_names             IN VARCHAR2 DEFAULT NULL,
    schema_names            IN VARCHAR2 DEFAULT NULL,
    add                     IN BOOLEAN DEFAULT TRUE,
    capture_user            IN VARCHAR2 DEFAULT NULL,
    connect_user            IN VARCHAR2 DEFAULT NULL,
    comment                 IN VARCHAR2 DEFAULT NULL,  
    inclusion_rule          IN BOOLEAN  DEFAULT TRUE);

  PROCEDURE add_outbound(
    server_name             IN VARCHAR2,
    queue_name              IN VARCHAR2,
    source_database         IN VARCHAR2 DEFAULT NULL,
    table_names             IN DBMS_UTILITY.UNCL_ARRAY,
    schema_names            IN DBMS_UTILITY.UNCL_ARRAY,
    connect_user            IN VARCHAR2 DEFAULT NULL,
    comment                 IN VARCHAR2 DEFAULT NULL);

  PROCEDURE add_outbound(
    server_name             IN VARCHAR2,
    queue_name              IN VARCHAR2,
    source_database         IN VARCHAR2 DEFAULT NULL,
    table_names             IN VARCHAR2 DEFAULT NULL,
    schema_names            IN VARCHAR2 DEFAULT NULL,
    connect_user            IN VARCHAR2 DEFAULT NULL,
    comment                 IN VARCHAR2 DEFAULT NULL);

  PROCEDURE drop_outbound(
    server_name             IN VARCHAR2);

  PROCEDURE add_subset_outbound_rules(
    server_name             IN VARCHAR2,
    table_name              IN VARCHAR2,
    condition               IN VARCHAR2 DEFAULT NULL,
    column_list             IN DBMS_UTILITY.LNAME_ARRAY,
    keep                    IN BOOLEAN  DEFAULT TRUE);

  PROCEDURE add_subset_outbound_rules(
    server_name             IN VARCHAR2,
    table_name              IN VARCHAR2,
    condition               IN VARCHAR2 DEFAULT NULL,
    column_list             IN VARCHAR2 DEFAULT NULL, 
    keep                    IN BOOLEAN  DEFAULT TRUE);

  -- Removes the specified subsetting rules from the given outbound server.
  -- The specified rules must have been created for the same subsetting 
  -- condition.
  PROCEDURE remove_subset_outbound_rules(
    server_name             IN VARCHAR2,
    insert_rule_name        IN VARCHAR2, 
    update_rule_name        IN VARCHAR2, 
    delete_rule_name        IN VARCHAR2);
 
  PROCEDURE create_inbound(
    server_name             IN VARCHAR2,
    queue_name              IN VARCHAR2,
    apply_user              IN VARCHAR2 DEFAULT NULL,  
    comment                 IN VARCHAR2 DEFAULT NULL);

  PROCEDURE alter_inbound(
    server_name             IN VARCHAR2,
    apply_user              IN VARCHAR2 DEFAULT NULL,  
    comment                 IN VARCHAR2 DEFAULT NULL);

  PROCEDURE drop_inbound(
    server_name             IN VARCHAR2);

END dbms_xstream_adm;
/

CREATE OR REPLACE PUBLIC SYNONYM dbms_xstream_adm FOR sys.dbms_xstream_adm
/
GRANT EXECUTE ON sys.dbms_xstream_adm TO execute_catalog_role
/