set define '^' set verify off prompt wwv_flow_upgrade Rem RUNTIME DEPLOYMENT: YES Rem Rem MODIFIED (MM/DD/YYYY) Rem mhichwa 07/03/2001 - Created Rem mhichwa 07/20/2001 - Enhanced Rem mhichwa 07/23/2001 - Added re-create public synonyms Rem mhichwa 07/27/2001 - Enhanced package documentation Rem mhichwa 07/27/2001 - Added upgrade command column Rem tmuth 08/13/2001 - Added increment session procedure Rem jstraub 09/28/2001 - Added drop_public_synonyms, create_public_synonyms, grant_public_synonyms Rem and rewrote recreate_public_synonyms to call drop, create, & grant Rem tmuth 10/01/2001 - Added create and remove jobs procedures to manage dbms_jobs Rem tmuth 10/01/2001 - Added switch_schemas Rem tmuth 10/08/2001 - Added flows_files_objects_create and flows_files_objects_remove Rem tmuth 11/30/2001 - Set termout off for drop table Rem mhichwa 03/21/2002 - Changed comments Rem mhichwa 04/15/2002 - Exposed copy table routine Rem mhichwa 04/16/2002 - Removed exposed copy table routine Rem mhichwa 04/16/2002 - Exposed copy table routine Rem tmuth 05/01/2002 - Added copy_prefs and associcated globals Rem tmuth 11/25/2002 - Added item_attributes Rem sspadafo 02/27/2003 - Added to_template_id function (Bug 2823578) Rem jstraub 06/17/2004 - Added upgrade_to_010600 to support upgrade from 1.5.* Rem jstraub 08/27/2004 - Added template_name_cleanup (Bug 3858934) Rem cbcho 06/02/2005 - Added sw_cleanup procedure Rem cbcho 06/13/2005 - Added p_to argument to sw_cleanup Rem jduan 07/07/2005 - Modify for upgrade to 2.0 Rem jkallman 08/02/2005 - Add p_from parameter to upgrade_to_020000 Rem jduan 02/10/2006 - Change upgrade_to_020000 to upgrade_to_020200 Rem jkallman 09/29/2006 - Change upgrade_to_020200 to upgrade_to_030000 Rem jstraub 03/07/2007 - Removed wwv_flow_upgrade_progress ddl Rem msewtz 05/09/2007 - Added report_column_cleanup (bug 6029855) Rem jkallman 08/02/2007 - Rename update_to_030000 to upgrade_to_030100 Rem jstraub 09/14/2007 - Added drop_private_synonyms and create_private_synonyms to support runtime Rem jstraub 01/07/2008 - Moved g_tabs and g_synonyms to package body (Bug 6707950) Rem jstraub 01/07/2008 - Removed copy_table from spec to make private (Bug 6707950) Rem jkallman 09/09/2008 - Rename update_to_030100 to upgrade_to_030200 create or replace package wwv_flow_upgrade -- -- Copyright (c) Oracle Corporation 2001 - 2002. All Rights Reserved. -- -- NAME -- wwv_flow_upgrade -- -- DESCRIPTION -- This package facilitates flows upgrades. -- Follow the following steps to upgrade: -- 1. Install a new version of flows into a "new flows" schema. -- 2. Login to sqlplus as the "new flows" user. -- 3. exec wwv_flow_upgrade.copy_flow_meta_data('FLOWS1','FLOWS2') -- assume flows1 is the old schema and flows2 is the new schema. -- 4. Review the upgrade log (see example query) -- 5. exec wwv_flow_upgrade.recreate_public_synonyms('FLOWS2') -- -- NOTES -- Example Log Query: -- column upgrade_sequence format 9999 -- column upgrade_action format a44 wrapped -- column upgrade_error format a50 wrapped -- column upgrade_command format a50 wrapped -- set linesize 160 -- set pagesize 1000 -- select upgrade_sequence, upgrade_action, upgrade_error, upgrade_command -- from wwv_flow_upgrade_progress -- order by upgrade_id desc, upgrade_sequence desc -- -- Example Log Query 2: -- column upgrade_sequence format 9999 -- column upgrade_action format a44 wrapped -- column upgrade_error format a50 wrapped -- column upgrade_command format a50 wrapped -- set linesize 160 -- set pagesize 1000 -- select upgrade_sequence, upgrade_action, upgrade_error, upgrade_command -- from wwv_flow_upgrade_progress -- order by upgrade_id desc, upgrade_sequence -- as g_seq number := 0; g_upgrade_id number := 0; g_version_from varchar2(255); g_version_to varchar2(255); g_f number; c number; p number; i number; g_row_cnt number := 0; g_session_seq1 number := 0; g_session_seq2 number := 0; g_pref_name wwv_flow.flow_vc_arr; g_pref_value wwv_flow.flow_vc_arr; g_pref_desc wwv_flow.flow_vc_arr; procedure increment_session( p_old_schema in varchar2, p_new_schema in varchar2) ; procedure log ( p_action in varchar2, p_error in varchar2 default null, p_command in varchar2 default null) ; procedure drop_public_synonyms -- -- drop public synonyms to flow objects -- -- sqlplus example: -- exec wwv_flow_upgrade.drop_public_synonyms; -- ; procedure drop_private_synonyms ( -- -- drop private synonyms to objects -- -- sqlplus example: -- exec wwv_flow_upgrade.drop_private_synonyms('FLOWS2'; -- p_owner_to in varchar2) ; procedure create_private_synonyms ( -- -- create private synonyms only without dropping or granting -- sqlplus example: -- exec wwv_flow_upgrade.create_private_synonyms('FLOWS2') -- p_owner_to in varchar2) ; procedure create_public_synonyms ( -- -- create public synonyms only without dropping or granting -- sqlplus example: -- exec wwv_flow_upgrade.create_public_synonyms('FLOWS2') -- p_owner_to in varchar2) ; procedure grant_public_synonyms ( -- -- issue grants to public synonyms only without dropping or creating -- sqlplus example: -- exec wwv_flow_upgrade.grant_public_synonyms('FLOWS2') -- p_owner_to in varchar2) ; procedure recreate_public_synonyms ( -- -- create all needed public synonyms for a flows environent. -- -- sqlplus example: -- exec wwv_flow_upgrade.recreate_public_synonyms('FLOWS2') -- p_owner_to in varchar2) ; procedure copy_flow_meta_data ( -- -- Copy flows meta data from once schema to another. -- -- sqlplus example: -- exec wwv_flow_upgrade.copy_flow_meta_data('FLOWS1','FLOWS2') -- p_owner_from in varchar2, p_owner_to in varchar2) ; procedure remove_meta_data ( -- -- Remove meta data that is not owned by the internal user. -- WARNING running this procedure could remove your flows data! -- Without flow meta data you have not flows. -- p_schema in varchar2) ; procedure purge_log -- -- delete all entries in the flow upgrade log -- ; procedure remove_jobs -- -- Remove all of the standard dbms_jobs for a schema ; procedure create_jobs( -- -- Create all of the standard dbms_jobs for a given schema p_owner in varchar2) ; procedure flows_files_objects_create( -- -- Create the proper grants and synonyms for the flows_files schema -- used for file upload / download p_flow_owner in varchar2, p_owner in varchar2 default 'FLOWS_FILES' ) ; procedure flows_files_objects_remove( -- -- Remove the proper grants and synonyms for the flows_files schema -- used for file upload / download p_flow_owner in varchar2, p_owner in varchar2 default 'FLOWS_FILES' ) ; procedure meta_cleanup( p_from in varchar2, P_to in varchar2); procedure report_column_cleanup; procedure template_name_cleanup( p_owner in varchar2); procedure switch_schemas( -- -- This procedure should be run after an additional copy of flows is installed, -- as it is the final step in the upgrade process. -- Once this procedure is run, you will be running in the new version of flows. -- This will: -- 1. Remove all old dbms_jobs -- 2. Create all new dbms_jobs -- 3. Drop All public synonyms -- 4. Create all new public synonyms -- 5. Perform all necessary grants on public synoyms p_from in varchar2, P_to in varchar2) ; procedure copy_prefs ( -- -- Copys old preferences from schema1.WWV_FLOW_PLATFORM_PREFS -- to schema2.WWV_FLOW_PLATFORM_PREFS -- p_owner_from in varchar2, p_owner_to in varchar2) ; function to_template_id( p_in varchar2) return number; procedure upgrade_to_030200( p_owner in varchar2, p_from in varchar2) ; procedure sw_cleanup ( p_from in varchar2, p_to in varchar2) ; end wwv_flow_upgrade; / show errors