Rem
Rem $Header: dbmsol.sql 03-dec-98.16:41:00 sbodagal Exp $
Rem
Rem dbmsol.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998. All Rights Reserved.
Rem
Rem    NAME
Rem      dbmsol.sql - Specification for outln_pkg
Rem
Rem    DESCRIPTION
Rem      Contains functional interface for procedures and functions that
Rem	 are associated with management of stored outlines.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    sbodagal    12/03/98 - grant execute privilege on outln_pkg to outln
Rem    sbodagal    10/13/98 - fix default parameters in update_by_cat
Rem    sbodagal    10/08/98 - fix default parameters in update_by_cat
Rem    pejustus    10/29/98 - change synonym for outln_pkg
Rem    svivian     08/20/98 - default parameters in update_by_cat
Rem    svivian     06/05/98 - move outln_pkg to sys
Rem    svivian     04/22/98 - fix post table actions
Rem    svivian     04/16/98 - Created
Rem

CREATE OR REPLACE PACKAGE sys.outln_pkg as

--
-- NAME: drop_unused
--
-- DESCRIPTION:
--
--  Drop outlines that have never been applied in the compilation of a
--  SQL statement. 
--
-- PARAMETERS
--
--  none.
--
-- USAGE NOTES:
--
--  General purpose.
--  Likely candidates that will fall in this class are outlines created
--  on behalf of dynamic SQL statements generated by an application for 
--  one time use only. For these statements, the outlines will never be 
--  used and are simply taking up valuable disk space.
--
  PROCEDURE drop_unused;

--
-- NAME: drop_by_cat
--
-- DESCRIPTION:
--
--  Drop outlines that belong to a particular category. While outlines
--  are put into the DEFAULT category unless otherwise specified, users
--  have the option of grouping their outlines into groups called 
--  categories.  
--
-- PARAMETERS
--
--  cat - category of outlines to drop
--
-- USAGE NOTES:
--
--  General purpose.
--  It is expected that users will, from to time, wish to purge a category 
--  of outlines and this procedure does this in a single call.
--
  PROCEDURE drop_by_cat (cat VARCHAR2);

--
-- NAME: update_by_cat
--
-- DESCRIPTION:
--
--  Change the category of all outlines in one category to a
--  new category.
--
-- PARAMETERS:
--
--  oldcat - current category to be changed
--  newcat - target category to change outline to
--
-- USAGE NOTES:
--
--  General purpose.
--  Once satisfied with a set of outlines, a user might chose to move
--  the outlines from an "experimental" category to a "production" category.
--  Likewise, a user might wish to merge a set of outlines from one
--  category into another pre-existing category.
--
  PROCEDURE update_by_cat (oldcat VARCHAR2 default 'DEFAULT',
			   newcat VARCHAR2 default 'DEFAULT');

--
-- NAME: drop_extras
--
-- DESCRIPTION:
--
--  Drop extra hint tuples not accounted for by hintcount.
--
-- PARAMETERS:
--
--  none.
--
-- USAGE NOTES:
--
--  Import/Export post table action.
--  The main purpose of this procedure is to clean up after an import.
--  It is possible that an outline's OL$ tuple was rejected because an
--  outline already existed in the target database, either with the same
--  name or same signature. Hint tuples will also be rejected, up to the
--  number of hints in the already existing outline. 
--
--  Therefore, if the rejected outline has more hint tuples than the 
--  existing one, spurious tuples will be successfully inserted into the 
--  OL$HINTS table and this procedure, executed automatically as a post
--  table action, will remove those wrongly inserted hint tuples.
--
  PROCEDURE drop_extras;

--
-- NAME: drop_unrefd_hints
--
-- DESCRIPTION:
--
--  Drop hint tuples having no corresponding outline in the OL$ table.
--
-- PARAMETERS:
--
--  none.
--
-- USAGE NOTES:
--
--  Import/Export post table action.
--  This procedure will automatically as a post table action in order to
--  remove any hints for which no corresponding entry exists in the OL$
--  table. This condition is expected to be rare where it might occur
--  when an outline is being dropped while an import is going on.
--
  PROCEDURE drop_unrefd_hints;

--
-- NAME: drop_collision
--
-- DESCRIPTION:
--
--  Drop the outlines with an ol$.hintcount that does not match
--  the number of hints for that outline in ol$hints
--
-- PARAMETERS:
--
--  none.
--
-- USAGE NOTES:
--
--  Import/Export post table action.
--  Another concurrency problem could be that an outline is being created
--  or altered at the same time as one is being imported. Since it is
--  required that the outline be imported according to its original design,
--  if the concurrent operation changes the outline in mid-import, the outline
--  will be dropped as unreliable since its metadata is in an inconsistent
--  state.
--
  PROCEDURE drop_collision;

--
-- NAME: drop_extras_expact
--
-- DESCRIPTION:
--
--  This functional generates a string which serves as an invocation of
--  the drop_extras stored procedure.
--
-- PARAMETERS:
--
--  none.
--
-- USAGE NOTES:
--
--  Import/Export post table action
--

  FUNCTION drop_extras_expact RETURN VARCHAR2;

--
-- NAME: drop_unrefd_hints_expact
--
-- DESCRIPTION:
--
--  This functional generates a string which serves as an invocation of
--  the drop_unrefd_hints stored procedure.
--
-- PARAMETERS:
--
--  none.
--
-- USAGE NOTES:
--
--  Import/Export post table action
--

  FUNCTION drop_unrefd_hints_expact RETURN VARCHAR2;

--
-- NAME: drop_collision_expact
--
-- DESCRIPTION:
--
--  This functional generates a string which serves as an invocation of
--  the drop_collision stored procedure.
--
-- PARAMETERS:
--
--  none.
--
-- USAGE NOTES:
--
--  Import/Export post table action
--

  FUNCTION drop_collision_expact RETURN VARCHAR2;

END;
/

DROP PUBLIC SYNONYM outln_pkg;
CREATE PUBLIC SYNONYM outln_pkg FOR sys.outln_pkg;
GRANT EXECUTE ON outln_pkg TO dba;
GRANT EXECUTE ON outln_pkg TO outln;

DROP PUBLIC SYNONYM outline;
CREATE PUBLIC SYNONYM outline FOR sys.outln_pkg;
GRANT EXECUTE ON outline TO dba;
GRANT EXECUTE ON outline TO outln;

INSERT INTO sys.expact$ (owner, name, func_schema, func_package, func_proc, 
			code, callorder, callarg, obj_type, user_arg)
VALUES('OUTLN', 'OL$', 'SYS', 'OUTLN_PKG', 'DROP_EXTRAS_EXPACT',
      2, 1, NULL, 2, NULL);

INSERT INTO sys.expact$ (owner, name, func_schema, func_package, func_proc, 
			code, callorder, callarg, obj_type, user_arg)
VALUES('OUTLN', 'OL$', 'SYS', 'OUTLN_PKG', 'DROP_UNREFD_HINTS_EXPACT',
      2, 2, NULL, 2, NULL);

INSERT INTO sys.expact$ (owner, name, func_schema, func_package, func_proc, 
			code, callorder, callarg, obj_type, user_arg)
VALUES('OUTLN', 'OL$', 'SYS', 'OUTLN_PKG', 'DROP_COLLISION_EXPACT',
      2, 3, NULL, 2, NULL);
