-- -- $Header: whdev/2.0/owb/oracle/wh/runtime/sql/pls/metadata/wb_rt_reset_location.pls /main/3 2011/06/15 18:41:52 rdong Exp $ -- -- wb_rt_reset_location.pls -- -- Copyright (c) 2010, 2011, Oracle and/or its affiliates. -- All rights reserved. -- -- NAME -- wb_rt_reset_location.pls -- -- DESCRIPTION -- The package specification of package wb_rt_reset_location -- -- NOTES -- 1, This package is used to upgrade the runtime and design time location details during upgrade -- Function fixDTLocation can update host, port, service, version, schema and user details for design time locations -- Function fixRTLocation can update host, port, service, version, schema, user and password details for runtime locations -- Function fixRTLocationPass can update password details for runtime locations -- Function fixRTFileSystemLocation can update path details for runtime Flat File locations and update user, password, host and path details for runtime FTP locations. -- -- 2, The model would be something like... -- Compile wb_rt_reset_location to owbsys -- grant execute on wb_rt_reset_location to owb_user -- -- 3, After upgrade to 11.2 workspace from the previous release -- As workspace owner run fixDTLocation to reset all the design time location details excepting password -- As workspace owner run fixRTLocation to reset all the runtime location details including password -- As any other workspace user run fixRTLocationPass to register the password for any locations where you may need to run jobs -- -- 4, User can create the wb_rtv_store_summary view as below, then "grant select on wb_rtv_store_summary to OWB_USER". -- Then users can do stuff like the following to generate a series of calls that set the location easily, they can copy paste this into a full script with variables etc.; -- select 'v_result := owbsys.wb_rt_reset_location.fixRTLocation ('''||store_name||''', '''||host||''', '''||port||''', '''||service||''', '''||version||''', '''||schema||''', '''||user||''', '''||password||''');' from owbsys.wb_rtv_store_summary; /* create or replace view wb_rtv_store_summary as SELECT "UNPIVOT"."STORE_NAME" "STORE_NAME", "UNPIVOT"."HOST" "HOST", "UNPIVOT"."PORT" "PORT", "UNPIVOT"."SERVICE" "SERVICE", "UNPIVOT"."VERSION" "VERSION", "UNPIVOT"."SCHEMA" "SCHEMA", "UNPIVOT"."USER" "USER", '' PASSWORD FROM (SELECT "STORE_NAME" "STORE_NAME", MIN(CASE WHEN "PARAMETER_NAME" = 'Host' THEN "PARAMETER_VALUE" ELSE NULL END) "HOST", MIN(CASE WHEN "PARAMETER_NAME" = 'Port' THEN "PARAMETER_VALUE" ELSE NULL END) "PORT", MIN(CASE WHEN "PARAMETER_NAME" = 'Service' THEN "PARAMETER_VALUE" ELSE NULL END) "SERVICE", MIN(CASE WHEN "PARAMETER_NAME" = 'Schema' THEN "PARAMETER_VALUE" ELSE NULL END) "SCHEMA", MIN(CASE WHEN "PARAMETER_NAME" = 'ConnectAs' THEN "PARAMETER_VALUE" ELSE NULL END) "USER", STORE_TYPE_VERSION "VERSION" FROM (SELECT "INGRP1"."STORE_NAME" "STORE_NAME", "WB_RTV_STORE_PARAMETERS"."PARAMETER_NAME" "PARAMETER_NAME", "WB_RTV_STORE_PARAMETERS"."PARAMETER_VALUE" "PARAMETER_VALUE", "INGRP1"."STORE_TYPE_VERSION" "STORE_TYPE_VERSION" FROM (SELECT "WB_RTV_STORES"."STORE_ID" "STORE_ID", "WB_RTV_STORES"."STORE_TYPE_ID" "STORE_TYPE_ID", "WB_RTV_STORES"."STORE_UOID" "STORE_UOID", "WB_RTV_STORES"."STORE_NAME" "STORE_NAME", "WB_RTV_STORES"."VERSION_TAG" "VERSION_TAG", "WB_RTV_STORES"."IS_DEPLOYMENT_TARGET" "IS_DEPLOYMENT_TARGET", "WB_RTV_STORES"."OWNER_UOID" "OWNER_UOID", "WB_RTV_STORES"."IS_LOCAL_TO_REPOS" "IS_LOCAL_TO_REPOS", NVL((SELECT "WB_RTV_DEF_STORE_TYPES_S"."STORE_TYPE_VERSION" "STORE_TYPE_VERSION" FROM OWBSYS."WB_RTV_DEF_STORE_TYPES" "WB_RTV_DEF_STORE_TYPES_S" WHERE ( "WB_RTV_DEF_STORE_TYPES_S".STORE_TYPE_NAME IN ('Oracle Database', 'Oracle Gateway', 'Oracle Workflow') and "WB_RTV_DEF_STORE_TYPES_S"."STORE_TYPE_ID" = "WB_RTV_STORES"."STORE_TYPE_ID" ) ), NULL) "STORE_TYPE_VERSION" FROM OWBSYS."WB_RTV_STORES" "WB_RTV_STORES" WHERE STORE_NAME != 'PlatformSchema') "INGRP1" , OWBSYS."WB_RTV_STORE_PARAMETERS" "WB_RTV_STORE_PARAMETERS") "OUTGRP1" GROUP BY "STORE_NAME",STORE_TYPE_VERSION ) "UNPIVOT" ; */ create or replace package wb_rt_reset_location is function fixRTLocation ( location_in in varchar2, host_in in varchar2, port_in in varchar2, service_in in varchar2, version_in in varchar2, schema_in in varchar2, user_in in varchar2, password_in in varchar2 ) return boolean; function fixDTLocation ( location_in in varchar2, host_in in varchar2, port_in in varchar2, service_in in varchar2, version_in in varchar2, schema_in in varchar2, user_in in varchar2 ) return boolean; function fixRTLocationPass ( location_in in varchar2, password_in in varchar2 ) return boolean; /* * This function is not officially supported. Use at your own risk. * function changeRTLocTypeFromHPSToNet * Purpose: change any Host:Port:Service Type Runtime Location to SQLNET Connection Type location */ function changeRTLocTypeFromHPSToNet ( location_in in varchar2, net_service_in in varchar2, version_in in varchar2, schema_in in varchar2, user_in in varchar2, password_in in varchar2 ) return boolean; /* * This function is not officially supported. Use at your own risk. * function changeDTLocTypeFromHPSToNet * Purpose: change any Host:Port:Service Type DesignTime Location to SQLNET Connection Type location */ function changeDTLocTypeFromHPSToNet ( location_in in varchar2, net_service_in in varchar2, version_in in varchar2, schema_in in varchar2, user_in in varchar2 ) return boolean; /* * function fixRTFileSystemLocation * location_type_in should be "FTP" for ftp location or "FILE" for general file location. * Purpose: Update property path for runtime flat file locations and update properties user, password, host and path for runtime ftp locations. */ function fixRTFileSystemLocation ( location_in in varchar2, location_type_in in varchar2, host_in in varchar2, user_in in varchar2, password_in in varchar2, rootpath_in in varchar2 ) return boolean; end wb_rt_reset_location; /