Rem 
Rem $Header: dbmssnap.sql,v 1.14 1994/12/27 15:37:37 adowning Exp $ 
Rem  
Rem  Copyright (c) 1991 by Oracle Corporation 
Rem    NAME
Rem      dbmssnap.sql - utilities for snapshots
Rem    DESCRIPTION
Rem      See below
Rem    RETURNS
Rem
Rem  MODIFIED
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 
-----------------------------------------------------------------------

Rem
Rem  Headers
Rem

CREATE OR REPLACE PACKAGE dbms_snapshot IS

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

  ------------------------------------------------
  --  SUMMARY OF SERVICES PROVIDED BY THIS PACKAGE
  --
  --  purge_log		- purge log of unnecessary rows
  --  refresh		- refresh a given snapshot
  --  refresh_all	- refresh all snapshots that are due
  --			    to be refreshed
  --  drop_snapshot	- drop a given snapshot
  --  set_up		- prepare master site to refresh a snapshot
  --  wrap_up		- record a refresh at the master site
  --  get_log_age	- find oldest date entry in log
  --  testing		- test snapshots (currently null)
  --  I_am_a_refresh    - flag used to let triggers identify refreshes


  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --
  PROCEDURE purge_log( master VARCHAR2, num BINARY_INTEGER DEFAULT 1, 
                       flag VARCHAR2 DEFAULT 'NOP' );

  PROCEDURE refresh( list                 IN VARCHAR2,
                     method               IN VARCHAR2 DEFAULT NULL,
                     rollback_seg         IN VARCHAR2 DEFAULT NULL,
                     push_deferred_rpc    IN BOOLEAN DEFAULT TRUE,
                     refresh_after_errors IN BOOLEAN DEFAULT FALSE,
		     execute_as_user      IN BOOLEAN DEFAULT FALSE );

  --  -----------------------------------------------------------------------
  --  Transaction consistent refresh of an array of snapshots
  --
  --  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. 
  --  The "method" string (not required) should contain 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.
  --  The maximum number of snapshots that can be consistently refreshed is 100.
  --   Rollback_seg is the name of the rollback segment to use while 
  --     refreshing snapshots.
  --   Push_deferred_rpc pushes changes from 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, will allow the refresh to proceed
  --     even if there are outstanding conflicts logged in the DefError
  --     table for the snapshot's master.
  --   Execute_as_user
  --      IF TRUE the execution of deferred RPCs is authenticated at the 
  --      remote system using the authentication context of the session user.  If 
  --      FALSE the execution of deferred RPCs is authenticated at the remote system 
  --      using the authentication contexts of the users that originally queued the 
  --      deferred RPCs (indicated in the origin_user column of the deftran table). 
  -- 
  PROCEDURE refresh( tab                  IN OUT dbms_utility.uncl_array,
                     method               IN VARCHAR2 DEFAULT NULL,
                     rollback_seg         IN VARCHAR2 DEFAULT NULL,
                     push_deferred_rpc    IN BOOLEAN DEFAULT TRUE,
                     refresh_after_errors IN BOOLEAN DEFAULT FALSE,
		     execute_as_user      IN BOOLEAN DEFAULT FALSE );
  --  External

  PROCEDURE refresh_all;
  --  External, refresh all snapshots due to be refreshed
  --  Requires ALTER ANY SNAPSHOT privilege

  PROCEDURE set_i_am_a_refresh(value IN  BOOLEAN);
  --  External

  FUNCTION i_am_a_refresh RETURN BOOLEAN;
  --  External

  PROCEDURE drop_snapshot( mowner VARCHAR2, master VARCHAR2, snapshot date);
  --  Internal

  PROCEDURE set_up( mowner VARCHAR2, master VARCHAR2, log IN OUT VARCHAR2,
	            snapshot IN OUT date, snaptime IN OUT date);
  --  Internal

  PROCEDURE wrap_up( mowner VARCHAR2, master VARCHAR2, 
                     sshot date, stime date);
  --  Internal

  PROCEDURE get_log_age( oldest IN OUT date, mow VARCHAR2, mas VARCHAR2);
  --  Internal

  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
/

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

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

  aaspriv   BINARY_INTEGER;
  -- Privilege number for ALTER ANY SNAPSHOT

  PROCEDURE make    ( name      IN VARCHAR2,
                      list      IN VARCHAR2,
                      next_date IN DATE,
                      interval  IN VARCHAR2,
                      implicit_destroy    IN BOOLEAN DEFAULT FALSE,
                      lax       IN BOOLEAN DEFAULT FALSE,
                      job       IN BINARY_INTEGER DEFAULT 0,
                      rollback_seg  IN VARCHAR2 DEFAULT NULL,
                      push_deferred_rpc    IN BOOLEAN DEFAULT TRUE,
                      refresh_after_errors IN BOOLEAN DEFAULT FALSE );
  PROCEDURE make    ( name      IN VARCHAR2,
                      tab       IN dbms_utility.uncl_array,
                      next_date IN DATE,
                      interval  IN VARCHAR2,
                      implicit_destroy    IN BOOLEAN DEFAULT FALSE,
                      lax       IN BOOLEAN DEFAULT FALSE,
                      job       IN BINARY_INTEGER DEFAULT 0,
                      rollback_seg  IN VARCHAR2 DEFAULT NULL,
                      push_deferred_rpc    IN BOOLEAN DEFAULT TRUE,
                      refresh_after_errors IN BOOLEAN DEFAULT FALSE );
  -- MAKE a new refresh group.

  PROCEDURE add     ( name      IN VARCHAR2,
                      list      IN VARCHAR2,
                      lax       IN BOOLEAN  DEFAULT FALSE );
  PROCEDURE add     ( name      IN VARCHAR2,
                      tab       IN dbms_utility.uncl_array,
                      lax       IN BOOLEAN  DEFAULT FALSE );
  -- ADD some refreshable objects to a refresh group.

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

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

  PROCEDURE change( name                 IN VARCHAR2,
                    next_date            IN DATE DEFAULT NULL,
                    interval             IN VARCHAR2 DEFAULT NULL,
                    implicit_destroy     IN BOOLEAN  DEFAULT NULL,
                    rollback_seg         IN VARCHAR2 DEFAULT NULL,
                    push_deferred_rpc    IN BOOLEAN DEFAULT NULL,
                    refresh_after_errors IN BOOLEAN DEFAULT NULL);
  -- Change any changeable pieces of the job that does the refresh

  PROCEDURE refresh ( name      IN VARCHAR2 );
  -- Atomically, consistently refresh all objects in a refresh group now.
  -- Clear the BROKEN flag for the job if the refresh succeeds

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

  PROCEDURE user_export_child( myowner IN     VARCHAR2,
                               myname  IN     VARCHAR2,
                               mytype  IN     VARCHAR2,
                               mycall  IN OUT VARCHAR2);
  -- Produce the text of a call for recreating the given group item
END dbms_refresh;
/

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

