Rem 
Rem $Header: dbmssnap.sql 28-may-98.13:04:03 hasun Exp $ 
Rem  
Rem  Copyright (c) 1991, 1995, 1996, 1997, 1998, 1999 by Oracle Corporation 
Rem    NAME
Rem      dbmssnap.sql - utilities for snapshots
Rem    DESCRIPTION
Rem      See below
Rem    RETURNS
Rem
Rem  MODIFIED
Rem     kdias      07/07/99  -  add get_mv_dependencies                        
Rem     wesmith    08/06/99 -  Preserve refresh group id when exporting
Rem                            RepAPI snapshots
Rem     nshodhan   03/19/99  - Add flag to make_repapi                         
Rem     igreenbe   01/04/99  - bug#784822 - fix comments for refresh_dependent 
Rem                            and refresh_all_mviews
Rem     wesmith    11/14/98  - change user_export_child signature              
Rem     wesmith    11/13/98  - RepAPI export code review fixes                 
Rem     wesmith    11/05/98  - Add support for export of RepAPI refresh groups 
Rem     avaradar   10/14/98  - Bug#708846: Remove rbdms_snapshot_utility       
Rem     hasun      05/20/98  - Integrate with SM API                           
Rem     hasun      05/12/98  - Add dbms_mview as a synonym for dbms_snapshot   
Rem     igreenbe   12/16/97  - Update consistent snapshot refresh limit to 400
Rem     hasun      10/09/97 -  Change defaults in dbms_snapshot_utility
Rem     hasun      06/09/97 -  Add dbms_snapshot_utility package
Rem     liwong     04/21/97 -  Remove execute_as_user parameter
Rem     masubram   04/04/97 -  Fix merge conflicts
Rem     masubram   03/27/97 -  declare constants for snapshot version
Rem     masubram   03/11/97 -  re-implement snapshot registration
Rem     hasun      03/05/97 -  Expose heap_size for parallel propagation
Rem     hasun      02/27/97 -  Expose new queueu push parameters in for refresh
Rem     masubram   01/27/97 -  Make purge_snapshot_from_log public
Rem     hasun      10/14/96 -  Remove table_reorganized()
Rem     hasun      10/07/96 -  Relocate snapshot registration procedures
Rem     hasun      10/07/96 -  Relocate setup, wrapup and other V7 RPCs
Rem     hasun      10/04/96 -  Move internal trigger support procedures
Rem     hasun      10/01/96 -  Reorganize V8 RPCs
Rem     hasun      08/09/96 -  Move get_lob_columns_info to dbms_snapshot
Rem     ldoo       05/15/96 -  New security model
Rem     ldoo       05/09/96 -  Add support for exp/imp snapshot triggers
Rem     hasun      05/06/96 -  replmerge
Rem     hasun      04/29/96 -  Remove public synonym for dbms_snapshot_utl
Rem     ashgupta   04/25/96 -  adding dbms_snapshot_utl
Rem     ashgupta   04/25/96 -  moving vector_compare to dbms_snapshot
Rem     ashgupta   04/15/96 -  modifying get_log_name
Rem     ashgupta   04/10/96 -  modifying verify-log
Rem     hasun      04/08/96 -  Implement new snapshot id
Rem     ashgupta   04/04/96 -  adding log validation
Rem     hasun      03/08/96 -  Fix merge problems
Rem     hasun      02/09/96 -  Start V8 project: Subquery Snapshots
Rem     ashgupta   02/09/96 -  Merging  snapshot registration code (proj 2045)
Rem     ashgupta   02/07/96 -  adding RPC for getting flag from mlog$
Rem     hasun      01/10/96 -  Split verification() from set_up()
Rem     hasun      01/02/96 -  Add table_reorganized()
Rem     hasun      12/19/95 -  Modify set_up and wrap_up for pk snaps
Rem     hasun      12/11/95 -  Add support procs for primary key snasphots
Rem     adowning   12/23/94 -  merge changes from branch 1.13.720.1
Rem     boki       12/21/94 -  merge changes from branch 1.6.710.10
Rem     boki       12/08/94 -  add new formal parameter to refresh()
Rem     adowning   11/11/94 -  merge changes from branch 1.6.710.9
Rem     adowning   10/24/94 -  add comments to refresh
Rem     adowning   10/11/94 -  make i_am_a_refresh a function
Rem     rjenkins   02/17/94 -  adding defaults
Rem     adowning   02/02/94 -  split file into public / private binary files
Rem     rjenkins   01/13/94 -  adding rollback seg to refresh groups
Rem     rjenkins   12/21/93 -  supporting import/export
Rem     rjenkins   12/17/93 -  creating job queue
Rem     rjenkins   11/02/93 -  adding explicit language flag to parse
Rem     rjenkins   10/22/93 -  work even if name_tokenize does not work
Rem     rjenkins   10/14/93 -  merge changes from branch k7010101
Rem     rjenkins   10/12/93 -  moving comma_to_table to dbmsutil
Rem     rjenkins   10/11/93 -  change default refresh method to NULL
Rem     rjenkins   08/30/93 -  push deferred txn queues before refresh
Rem     rjenkins   08/17/93 -  adding clear_refresh
Rem     rjenkins   07/22/93 -  stop hardcoding foo, bar
Rem     rjenkins   07/06/93 -  adding i_am_a_refresh
Rem     ghallmar   05/02/93 -  add multi-table consistent refresh 
Rem     mmoore     10/02/92 -  change pls_integer to binary_integer 
Rem     rjenkins   06/02/92 -  changing comment types 
Rem     rjenkins   02/12/92 -  more snapshot changes 
Rem     rjenkins   11/25/91 -  Creation 
-----------------------------------------------------------------------

CREATE OR REPLACE PACKAGE dbms_snapshot IS

  -- constants for snapshot version
  reg_unknown	      CONSTANT NUMBER := 0;
  reg_v7_snapshot     CONSTANT NUMBER := 1;
  reg_v8_snapshot     CONSTANT NUMBER := 2;
  reg_repapi_snapshot CONSTANT NUMBER := 3;  

  ------------
  --  OVERVIEW
  --
  --  These routines allow the user to refresh snapshots and purge logs.

  ------------------------------------------------
  --  SUMMARY OF SERVICES PROVIDED BY THIS PACKAGE
  --
  --  refresh		         - refresh a given snapshot
  --  refresh_all	         - refresh all snapshots that are due
  --			           to be refreshed
  --  refresh_dependent          - refresh all stale snapshots that depend
  --                               on the specified master tables
  --  refresh_all_mviews         - refresh all stale snapshots
  --  get_mv_dependencies	 - gets the list of snapshots that depend
  --                               on the specified tables/snapshots. 
  --  i_am_a_refresh             - return TRUE if local site is in the process
  --                               of refreshing one or more snapshots
  --  set_i_am_a_refresh         - set the refresh indicator
  --  begin_table_reorganization - indicate the start of a table reorganization
  --  end_table_reorganization   - indicate the end of a table reorganization
  --  purge_log		         - purge log of unnecessary rows
  --  purge_direct_load_log	 - purge direct loader log of unnecessary rows
  --  register_snapshot          - register a snapshot with the master site
  --  unregister_snapshot        - unregister a snapshot with the master site
  --  purge_snapshot_from_log    - purge the snapshot log for a specific 
  --                               snapshot

  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --

  --  -----------------------------------------------------------------------
  --  Transaction consistent refresh of an array of snapshots.
  --  The max number of snapshots that can be consistently refreshed is 400.
  --  The snapshots are refreshed atomically and consistently. 
  --  Atomically: all snapshots are refreshed or none are.
  --  Consistently: all integrity constraints that hold among master tables 
  --                will hold among the snapshot tables. 
  --  	
  --   LIST
  --     A comma-separated list or PL/SQL table of the snapshots 
  --     to be refreshed.
  --   METHOD
  --     A string that contains a letter for each 
  --     of the snapshots in the array according to the following codes:
  --     '?' -- use fast refresh when possible
  --     'F' -- use fast refresh or raise an error if not possible
  --     'C' -- perform a complete refresh, copying the entire snapshot from
  --            the master
  --     The default method for refreshing a snapshot is the method stored for
  --     that snapshot in the data dictionary.
  --   ROLLBACK_SEG 
  --     The name of the rollback segment to use while 
  --     refreshing snapshots.
  --   PUSH_DEFERRED_RPC 
  --     If TRUE then push all changes made to an updatable snapshot to its 
  --     associated master before refreshing the snapshot.  Otherwise, these
  --     changes may appear to be temporarily lost.
  --   REFRESH_AFTER_ERRORS
  --     If TRUE, then allow the refresh to proceed
  --     even if there are outstanding conflicts logged in the DefError
  --     table for the snapshot's master.
  --   PURGE_OPTION
  --     How to purge the transaction queue if PUSH_DEFERRED_RPC is true.
  --     0 = don't
  --     1 = cheap but imprecise (optimize for time)
  --     2 = expensive but precise (optimize for space)
  --   PARALLELISM
  --     Max degree of parallelism for pushing deferred RPCs. This value
  --     is considered only if PUSH_DEFERRED_RPC is true.
  --     0 = (old algorithm) serial propagation
  --     1 = (new algorithm) parallel propagation with only 1 slave
  --     n = (new algorithm) parallel propagation with n slaves
  --   HEAP_SIZE
  --     The max number of txns to be examined simultaneously for
  --     parallel scheduling computation. This parameter is used only if
  --     the value of the PARALLELISM parameter is greater than 0.
  --   ATOMIC_REFRESH
  --     If TRUE, then perform the refresh operations for the specified
  --     set of snapshots in a single transaction. This guarantees that either
  --     all of the snapshots are successfully refresh or none of the snapshots
  --     are refreshed.
  PROCEDURE refresh(list                 IN VARCHAR2,
                    method               IN VARCHAR2       := NULL,
                    rollback_seg         IN VARCHAR2       := NULL,
                    push_deferred_rpc    IN BOOLEAN        := TRUE,
                    refresh_after_errors IN BOOLEAN        := FALSE,
                    purge_option         IN BINARY_INTEGER := 1,
                    parallelism          IN BINARY_INTEGER := 0,
                    heap_size            IN BINARY_INTEGER := 0,
                    atomic_refresh       IN BOOLEAN        := TRUE);

  PROCEDURE refresh(tab                  IN OUT dbms_utility.uncl_array,
                    method               IN VARCHAR2       := NULL,
                    rollback_seg         IN VARCHAR2       := NULL,
                    push_deferred_rpc    IN BOOLEAN        := TRUE,
                    refresh_after_errors IN BOOLEAN        := FALSE,
                    purge_option         IN BINARY_INTEGER := 1,
                    parallelism          IN BINARY_INTEGER := 0,
                    heap_size            IN BINARY_INTEGER := 0,
                    atomic_refresh       IN BOOLEAN        := TRUE);

  --  -----------------------------------------------------------------------
  --  Execute all refresh jobs due to be executed
  --  Requires ALTER ANY SNAPSHOT privilege
  PROCEDURE refresh_all;

  --  -----------------------------------------------------------------------
  --  Refresh all local snapshots based on a specified local master table where 
  --    1) the table has been modified since it was last successfully refreshed
  --    2) the snapshot is a member of dba_mview_analysis
  --  NOTE: 
  --    A snapshot will be considered only if all of its tables are local.
  --
  --   NUMBER_OF_FAILURES
  --     Returns the number of failures that occurred during processing.
  --   LIST
  --     A comma-separated list of the master tables to consider.
  --   TAB
  --     A PL/SQL table of the master tables to consider.
  --   METHOD
  --     A string of refresh methods indicating how to refresh the dependent
  --     snapshots.  All of the snapshots that depend on a particular table 
  --     are refreshed according to the refresh method associated with that 
  --     table.  If a table does not have a corresponding refresh method 
  --     (that is, more tables are specified than refresh methods), then any 
  --     snapshot that depends on that table is refreshed according to its 
  --     default refresh method.  The default refresh method for a snapshot 
  --     is stored in the data dictionary.
  --     The refresh methods are represented by the following codes:
  --     '?' -- use fast refresh if possible;  otherwise, use complete refresh
  --     'F' -- use fast refresh if possible;  otherwise, raise an error
  --     'C' -- use complete refresh to construct the entire snapshot from 
  --            the master tables
  --   ROLLBACK_SEG 
  --     The name of the rollback segment to use while refreshing the snapshots.
  --   REFRESH_AFTER_ERRORS
  --     If TRUE and if ATOMIC_REFRESH is FALSE, then an error will not be 
  --     raised if an error is encountered during the refresh.  Otherwise, 
  --     any error encountered during refresh will be raised.
  --   ATOMIC_REFRESH
  --     If TRUE, then refresh all of the dependent snapshots in a single 
  --     transaction.  This guarantees that either all of the snapshots are 
  --     successfully refreshed or none of the snapshots are refreshed.  
  --     Otherwise, refresh each dependent snapshot in a separate transaction.
  PROCEDURE refresh_dependent(number_of_failures   OUT BINARY_INTEGER,
                              list                  IN VARCHAR2,
                              method                IN VARCHAR2 := NULL,
                              rollback_seg          IN VARCHAR2 := NULL,
                              refresh_after_errors  IN BOOLEAN  := FALSE,
                              atomic_refresh        IN BOOLEAN  := TRUE);

  PROCEDURE refresh_dependent(number_of_failures   OUT BINARY_INTEGER,
                              tab                   IN dbms_utility.uncl_array,
                              method                IN VARCHAR2 := NULL,
                              rollback_seg          IN VARCHAR2 := NULL,
                              refresh_after_errors  IN BOOLEAN  := FALSE,
                              atomic_refresh        IN BOOLEAN  := TRUE);

  --  -----------------------------------------------------------------------
  --  Refresh all local snapshots based on a local master table where 
  --    1) the table has been modified since it was last successfully refreshed
  --    2) the snapshot is a member of dba_mview_analysis
  --  NOTE: 
  --    A snapshot will be considered only if all of its tables are local.
  --
  --   NUMBER_OF_FAILURES
  --     Returns the number of failures that occurred during processing.
  --   METHOD
  --     A single refresh method indicating how to refresh the dependent 
  --     snapshots.  If a refresh method is not specified, then any dependent
  --     snapshot is refreshed according to its default refresh method.  The 
  --     default refresh method for a snapshot is stored in the data dictionary.
  --     A refresh method is represented by the following codes:
  --     '?' -- use fast refresh if possible;  otherwise, use complete refresh
  --     'F' -- use fast refresh if possible;  otherwise, raise an error 
  --     'C' -- use complete refresh to construct the entire snapshot from 
  --            the master tables
  --   ROLLBACK_SEG 
  --     The name of the rollback segment to use while refreshing the snapshots.
  --   REFRESH_AFTER_ERRORS
  --     If TRUE and if ATOMIC_REFRESH is FALSE, then an error will not be 
  --     raised if an error is encountered during the refresh.  Otherwise, 
  --     any error encountered during refresh will be raised.
  --   ATOMIC_REFRESH
  --     If TRUE, then refresh all of the dependent snapshots in a single 
  --     transaction.  This guarantees that either all of the snapshots are 
  --     successfully refreshed or none of the snapshots are refreshed.  
  --     Otherwise, refresh each dependent snapshot in a separate transaction.
  PROCEDURE refresh_all_mviews(number_of_failures   OUT BINARY_INTEGER,
                               method                IN VARCHAR2 := NULL,
                               rollback_seg          IN VARCHAR2 := NULL,
                               refresh_after_errors  IN BOOLEAN  := FALSE,
                               atomic_refresh        IN BOOLEAN  := TRUE);

  -- ------------------------------------------------------------------------
  -- This procedure finds the list of materialized view that are directly
  -- dependent on the list of tables or materialized views that has been 
  -- specified.
  --
  -- LIST : 
  --   A comma separated list of the tables/materialized views to consider
  -- DEPLIST
  --   The list of materialized views that are directly dependent on the 
  --   tables/materialized view that has been specified in "LIST".
  --
  PROCEDURE get_mv_dependencies(list		IN VARCHAR2,		
 				deplist	       OUT VARCHAR2);

  -- ------------------------------------------------------------------------
  -- This procedure disables or enables snapshot replication trigger at the
  -- local snapshot site.
  -- value = TRUE  -> disable all local replication triggers for snapshots
  -- value = FALSE -> enable all local replication triggers for snapshots
  PROCEDURE set_i_am_a_refresh(value IN BOOLEAN);

  -- ------------------------------------------------------------------------
  -- Returns TRUE if the local site is in the process of refreshing one or
  -- more snapshots. Return FALSE otherwise.
  FUNCTION i_am_a_refresh RETURN BOOLEAN;

  -- ------------------------------------------------------------------------
  -- This procedure must be called before a master table is reorganized. It 
  -- performs process to preserve snapshot data needed for refresh.
  PROCEDURE begin_table_reorganization(tabowner IN VARCHAR2,
                                       tabname  IN VARCHAR2);

  -- ------------------------------------------------------------------------
  -- This procedure myst be call after a master tanel is reorganized. It
  -- ensures that the snapshot data for the master table is valid and that
  -- the master table is in the proper state.
  PROCEDURE end_table_reorganization(tabowner IN VARCHAR2,
                                     tabname  IN VARCHAR2);

  -- ------------------------------------------------------------------------
  -- Purge the snapshot log for the specified master master of unecessary rows.
  PROCEDURE purge_log(master IN VARCHAR2, 
                      num    IN BINARY_INTEGER := 1, 
                      flag   IN VARCHAR2       := 'NOP' );

  -- ------------------------------------------------------------------------
  -- Remove entries from the direct loader log after they are no longer
  -- needed for any known snapshot.
  PROCEDURE purge_direct_load_log;

  -- ------------------------------------------------------------------------
  -- Invoked at the master site by (remote) snapshot site 'snapsite' to 
  -- register snapshot 'snapname' at the master site. The invocation
  -- is done using a synchronous RPC. 
  -- May also be invoked directly at the master site by the DBA to manually
  -- register a snapshot.
  --
  -- Input argugments:
  --    snapowner   Owner of the snapshot
  --    snapname    Name of the snapshot
  --    snapsite    Name of the snapshot site (should contain no double qoutes)
  --    snapshot_id V7 snapshot identifier
  --    flag        Attributes of the snapshot
  --    qry_txt     Snapshot definition query
  --    rep_type    Version of snapshot
  PROCEDURE register_snapshot(snapowner   IN VARCHAR2,
                  snapname    IN VARCHAR2,
                  snapsite    IN VARCHAR2,
                  snapshot_id IN DATE,
                  flag        IN BINARY_INTEGER, 
                  qry_txt     IN VARCHAR2,
                  rep_type    IN BINARY_INTEGER := dbms_snapshot.reg_unknown);
  -- Input argugments:
  --    snapowner   Owner of the snapshot
  --    snapname    Name of the snapshot
  --    snapsite    Name of the snapshot site (should contain no double qoutes)
  --    snapshot_id snapshot identifier
  --    flag        Attributes of the snapshot
  --    qry_txt     Snapshot definition query
  --    rep_type    Version of snapshot
  PROCEDURE register_snapshot(snapowner   IN VARCHAR2,
                  snapname    IN VARCHAR2,
                  snapsite    IN VARCHAR2,
                  snapshot_id IN BINARY_INTEGER,
                  flag        IN BINARY_INTEGER, 
                  qry_txt     IN VARCHAR2,
                  rep_type    IN BINARY_INTEGER := dbms_snapshot.reg_unknown);

  -- ------------------------------------------------------------------------
  -- Invoked at the master site by (remote) snapshot site 'snapsite' to 
  -- unregister snapshot 'snapname' at the master site. The invocation
  -- is done using a synchronous RPC. 
  -- May also be invoked directly at the master site by the DBA to manually
  -- register a snapshot.
  --
  -- Input argugments:
  --    snapowner   Owner of the snapshot
  --    snapname    Name of the snapshot
  --    snapsite    Name of the snapshot site (should contain no double qoutes)
  PROCEDURE unregister_snapshot(snapowner IN VARCHAR2,
                                snapname  IN VARCHAR2,
                                snapsite  IN VARCHAR2);

  -- ------------------------------------------------------------------------
  -- This procedure is called on the master site to delete the rows in 
  -- snapshot refresh related data dictionary tables maintained at the 
  -- master site for the specified snapshot identified by its snapshot_id. 
  -- If the snapshot specified is the oldest snapshot to have refreshed
  -- from any of the  master tables, then the snapshot log is also purged. 
  -- This procedure does not unregister the snapshot.
  --
  -- In case there is an error while purging one of the snapshot logs, the 
  -- successful purge operations of the previous snapshot logs are not rolled 
  -- back. This is to  minimize the size of the snapshot logs. In case of an
  -- error, this procedure can be invoked again until all the snapshot
  -- logs are purged.
  PROCEDURE purge_snapshot_from_log(snapshot_id IN BINARY_INTEGER); 

  -- ------------------------------------------------------------------------
  -- This procedure is called on the master site to delete the rows in 
  -- snapshot refresh related data dictionary tables maintained at the 
  -- master site for the specified snapshot. If the snapshot specified is
  -- the oldest snapshot to have refreshed  from any of the master tables, 
  -- then the snapshot log is also purged. This procedure does not unregister
  -- the snapshot.
  --
  -- In case there is an error while purging one of the snapshot logs, the 
  -- successful purge operations of the previous snapshot logs are not rolled 
  -- back. This is to  minimize the size of the snapshot logs. In case of an
  -- error, this procedure can be invoked again until all the snapshot
  -- logs are purged.
  PROCEDURE purge_snapshot_from_log(snapowner   IN VARCHAR2, 
                                    snapname    IN VARCHAR2, 
                                    snapsite    IN VARCHAR2);

  --- #######################################################################
  --- INTERNAL PROCEDURES
  ---
  --- The following procedure provide internal functionality and should
  --- not be called directly. 
  ---
  ---  These interfaces are obselete in V8 and are present only for 
  ---  providing backwards compatibility
  --- #######################################################################

  PROCEDURE set_up(mowner   IN     VARCHAR2, 
                   master   IN     VARCHAR2, 
                   log      IN OUT VARCHAR2,
	           snapshot IN OUT DATE,
                   snaptime IN OUT DATE);

  PROCEDURE wrap_up(mowner IN VARCHAR2, 
                    master IN VARCHAR2, 
                    sshot  IN DATE, 
                    stime  IN DATE);

  PROCEDURE get_log_age(oldest IN OUT DATE, 
                        mow    IN     VARCHAR2, 
                        mas    IN     VARCHAR2);

  -- obselete interface, present for backward compatability
  PROCEDURE drop_snapshot(mowner   IN VARCHAR2, 
                          master   IN VARCHAR2, 
                          snapshot IN DATE);

  PROCEDURE testing;

END dbms_snapshot;
/
GRANT EXECUTE ON dbms_snapshot TO PUBLIC
/
DROP PUBLIC SYNONYM dbms_snapshot
/
CREATE PUBLIC SYNONYM dbms_snapshot FOR dbms_snapshot
/
DROP PUBLIC SYNONYM dbms_mview
/
CREATE PUBLIC SYNONYM dbms_mview FOR dbms_snapshot
/


CREATE OR REPLACE PACKAGE dbms_refresh IS
  -- dbms_refresh is the interface for administering refresh groups.

  -- CONSTANTS
  --
  -- constants for rgroup$.flag
  -- NOTE: if you make any changes here, you must change the 
  --       corresponding constants in prvtsnap.sql
  --
  REPAPI_RGROUP CONSTANT NUMBER := 8;

  -- ------------------------------------------------------------------------
  -- MAKE a new refresh group.
  --
  -- PARAMETERS:
  -- NAME is of the form 'foo' or 'user.foo' or '"USER"."FOO"'.  
  --   The logged-in user is used as a default.
  -- LIST is a comma-separated list of objects to be refreshed, such as
  --     'foo, scott.bar ,"SCOTT"."BLUE"'.  The default user is the owner
  --     of the refresh group.
  -- TAB  is a PL/SQL table of objects to be refreshed, starting with 1
  --   and filling every number until an entry is NULL, with every entry
  --   formatted the same way as NAME.  The default user is the owner
  --   of the refresh group.
  -- NEXT_DATE is the date for the refresh group to first be refreshed.
  --   See dbmsjobq.sql .  If there is no current job, the default interval
  --   will be 'null' and the job will delete itself after refreshing the
  --   group at NEXT_DATE.
  -- INTERVAL is used to determine the next NEXT_DATE.  See dbmsjobq.sql .
  --   If there is no current job, NEXT_DATE will default to null and the
  --   job will not run until you manually set NEXT_DATE to something else
  --   or manually refresh the group.
  -- IMPLICIT_DESTROY means to delete the refresh group when the last item
  --   is subtracted from it.  The value is stored with the group definition.
  --   Empty groups can be created with IMPLICIT_DESTROY set.

  PROCEDURE make(name                 IN VARCHAR2,
                 list                 IN VARCHAR2,
                 next_date            IN DATE,
                 interval             IN VARCHAR2,
                 implicit_destroy     IN BOOLEAN        := FALSE,
                 lax                  IN BOOLEAN        := FALSE,
                 job                  IN BINARY_INTEGER := 0,
                 rollback_seg         IN VARCHAR2       := NULL,
                 push_deferred_rpc    IN BOOLEAN        := TRUE,
                 refresh_after_errors IN BOOLEAN        := FALSE,
                 purge_option         IN BINARY_INTEGER := 1,
                 parallelism          IN BINARY_INTEGER := 0,
                 heap_size            IN BINARY_INTEGER := 0);

  PROCEDURE make(name                 IN VARCHAR2,
                 tab                  IN dbms_utility.uncl_array,
                 next_date            IN DATE,
                 interval             IN VARCHAR2,
                 implicit_destroy     IN BOOLEAN        := FALSE,
                 lax                  IN BOOLEAN        := FALSE,
                 job                  IN BINARY_INTEGER := 0,
                 rollback_seg         IN VARCHAR2       := NULL,
                 push_deferred_rpc    IN BOOLEAN        := TRUE,
                 refresh_after_errors IN BOOLEAN        := FALSE,
                 purge_option         IN BINARY_INTEGER := 1,
                 parallelism          IN BINARY_INTEGER := 0,
                 heap_size            IN BINARY_INTEGER := 0);

  PROCEDURE make_repapi(
                 refgroup    IN BINARY_INTEGER,
                 name        IN VARCHAR2,
                 siteid      IN BINARY_INTEGER,
                 refresh_seq IN BINARY_INTEGER,
                 export_db   IN VARCHAR2,
                 flag        IN BINARY_INTEGER DEFAULT REPAPI_RGROUP);

  -- ------------------------------------------------------------------------
  -- ADD some refreshable objects to a refresh group.
  PROCEDURE add(name      IN VARCHAR2,
                list      IN VARCHAR2,
                lax       IN BOOLEAN  := FALSE,
                siteid    IN BINARY_INTEGER := 0,
                export_db IN VARCHAR2 := NULL );
  PROCEDURE add(name      IN VARCHAR2,
                tab       IN dbms_utility.uncl_array,
                lax       IN BOOLEAN  := FALSE,
                siteid    IN BINARY_INTEGER := 0,
                export_db IN VARCHAR2 := NULL );

  -- ------------------------------------------------------------------------
  -- SUBTRACT some refreshable objects from a refresh group.
  PROCEDURE subtract(name IN VARCHAR2,
                     list IN VARCHAR2,
                     lax  IN BOOLEAN  := FALSE );
  PROCEDURE subtract(name IN VARCHAR2,
                     tab  IN dbms_utility.uncl_array,
                     lax  IN BOOLEAN  := FALSE );

  -- ------------------------------------------------------------------------
  -- DESTROY a refresh group, make it cease to exist.
  PROCEDURE destroy(name IN VARCHAR2);

  -- ------------------------------------------------------------------------
  -- Change any changeable pieces of the job that does the refresh
  PROCEDURE change(name                 IN VARCHAR2,
                   next_date            IN DATE           := NULL,
                   interval             IN VARCHAR2       := NULL,
                   implicit_destroy     IN BOOLEAN        := NULL,
                   rollback_seg         IN VARCHAR2       := NULL,
                   push_deferred_rpc    IN BOOLEAN        := NULL,
                   refresh_after_errors IN BOOLEAN        := NULL,
                   purge_option         IN BINARY_INTEGER := NULL,
                   parallelism          IN BINARY_INTEGER := NULL,
                   heap_size            IN BINARY_INTEGER := NULL);
  
  -- ------------------------------------------------------------------------
  -- Atomically, consistently refresh all objects in a refresh group now.
  -- Clear the BROKEN flag for the job if the refresh succeeds
  PROCEDURE refresh(name IN VARCHAR2);

  -- ------------------------------------------------------------------------
  -- Produce the text of a call for recreating the given group
  PROCEDURE user_export(rg#    IN      BINARY_INTEGER,
                        mycall IN OUT VARCHAR2);

  -- ------------------------------------------------------------------------
  -- Produce the text of a call for recreating the given group item
  PROCEDURE user_export_child(myowner IN     VARCHAR2,
                              myname  IN     VARCHAR2,
                              mytype  IN     VARCHAR2,
                              mycall  IN OUT VARCHAR2, 
                              mysite  IN     BINARY_INTEGER := 0);

END dbms_refresh;
/
GRANT EXECUTE ON dbms_refresh TO PUBLIC
/
DROP PUBLIC SYNONYM dbms_refresh
/
CREATE PUBLIC SYNONYM dbms_refresh FOR dbms_refresh
/


