Rem Rem $Header: dbmsol.sql 11-aug-2006.08:39:48 rburns Exp $ Rem Rem dbmsol.sql Rem Rem Copyright (c) 1998, 2006, Oracle. 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 Rem Rem MODIFIED (MM/DD/YY) Rem rburns 08/11/06 - add grants Rem rburns 05/11/04 - use dynamic SQL Rem svivian 12/16/03 - add callout to refresh outline cache Rem svivian 04/17/03 - generate_outline becomes create_outline Rem bdagevil 04/28/03 - merge new file Rem svivian 01/21/03 - add generate_outline Rem kquinn 05/23/02 - 2209649: raise exception if outline not found Rem gviswana 05/24/01 - CREATE OR REPLACE SYNONYM Rem svivian 11/27/00 - grant execute to execute_catalog_role Rem svivian 11/15/00 - grant execute on dbms_outln_edit to public Rem svivian 10/06/00 - add procedure to grant select to sys Rem svivian 08/23/00 - add editing interfaces Rem svivian 07/26/00 - bug 1364696: case/whitespace insensitivity 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; PROCEDURE update_signatures; PROCEDURE exact_text_signatures; PROCEDURE clear_used(name IN VARCHAR2); -- -- NAME: create_outline -- -- DESCRIPTION: -- Generate an outline from the shared cursor identified -- by hash value and child number. -- -- PARAMETERS -- -- hash_value - hash value identifying the target shared cursor -- child_number - child number of the target shared cursor -- category - (optional) category in which to create outline -- -- USAGE NOTES: -- PROCEDURE create_outline(hash_value IN NUMBER, child_number IN NUMBER, category IN VARCHAR2 DEFAULT 'DEFAULT'); -- -- NAME: refresh_outline_cache -- -- DESCRIPTION: -- Refresh the outline cache by re-populating it with the current -- set of outlines. -- -- PARAMETERS -- -- none. -- -- USAGE NOTES: -- This procedure is run as a post table action for IMPORT. -- It can also be invoked directly by the user to refresh the -- outline cache. This might be done if many outlines have -- been dropped or their categories changed. -- -- PROCEDURE BODY: -- PROCEDURE refresh_outline_cache; FUNCTION refresh_outline_cache_expact RETURN VARCHAR2; END; / -- Create synonyms and add privileges -- CREATE OR REPLACE PUBLIC SYNONYM outln_pkg FOR sys.outln_pkg; GRANT EXECUTE ON outln_pkg TO dba; GRANT EXECUTE ON outln_pkg TO outln; GRANT EXECUTE ON outln_pkg TO execute_catalog_role; CREATE OR REPLACE PUBLIC SYNONYM outline FOR sys.outln_pkg; CREATE OR REPLACE PUBLIC SYNONYM dbms_outln FOR sys.outln_pkg; CREATE OR REPLACE PACKAGE sys.outln_edit_pkg AUTHID CURRENT_USER as -- -- NAME - create_edit_tables -- -- DESCRIPTION -- Create outline editing tables in calling user's schema. -- -- USAGE NOTES -- PROCEDURE create_edit_tables; -- -- NAME - drop_edit_tables -- -- DESCRIPTION -- drop outline editing tables in calling user's schema. -- -- USAGE NOTES -- PROCEDURE drop_edit_tables; -- -- NAME: refresh_private_outline -- -- DESCRIPTION: -- Refresh the in-memory copy of the outline, synchronizing -- its data with the edits made to the outline hints. -- -- PARAMETERS -- -- name - Name of the private outline to be refreshed -- -- USAGE NOTES: -- 1. If the private outline specified by the user is not found then an -- Ora-20001 error is raised to alert the user of this. -- PROCEDURE refresh_private_outline(name IN VARCHAR2); -- -- NAME: change_join_pos -- -- DESCRIPTION: -- Change the join position for the hint identified by outline name -- and hint number to the position specified by newpos. -- -- PARAMETERS -- -- name - Name of the private outline to be modified -- hintno - hint number to be modified -- newpos - new join position for the target hint -- -- USAGE NOTES: -- PROCEDURE CHANGE_JOIN_POS(name VARCHAR2, hintno NUMBER, newpos NUMBER); PROCEDURE generate_signature(sqltxt IN VARCHAR2, signature OUT RAW); END; / -- Create synonyms and add privileges -- CREATE OR REPLACE PUBLIC SYNONYM dbms_outln_edit FOR sys.outln_edit_pkg; GRANT EXECUTE ON dbms_outln_edit TO dba; GRANT EXECUTE ON dbms_outln_edit TO public; GRANT EXECUTE ON dbms_outln_edit TO execute_catalog_role; 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); 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', 'REFRESH_OUTLINE_CACHE_EXPACT', 2, 4, NULL, 2, NULL);