Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/ecm/ecm_util_pkgbody.sql /st_emcore_10.2.0.4.4db11.2.0.3/1 2011/07/22 07:37:41 niramach Exp $ Rem Rem ecm_util_pkgbody.sql Rem Rem Copyright (c) 2002, 2011, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem ecm_util_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem niramach 07/21/11 - Fix for bug 11697046 Rem niramach 05/26/10 - Fix for upgrade issue. Rem niramach 04/28/10 - XbranchMerge niramach_bug-8837368 & niramach_sqjinjectionfix from Rem st_emcore_10.2.0.1.0 Rem vkhizder 06/18/09 - XbranchMerge vkhizder_bug6503115_10g from Rem st_emcore_10.2.0.1.0 Rem vkhizder 04/18/08 - transferring Mark's fix for bug 5862075 into Rem EMCORE_10.2.0.4.1DB11_LINUX branch Rem rrawat 09/10/07 - Fix bug #6379261 Rem shnavane 09/06/07 - Fix bug #6379239 Rem shnavane 09/06/07 - Backport shnavane_bug-6379239 from main Rem vkhizder 08/07/07 - execute body of UPDATE_ECM_METADATA only once Rem during upgrade (bug-6157799) Rem amani 11/08/06 - Backport amani_bug-5623085 from main Rem amani 08/01/06 - Backport amani_bug-5184851 from main Rem agor 03/22/06 - Backport agor_bug-5105562 from Rem st_emcore_10.2.0.1.0 Rem audupi 12/11/05 - Backport audupi_bug-4397373 from main Rem amani 10/27/06 - For bug fix 5623085. Remvoing % for filter exact. Rem amani 07/04/06 - For bug fix 5184851 Rem agor 03/22/06 - fix 5105562 Rem akskumar 11/22/05 - bug-4623295 Rem audupi 11/16/05 - 4647800 - Open/close targets-cursor for each Rem home in get-clone-source Rem anusharm 08/16/05 - add search for exact values in Rem get_clone_from_source Rem asaraswa 07/15/05 - changing column names in history views Rem niramach 06/29/05 - Fix for bug 4438297. Rem mgoodric 06/23/05 - fix for upgrading from 10.1.0 - 10.2.0 as SYS Rem anusharm 05/12/05 - correcting sql inj in get_clone_src Rem gpalrech 04/08/05 - update so that 10.1 RAC homes are clonable Rem mnosseir 01/25/05 - add PRODUCT_NAMES to result set in Rem get_clone_source Rem mnosseir 12/21/04 - allow db 920x homes with oracle.jdbc.thin14 to Rem be clonable Rem anusharm 11/04/04 - setting l_might_be_hybrid_clonable to false for clustered homes Rem mgoodric 10/25/04 - move ECM_UTIL functions to MGMT_VIEW_UTIL Rem mnosseir 10/08/04 - mark 10gr1 RAC homes as non clonable Rem vkhizder 10/04/04 - adding refresh for ecm materialized views Rem mgoodric 10/02/04 - fix key value lookup Rem mgoodric 09/26/04 - fix CONFIG_HISTORY views returning key columns Rem mgoodric 09/23/04 - convert null key values to 'Unavailable' Rem niramach 09/22/04 - Removing INSERT_HIST_GROUPS procedure since Rem MGMT_ECM_HIST_FOR_GROUPS table is removed. Rem niramach 09/21/04 - Modifying get_hist_for_group procedure Rem mgoodric 09/15/04 - fix using MGMT_MESSAGE.GET_MESSAGE Rem mgoodric 09/12/04 - fix NLS routines to check of 'history_ui_on' Rem mgoodric 08/19/04 - add NLS routines Rem jmansur 08/11/04 - add NV() Rem agor 07/30/04 - add wrapper procedure for history groups Rem insertion Rem anusharm 06/14/04 - adding oui to product filter Rem awarkar 11/28/03 - Bug Fix For Issue#3280397 Rem jmansur 11/03/03 - bug 3233180 - in get_clone_source, do not return Rem non-clonable ias 904 homes Rem vkhizder 10/31/03 - adding functions for getting Rem patchsets/patches/bugs for a home Rem jmansur 10/28/03 - bug 3199957 - in get_clone_source, for 920*/904*, Rem include any patched oracle.rdbms or Rem oracle.iappserver.iappcore in version list, Rem since 920*/904* top-level versions are not patched Rem kchiasso 10/20/03 - bugfix 3206358 Rem awarkar 09/03/03 - Bug Fix For Issue#3056258 Rem groyal 08/28/03 - Enhance concat to support a limit Rem jmansur 08/21/03 - update get_clone_source to match latest design Rem jmansur 07/14/03 - return ad-hoc-clonable homes in GET_CLONE_SOURCE Rem vkhizder 05/01/03 - changing emd_url to host_name/target_name matching for finding targets host Rem jmansur 04/28/03 - honor is_clonable in GET_CLONE_SOURCE Rem shuberma 04/16/03 - Removing obsolete procedure Rem shuberma 02/12/03 - document new returned column in cursor Rem shuberma 02/12/03 - Adding emd_url to cursor Rem shuberma 01/03/03 - Adding procedure for clone home source Rem shuberma 12/23/02 - adding an optional argument to the concat_col method Rem shuberma 11/01/02 - Version number in wrong order Rem rmenon 10/18/02 - added CONCAT_COMPONENT_VERSION body Rem mgoodric 07/09/02 - Generalize patch wizard targets Rem skini 07/12/02 - Change target_name column size Rem kchiasso 06/18/02 - add oracle_ias to target_list Rem mgoodric 05/31/02 - Update ARU mapping scripts for Oracle Agent Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Created Rem CREATE OR REPLACE PACKAGE BODY ECM_UTIL IS --DEPRECATED: DONT USE THIS FUNCTION.THIS CAUSED SQL INJECTION ISSUE (Bug 8837368) --Removed the implementation that concatenates the input parameters into a SQL and executes it. -- (To avoid sql injection issue). --Function is not removed for backward compatability.Implementation is dummy. -- Function to return a concatenated list (space delimited by default) a the -- given table and column, restricted by given predicate. -- If the max varchar2 length is exceeded (VALUE_ERROR) a truncated -- result is returned. -- COL The name of the column, from the tab passed int. -- TAB The table (or query in parens) from which to select the column. -- PREDICATE - Used in a where clause to restrict the selection. -- DELIMETER Used to separate the column values in the returned string. -- COL_LIMIT Used to limit the number of COL values to return. If more then -- this limit exists the return string has an ellipsis added at -- the end FUNCTION CONCAT_COL ( COL VARCHAR2, TAB VARCHAR2, PREDICATE VARCHAR2, DELIMETER VARCHAR2 := ' ', COL_LIMIT NUMBER := -1) RETURN VARCHAR2 IS BEGIN RETURN ' '; END; FUNCTION CONCAT_RESULT_COL ( c1 RESULT_CURSOR, DELIMETER VARCHAR2 := ' ', COL_LIMIT NUMBER := -1) RETURN VARCHAR2 IS result VARCHAR2(4000); col_val VARCHAR2(1000); tmpDel VARCHAR2(10) := ''; ellipsis VARCHAR2(3) := '...'; cnt NUMBER := 0; BEGIN result := ''; LOOP FETCH c1 INTO col_val; EXIT WHEN c1%NOTFOUND; cnt := cnt + 1; IF (COL_LIMIT != -1 AND cnt > COL_LIMIT) THEN result := result || tmpDel || ellipsis; EXIT; END IF; result := result || tmpDel || col_val; tmpDel := DELIMETER; END LOOP; IF result IS NULL THEN result := ' '; END IF; RETURN result; EXCEPTION WHEN VALUE_ERROR THEN RETURN result; -- Truncated to value prior to value error. END; FUNCTION HOST_HOME_LIST ( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2 ) RETURN VARCHAR2 IS TYPE C1Type IS REF CURSOR; -- define weak REF CURSOR type c1 C1Type; result VARCHAR2(4000); BEGIN OPEN c1 FOR select c.container_location || '(' || c.container_name || ')' as home from mgmt_ecm_snapshot s, mgmt_inv_container c where s.is_current = 'Y' and s.snapshot_type = 'host_configuration' and s.snapshot_guid = c.snapshot_guid and target_name = p_target_name and target_type = p_target_type; result := CONCAT_RESULT_COL(c1, ','); CLOSE c1; RETURN result; EXCEPTION WHEN OTHERS THEN IF c1%ISOPEN THEN CLOSE c1; END IF; RETURN ' '; END HOST_HOME_LIST; FUNCTION PATCH_ADVISORY_LIST ( p_patch_guid IN RAW) RETURN VARCHAR2 IS TYPE C1Type IS REF CURSOR; -- define weak REF CURSOR type c1 C1Type; result VARCHAR2(4000); BEGIN OPEN c1 FOR SELECT distinct ADVISORY_NAME FROM MGMT_BUG_ADV_HOME_PATCH WHERE PATCH_GUID = p_patch_guid; result := CONCAT_RESULT_COL(c1, ','); CLOSE c1; RETURN result; EXCEPTION WHEN OTHERS THEN IF c1%ISOPEN THEN CLOSE c1; END IF; RETURN ' '; END PATCH_ADVISORY_LIST; FUNCTION FIXED_BUGS_IN_PATCH ( p_patch_guid IN RAW) RETURN VARCHAR2 IS TYPE C1Type IS REF CURSOR; -- define weak REF CURSOR type c1 C1Type; result VARCHAR2(4000); BEGIN OPEN c1 FOR SELECT distinct BUG_NUMBER FROM mgmt_inv_patch_fixed_bug WHERE PATCH_GUID = p_patch_guid; result := CONCAT_RESULT_COL(c1, ','); CLOSE c1; RETURN result; EXCEPTION WHEN OTHERS THEN IF c1%ISOPEN THEN CLOSE c1; END IF; RETURN ' '; END FIXED_BUGS_IN_PATCH; FUNCTION CSA_CLIENT_RULE_LIST ( p_snapshot_id IN RAW) RETURN VARCHAR2 IS TYPE C1Type IS REF CURSOR; -- define weak REF CURSOR type c1 C1Type; result VARCHAR2(4000); BEGIN OPEN c1 FOR SELECT r.name FROM mgmt$csa_host_rules r WHERE r.snapshot_id = p_snapshot_id and r.status > 15; result := CONCAT_RESULT_COL(c1, ',', 50); CLOSE c1; RETURN result; EXCEPTION WHEN OTHERS THEN IF c1%ISOPEN THEN CLOSE c1; END IF; RETURN ' '; END CSA_CLIENT_RULE_LIST; FUNCTION CONFIG_STD_KEYWORD_LIST ( p_cs_guid IN RAW, p_language_code IN VARCHAR2 DEFAULT NULL, p_country_code IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS TYPE C1Type IS REF CURSOR; -- define weak REF CURSOR type c1 C1Type; result VARCHAR2(4000); BEGIN IF(p_language_code IS NULL OR p_country_code IS NULL) THEN OPEN c1 FOR SELECT nvl(kem.message, k.keyword) FROM mgmt_cs_keyword k, (select message, message_id from mgmt_messages where subsystem = 'CONFIG_STD' and language_code = 'en' and country_code = ' ') kem WHERE k.cs_guid = p_cs_guid and k.keyword = kem.message_id(+); ELSE OPEN c1 FOR SELECT NVL(km.message, NVL(kem.message, k.keyword)) FROM mgmt_cs_keyword k, (select message, message_id from mgmt_messages where subsystem = 'CONFIG_STD' and language_code = p_language_code and country_code = p_country_code ) km, (select message, message_id from mgmt_messages where subsystem = 'CONFIG_STD' and language_code = 'en' and country_code = ' ') kem WHERE k.cs_guid = p_cs_guid and k.keyword = kem.message_id(+) and k.keyword = km.message_id(+); END IF; result := CONCAT_RESULT_COL(c1, ';'); CLOSE c1; RETURN result; EXCEPTION WHEN OTHERS THEN IF c1%ISOPEN THEN CLOSE c1; END IF; RETURN ' '; END CONFIG_STD_KEYWORD_LIST; FUNCTION CSA_TARGET_LIST ( p_application_id IN VARCHAR2) RETURN VARCHAR2 IS TYPE C1Type IS REF CURSOR; -- define weak REF CURSOR type c1 C1Type; result VARCHAR2(4000); BEGIN OPEN c1 FOR SELECT t.target_name FROM mgmt_targets t, mgmt_ecm_csa_appid_target_map map WHERE map.appid = p_application_id and map.target_guid = t.target_guid; result := CONCAT_RESULT_COL(c1, ',', 10); CLOSE c1; RETURN result; EXCEPTION WHEN OTHERS THEN IF c1%ISOPEN THEN CLOSE c1; END IF; RETURN ' '; END CSA_TARGET_LIST; FUNCTION HOST_HOME_SID_LIST( v_host_name IN VARCHAR2, v_oracle_home IN VARCHAR2 ) RETURN VARCHAR2 IS result VARCHAR2(2000); sid_name VARCHAR2(64); comma VARCHAR2(2); CURSOR c1 IS SELECT p.property_value FROM mgmt_targets t, mgmt_targets h, mgmt_target_properties p WHERE h.target_type = 'host' AND h.target_name = v_host_name AND h.target_name = t.host_name AND t.target_type = 'oracle_database' AND t.target_guid = p.target_guid AND p.property_name = 'SID' AND EXISTS ( SELECT * FROM mgmt_target_properties p2 WHERE t.target_guid = p2.target_guid AND p2.property_name = 'OracleHome' AND p2.property_value = v_oracle_home ); BEGIN result := ''; comma := ''; OPEN C1; LOOP FETCH c1 INTO sid_name; EXIT WHEN c1%NOTFOUND; result := result || comma || sid_name; comma := ','; END LOOP; RETURN result; END; FUNCTION HOST_HOME_LSNR_LIST( v_host_name IN VARCHAR2, v_oracle_home IN VARCHAR2 ) RETURN VARCHAR2 IS result VARCHAR2(2000); lsnr_name VARCHAR2(64); comma VARCHAR2(2); CURSOR c1 IS SELECT p.property_value FROM mgmt_targets t, mgmt_targets h, mgmt_target_properties p WHERE h.target_type = 'host' AND h.target_name = v_host_name AND h.target_name = t.host_name AND t.target_type = 'oracle_listener' AND t.target_guid = p.target_guid AND p.property_name = 'LsnrName' AND EXISTS ( select * FROM mgmt_target_properties p2 WHERE t.target_guid = p2.target_guid AND p2.property_name = 'OracleHome' AND p2.property_value = v_oracle_home ); BEGIN result := ''; comma := ''; OPEN C1; LOOP FETCH c1 INTO lsnr_name; EXIT WHEN c1%NOTFOUND; result := result || comma || lsnr_name; comma := ','; END LOOP; RETURN result; END; FUNCTION HOST_HOME_TARGET_LIST( v_host_name IN VARCHAR2, v_oracle_home IN VARCHAR2, v_target_type IN VARCHAR2 ) RETURN VARCHAR2 IS result VARCHAR2(2000); target_name MGMT_TARGETS.target_name%TYPE; target_type MGMT_TARGETS.target_type%TYPE; comma VARCHAR2(2); CURSOR c1 IS SELECT t.target_name, t.target_type FROM mgmt_targets t, mgmt_targets h, mgmt_target_properties p WHERE h.target_type = 'host' AND h.target_name = v_host_name AND h.target_name = t.host_name AND t.target_guid = p.target_guid AND p.property_name = 'OracleHome' AND EXISTS ( SELECT * FROM mgmt_target_properties p2 WHERE t.target_guid = p2.target_guid AND p2.property_name = 'OracleHome' AND p2.property_value = v_oracle_home ); BEGIN result := ''; comma := ''; OPEN C1; LOOP FETCH c1 INTO target_name, target_type; EXIT WHEN c1%NOTFOUND; IF (v_target_type IS NULL OR target_type = v_target_type) THEN result := result || comma || target_name; comma := ', '; END IF; END LOOP; RETURN result; END; -- Given a text string, try to figure out if the string is 'NUMBER', 'DATE', or -- 'TEXT' by trying various conversion functions and catching exceptions FUNCTION GET_GENERIC_VALS_DATATYPE( v_textInQuestion IN VARCHAR2 ) RETURN VARCHAR2 IS numberTry NUMBER; timestampTry TIMESTAMP; result VARCHAR2(10); BEGIN result := 'TEXT'; -- First try to do a numeric conversion BEGIN numberTry := TO_NUMBER( v_textInQuestion ); result := 'NUMBER'; EXCEPTION WHEN OTHERS THEN -- Now try to do a timestamp conversion BEGIN timestampTry := TO_TIMESTAMP( v_textInQuestion ); result := 'DATE'; EXCEPTION WHEN OTHERS THEN result := 'TEXT'; END; END; RETURN result; END GET_GENERIC_VALS_DATATYPE; FUNCTION CONCAT_COMPONENT_VERSION ( p_component_name VARCHAR2, p_container_guid RAW ) RETURN VARCHAR2 IS result VARCHAR2(2000); vers VARCHAR(64); comma VARCHAR2(2); CURSOR c1 IS select decode (component_patch.version, NULL, component.version, component_patch.version) as version FROM MGMT_INV_VERSIONED_PATCH component_patch, MGMT_INV_COMPONENT component WHERE component.component_guid = component_patch.component_guid(+) and component.name = p_component_name and container_guid = p_container_guid ORDER BY 1; BEGIN result := ''; comma := ''; OPEN C1; LOOP FETCH c1 INTO vers; EXIT WHEN c1%NOTFOUND; result := result || comma || vers; comma := ', '; END LOOP; RETURN result; END; -- this function splits a comma separated list of component names into a table FUNCTION tokenize_it( i_str IN VARCHAR2, delimeter VARCHAR2) RETURN TOKEN_LIST IS l_n NUMBER; l_data TOKEN_LIST := TOKEN_LIST(); l_str VARCHAR2(4096) := i_str || delimeter; BEGIN LOOP l_n := instr( l_str, delimeter); exit when (nvl(l_n,0) = 0); l_data.extend; l_data( l_data.count ) := ltrim( rtrim(substr(l_str, 1, l_n-1)) ); l_str := substr( l_str, l_n+1 ); END LOOP; -- l_n := 1; -- LOOP -- exit when (l_n > l_data.count); -- dbms_output.put_line(l_data(l_n)); -- l_n := l_n +1; -- END LOOP; return l_data; END; -- this function determines whether a component(version) exists in the tokenized list of comps provided FUNCTION find_in_list( i_comp IN VARCHAR2, i_comp_ver IN VARCHAR2, i_comp_list IN TOKEN_LIST ) RETURN BOOLEAN IS l_in_list BOOLEAN := FALSE; l_comp VARCHAR2(64); l_comp_version VARCHAR2(20); l_n NUMBER; BEGIN IF i_comp_list IS NOT NULL THEN FOR i IN i_comp_list.FIRST .. i_comp_list.LAST LOOP l_n := instr(i_comp_list(i), ':'); IF (nvl(l_n,0) = 0) THEN l_comp := i_comp_list(i); l_comp_version := ''; ELSE l_comp := substr(i_comp_list(i), 1 , l_n - 1); l_comp_version := ltrim( rtrim(substr(i_comp_list(i), l_n + 1 , length(i_comp_list(i) ))) ) || '%'; END IF; IF ( i_comp = l_comp ) THEN IF (l_comp_version IS NOT NULL) THEN IF (i_comp_ver like l_comp_version) THEN l_in_list := TRUE; ELSE l_in_list := FALSE; END IF; ELSE l_in_list := TRUE; END IF; ELSE l_in_list := FALSE; END IF; exit when (l_in_list = TRUE); END LOOP; END IF; return l_in_list; END; -- This function returns the list of homes for populating the clone home -- source. Along with the home, the list of targets using that home and -- the list of products (Top level) in the home. -- The arguments are used to narrow the list. -- p_searchExact are used to search for exact values instead of time consuming "like" -- p_searchCol - The search column to narrow the result. Can be one of: -- ECM_UTIL.APP_SERVER_NAME, ECM_UTIL.DB_NAME, ECM_UTIL.HOST_NAME, -- ECM_UTIL.HOME_LOCATION, ECM_UTIL.OCS_NAME, ECM_UTIL.HOME_NAME, -- ECM_UTIL.PLATFORM, ECM_UTIL.PRODUCT_NAME -- The actual string values of this are constants defined in this package. -- APP_SERVER_NAME, DB_NAME, and OCS_NAME are the actual target type names -- used in EM, e.g. 'oracle_ias', 'oracle_database' and 'oracle_ocs'. -- The ref cursor points to records with the following columns: -- HOME_LOCATION -- This is the path of the oracle home -- HOME_NAME -- This is the name of the oracle home -- HOST_NAME -- This is the host name of on which the oracle home resides. -- PLATFORM -- This is the platform (e.g. SunOS) of the host on which this oracle -- home resides. -- ADDRESS_SIZE -- This is the address_size (e.g. 32, 64...) of the host on which this -- home resides. -- TARGETS -- This is a comma separated list of target name whose oracle home -- property indicates this oracle home. The list is a string. -- PRODUCTS -- This is a comma separated list of top level products that are installed -- in this home. The list is a string. -- EMD_URL -- This is the emd_url property from the host's mgmt_target entry. -- OUI_PLATFORM -- The oui platform id of the home, e.g., 453 for solaris/sparc -- HOME_TYPE -- When the home is ad-hoc-clonable, or is an ias 9.0.4.0.0 home, or -- is a restaged rdbms 9.2 home, this is the type of home, i.e., -- what product type is in the home, e.g., 'db' or 'ias'. NULL if -- home is not one of these. 904 and restaged 92 homes may be -- pre-cloned via an oms-resident ClonerStage -- IS_AD_HOC_CLONABLE -- 'true' if home is ad-hoc-clonable, i.e., must be pre-cloned and -- cloned/fixed-up via an oms-resident ClonerStage -- PRODUCT_VERSIONS -- if HOME_TYPE is non-NULL, this contains a comma-separated list of -- the product version(s) in the home, i.e., the version of -- oracle.server and/or oracle.client for an rdbms home, or version of -- oracle.iappserver.iapptop for an ias home. NULL otherwise. FUNCTION GET_CLONE_SOURCE( p_searchExact VARCHAR2, p_searchCol VARCHAR2, p_searchVal VARCHAR2, p_comp_list_db VARCHAR2, p_comp_list_as VARCHAR2, p_hostName VARCHAR2) RETURN CLONE_SOURCE_REF IS TYPE C1Type IS REF CURSOR; -- define weak REF CURSOR type c1 C1Type; l_batch_size INTEGER; -- All targets for host-locations. CURSOR C2(c_hl VARCHAR2, c_ho VARCHAR2) IS select t.target_name from mgmt_targets t, mgmt_target_properties tphl where tphl.target_guid = t.target_guid and tphl.property_name = 'OracleHome' and tphl.property_value = c_hl and t.host_name = c_ho order by 1; TYPE arr_target_name_type IS TABLE OF mgmt_targets.target_name%TYPE; l_arr_target_names arr_target_name_type; return_result CLONE_SOURCE_TABLE; cloneSourceRef CLONE_SOURCE_REF; comp_list_db TOKEN_LIST; comp_list_as TOKEN_LIST; tempStr VARCHAR2(4000); tempRec CLONE_SOURCE_RECORD; isNewHomeOrFinalRecord BOOLEAN := true; last_hl VARCHAR2(128) := NULL; last_ho VARCHAR2(256) := NULL; l_home_location VARCHAR2(128); l_container_guid RAW(16); l_container_name VARCHAR2(64); l_cluster_nodes MGMT_INV_CONTAINER_PROPERTY.PROPERTY_VALUE%TYPE; l_host_name VARCHAR2(156); l_platform VARCHAR2(150); l_address_size VARCHAR2(10); l_product_name VARCHAR2(128); l_version VARCHAR2(128); l_emd_url VARCHAR2(2000); comma VARCHAR2(10) := ''; product_separator VARCHAR2(10) := ''; version_separator VARCHAR2(10) := ''; temp_result CLONE_SOURCE_RECORD := NULL; matchFound BOOLEAN := false; sqlStmt VARCHAR2(4000); filter VARCHAR(1000); escape VARCHAR(20); l_number_of_products_per_home NUMBER := 0; l_found_db BOOLEAN := FALSE; l_found_ias BOOLEAN := FALSE; l_product_internal_name MGMT_INV_COMPONENT.NAME%TYPE; l_current_is_clonable MGMT_INV_CONTAINER.IS_CLONABLE%TYPE; l_is_clonable MGMT_INV_CONTAINER.IS_CLONABLE%TYPE; l_oui_platform MGMT_INV_CONTAINER.OUI_PLATFORM%TYPE; l_might_be_ad_hoc_clonable BOOLEAN := TRUE; -- home can use oms ClonerStage, but is fixed up via oui updateHome() l_might_be_hybrid_clonable BOOLEAN := TRUE; l_is_top_level MGMT_INV_COMPONENT.IS_TOP_LEVEL%TYPE; l_patch_version MGMT_INV_VERSIONED_PATCH.VERSION%TYPE; FUNCTION get_batch_size (p_str IN VARCHAR2) RETURN INTEGER IS l_batch INTEGER := 500; BEGIN SELECT parameter_value INTO l_batch FROM mgmt_parameters WHERE parameter_name = p_str; RETURN l_batch; EXCEPTION WHEN others THEN RETURN l_batch; END; BEGIN l_batch_size := get_batch_size('ecm_get_clone_source_batch_size'); -- First determine what needs to be added, if anything to the outer query -- in order to filter the results according to the args passed in. -- All filters are done on this query except the product_name filter, which -- is done in this plsql code instead of as part of the query. This is because -- the product name information is processed in this function in order to -- concatenate the result as a single string. If any one of the products -- matches, we include the row. IF p_searchCol IS NOT NULL THEN -- if the search col is a target type, then simply append an exists -- onto the query. IF (p_searchExact = 'true') THEN escape := ''; ELSE escape := ' escape ''#'' '; END IF; --escape := ' escape ''#'' '; IF p_searchCol = APP_SERVER_NAME OR p_searchCol = DB_NAME OR p_searchCol = OCS_NAME THEN escape := ''; filter := ' AND exists ( select * from mgmt_targets st, mgmt_targets sh, mgmt_target_properties sp where st.target_type = ' || em_check.enquote_literal(replace(p_searchCol, '''', '''''')) || ' and upper(st.target_name) like upper(' || em_check.enquote_literal(replace('%' || p_searchVal || '%', '''', '''''')) || ') escape ''#'' and st.host_name = sh.target_name and sh.target_name = s.target_name and sp.target_guid = st.target_guid and sp.property_name = ''OracleHome'' and sp.property_value = c.container_location )'; ELSE IF p_searchCol = HOST_NAME THEN -- For host name, just add a check against the host name value from the query. IF (p_searchExact = 'true') THEN filter := ' AND upper(s.target_name) = upper(' || em_check.enquote_literal(replace(p_searchVal, '''', '''''')) || ')'; ELSE filter := ' AND upper(s.target_name) like upper(' || em_check.enquote_literal(replace('%' || p_searchVal || '%', '''', '''''')) || ')'; END IF; ELSE IF p_searchCol = HOME_LOCATION THEN IF (p_hostName IS NOT NULL) THEN IF (p_searchExact = 'true') THEN filter := ' AND upper(c.container_location) = upper(' || em_check.enquote_literal(replace(p_searchVal, '''', '''''')) || ') AND upper(s.target_name) = upper('|| em_check.enquote_literal(replace(p_hostName, '''', '''''')) || ')'; ELSE filter := ' AND upper(c.container_location) like upper(' || em_check.enquote_literal(replace('%' || p_searchVal || '%', '''', '''''')) || ') AND upper(s.target_name) = upper(' || em_check.enquote_literal(replace(p_hostName, '''', '''''')) || ')'; END IF; ELSE -- For home_location, just add a check against the home_location value from the query. IF (p_searchExact = 'true') THEN filter := ' AND upper(c.container_location) = upper(' || em_check.enquote_literal(replace(p_searchVal, '''', '''''')) || ')'; ELSE filter := ' AND upper(c.container_location) like upper(' || em_check.enquote_literal(replace('%' || p_searchVal || '%', '''', '''''')) || ')'; END IF; END IF; ELSE IF p_searchCol = HOME_NAME THEN -- For home_name, just add a check against the home_name value from the query. filter := ' AND upper(c.container_name) like upper(' || em_check.enquote_literal(replace('%' || p_searchVal || '%', '''', '''''')) || ')'; ELSE IF p_searchCol = PLATFORM THEN -- For platform, just add a check against the platform value from the query. filter := ' AND upper(os.name) like upper(' || em_check.enquote_literal(replace('%' || p_searchVal || '%', '''', '''''')) || ')'; ELSE IF p_searchCol = PRODUCT_NAME THEN -- For product name, we don't construct filter here. escape := ''; END IF; END IF; END IF; END IF; END IF; END IF; ELSE filter := ''; escape := ''; END IF; IF p_comp_list_db IS NOT NULL THEN comp_list_db := tokenize_it(p_comp_list_db, ','); END IF; IF p_comp_list_as IS NOT NULL THEN comp_list_as := tokenize_it(p_comp_list_as, ','); END IF; -- The main query end up being a catenation of the query that returns -- the home info, combined with the filter. sqlStmt := 'select c.container_location as home_location, c.container_name, c.container_guid, s.target_name as host_name, os.name as platform, replace(os.address_length_in_bits, ''-bit'','''') as address_size, p.external_name as product_name, decode(patch.version, NULL, p.version, patch.version) as version, h.emd_url as emd_url, p.name, c.is_clonable, c.oui_platform, p.is_top_level, patch.version as patch_version from mgmt_ecm_snapshot s, mgmt_hc_os_summary os, mgmt_inv_container c, mgmt_inv_component p, mgmt_inv_versioned_patch patch, mgmt_targets h where s.target_type = ''host'' and s.snapshot_type = ''host_configuration'' and s.is_current = ''Y'' and h.target_name = s.target_name and h.target_type = s.target_type and os.snapshot_guid = s.snapshot_guid and c.snapshot_guid = s.snapshot_guid and c.container_guid = p.container_guid and ((p.is_top_level = ''Y'') or (p.name = ''oracle.rdbms'') or (p.name = ''oracle.iappserver.iappcore'')) and p.component_guid = patch.component_guid(+)' || filter || escape || ' order by 3,1,6,7'; -- This is a temporary table holding the results. return_result := CLONE_SOURCE_TABLE(); -- The C2 query is a query on the targets for this home. It will be -- folded into the home query. -- The C1 query gets all the homes. OPEN C1 FOR sqlStmt ; LOOP -- C1 Controls the loop. We don't see targets that have no valid home. FETCH C1 INTO l_home_location, l_container_name, l_container_guid, l_host_name, l_platform, l_address_size, l_product_name, l_version, l_emd_url, l_product_internal_name, l_current_is_clonable, l_oui_platform, l_is_top_level, l_patch_version; -- EXIT WHEN C1%NOTFOUND; -- exit when last row is fetched -- Since we are combining products for each home in a single string -- we need to track when we get to the next home. This compares the -- home identifying columns from the currently fetched row, with the previously -- fetched row. -- NULL <> NULL IF last_hl = l_home_location AND last_ho = l_host_name AND C1%FOUND THEN -- If they are the same, then this is not -- a new home. isNewHomeOrFinalRecord := FALSE; ELSE -- if they aren't the same, then the newly fetch row is a new home. last_hl := l_home_location; last_ho := l_host_name; l_cluster_nodes := null; isNewHomeOrFinalRecord := TRUE; END IF; IF isNewHomeOrFinalRecord THEN -- Save the last row now instead of when it was first encountered so we -- can filter based on boolean tests performed while concatenating its -- strings. Only save if row passes product filter. Match Found is -- true if the filtering is on product_name and the product name was -- found on this home, or true if the filtering is not on product name. IF temp_result IS NOT NULL AND -- skip saving row on first loop iter matchFound THEN -- If home is not clonable and we have not ruled out ad hoc -- clonability yet, check further for whether this is an -- ad-hoc-clonable home IF (l_is_clonable = 'N') AND l_might_be_ad_hoc_clonable THEN -- maximum number of top-levels in an ad-hoc clonable home is 4: -- oracle.server, oracle.client, orale.swd.oui, and -- oracle.java.jdbc.thin14 IF (l_number_of_products_per_home <= 4) THEN -- Found ad hoc clonable home: -- number of products is either 1 or (it is 2 and only dbs) -- NOTE: ASSUMPTION: this code allows two DB servers or two DB clients -- but we do not believe that it is possible to have two versions of -- DB server or client installed in the same home. -- Already checked that home only had db components. temp_result.home_type := 'db'; temp_result.is_ad_hoc_clonable := 'true'; ELSE -- turns out this is not an ad hoc clonable home after all.. -- don't clear product_versions since not returning row anyway l_might_be_ad_hoc_clonable := FALSE; END IF; -- If home is clonable and we haven't ruled out hybrid clonability -- yet, check further for whether this is a hybrid-clonable home ELSIF (l_is_clonable = 'Y') AND l_might_be_hybrid_clonable THEN IF (l_number_of_products_per_home = 1) OR ((l_number_of_products_per_home = 2) AND (NOT l_found_ias)) THEN -- Found hybrid-clonable home -- number of products is either 1 or (it is 2 and only dbs) -- NOTE: ASSUMPTION: this code allows two DB servers or two DB clients -- but we do not believe that it is possible to have two versions of -- DB server or client installed in the same home. IF (l_found_db) THEN temp_result.home_type := 'db'; ELSE temp_result.home_type := 'ias'; END IF; -- temp_result.is_ad_hoc_clonable remains 'false' ELSE -- turns out this is not a hybrid-clonable home l_might_be_hybrid_clonable := FALSE; temp_result.product_versions := NULL; END IF; -- If home is built-in-clonable, clear version info ELSIF (l_is_clonable = 'Y') THEN temp_result.product_versions := NULL; END IF; -- return this home if it's flagged as clonable or is -- ad-hoc-clonable, and is not a non-clonable ias home IF (l_is_clonable = 'Y' OR l_might_be_ad_hoc_clonable) AND (NOT (l_found_ias AND (NOT l_might_be_hybrid_clonable))) THEN return_result.EXTEND; return_result(return_result.LAST) := temp_result; END IF; END IF; -- If this was "new home" due to this being the last record, exit EXIT WHEN C1%NOTFOUND; -- reset indicators since we're processing a new row l_found_ias := FALSE; l_found_db := FALSE; l_might_be_ad_hoc_clonable := TRUE; l_number_of_products_per_home := 0; l_is_clonable := l_current_is_clonable; l_might_be_hybrid_clonable := TRUE; -- Since this is new, then reset matchFound to true if not filtering on -- product name. False otherwise. In that case, it will only be set -- to true for this home if a product that matches is found. matchFound := NVL(p_searchCol,' ') <> PRODUCT_NAME; temp_result := CLONE_SOURCE_RECORD( l_home_location, l_container_name, l_host_name, l_platform, l_address_size, NULL, l_product_name || ' ' || l_version, l_emd_url, l_oui_platform, NULL, 'false', l_version, l_product_internal_name); -- Loop through row from the other cursor (C2), until there are -- none left, comma := ''; OPEN C2(NVL(last_hl,' '), NVL(last_ho,' ')); LOOP FETCH C2 bulk collect INTO l_arr_target_names limit l_batch_size; EXIT WHEN l_arr_target_names.COUNT = 0; FOR j IN l_arr_target_names.FIRST..l_arr_target_names.LAST LOOP temp_result.targets := temp_result.targets || comma || l_arr_target_names(j); comma := ', '; END LOOP; END LOOP; CLOSE C2; -- if product is not top-level, remove it from product list IF (l_is_top_level <> 'Y') THEN temp_result.products := NULL; temp_result.product_internal_names := NULL; product_separator := ''; ELSE product_separator := ', '; END IF; -- if product is not top-level, and not patched (NULL patch version), -- remove it from version list. NOTE this leaves patched rep component -- versions in list for 10g+ also, but list is not returned for 10g+ IF ((l_is_top_level <> 'Y') AND (l_patch_version IS NULL)) THEN temp_result.product_versions := NULL; version_separator := ''; ELSE version_separator := ', '; END IF; ELSE -- Processing product from the same home -- Add the product to the product list, if it's top-level IF (l_is_top_level = 'Y') THEN temp_result.products := temp_result.products || product_separator || l_product_name || ' ' || l_version; temp_result.product_internal_names := temp_result.product_internal_names || product_separator || l_product_internal_name; product_separator := ', '; END IF; -- Add the version to the version list, if it's top-level or if patched IF (l_is_top_level = 'Y') OR (l_patch_version IS NOT NULL) THEN temp_result.product_versions := temp_result.product_versions || version_separator || l_version; version_separator := ', '; END IF; l_is_clonable := l_current_is_clonable; END IF; -- if we are filtering on product name, then here we can test to see -- if any of the products matched. IF (l_is_top_level = 'Y') THEN matchFound := matchFound OR ( p_searchCol = PRODUCT_NAME AND upper(l_product_name) LIKE UPPER('%' || p_searchVal || '%') escape '#' ); l_number_of_products_per_home := l_number_of_products_per_home + 1; END IF; -- If the home is not clonable, keep track of what is in it so we can -- later determine whether the home is ad hoc clonable IF (l_is_clonable = 'N') AND (l_is_top_level = 'Y') THEN IF (find_in_list(l_product_internal_name, l_version, comp_list_db)) THEN l_found_db := true; ELSE -- This home is definitely NOT ad hoc clonable since this top -- level product cannot belong to an ad hoc clonable home l_might_be_ad_hoc_clonable := false; END IF; -- If the home is clonable, but contains ias 9.0.4* mid-tier or contains -- rdbms 9.2.0* server and/or client (true for "restaged" 9.2 homes), -- keep track of what is in it so we can later determine whether -- HOME_TYPE and PRODUCT_VERSIONS should be returned ELSIF (l_is_clonable = 'Y') AND (l_is_top_level = 'Y') THEN IF (find_in_list(l_product_internal_name, l_version, comp_list_as)) THEN l_found_ias := true; ELSIF (find_in_list(l_product_internal_name, l_version, comp_list_db)) THEN l_found_db := true; -- ELSIF ((l_product_internal_name = 'oracle.server') OR -- (l_product_internal_name = 'oracle.client')) AND -- (l_version like '10.1.0%') -- 10gR1 database home -- THEN -- BEGIN -- -- 10gR1 RAC homes are not clonable -- select cp.property_value -- into l_cluster_nodes -- from mgmt_inv_container_property cp -- where -- cp.property_name = 'CLUSTER_NODES' AND -- cp.container_guid = l_container_guid; -- EXCEPTION -- WHEN NO_DATA_FOUND THEN -- l_cluster_nodes := NULL; -- l_might_be_hybrid_clonable := false; -- END; -- IF l_cluster_nodes IS NOT NULL -- THEN -- matchFound := FALSE; -- END IF; ELSE -- This home is built-in-clonable, i.e., may not be pre-cloned via -- an oms-resident ClonerStage l_might_be_hybrid_clonable := false; END IF; END IF; END LOOP; CLOSE C1; OPEN cloneSourceRef FOR SELECT * FROM table( CAST( return_result AS CLONE_SOURCE_TABLE)); RETURN cloneSourceRef; END GET_CLONE_SOURCE; -- Function to return a concatenated list of patchsets for a given Oracle Home -- If the max varchar2 length is exceeded (VALUE_ERROR) a truncated -- result is returned. -- p_home_id Guid of the HOME whose patchsets are of interest. -- p_delimiter Used to separate the patchsets in the returned string. FUNCTION PATCHSETS_IN_HOME ( p_home_id IN RAW, p_delimiter IN VARCHAR2 ) RETURN VARCHAR2 IS TYPE T_VARCHAR2_TABLE IS TABLE OF VARCHAR2(4000); l_ps_names T_VARCHAR2_TABLE; l_ps_versions T_VARCHAR2_TABLE; l_result VARCHAR2(4000); BEGIN BEGIN SELECT external_name, version BULK COLLECT INTO l_ps_names, l_ps_versions FROM mgmt_inv_patchset WHERE container_guid = p_home_id; IF (l_ps_names IS NULL OR l_ps_names.COUNT = 0) THEN RETURN NULL; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END; BEGIN l_result := l_ps_names(1) || ' ' || l_ps_versions(1); -- unlikely event of more than 1 patchset FOR i IN 2..l_ps_names.COUNT LOOP l_result := l_result || p_delimiter || l_ps_names(i) || ' ' || l_ps_versions(i); END LOOP; RETURN l_result; EXCEPTION WHEN VALUE_ERROR THEN RETURN l_result; -- Truncated to value prior to value error. END; END PATCHSETS_IN_HOME; -- Function to return a concatenated list of interim patches for a -- given Oracle Home. If the max varchar2 length is exceeded (VALUE_ERROR) -- a truncated result is returned. -- p_home_id Guid of the HOME whose interim patches are of interest. -- p_delimiter Used to separate the patches in the returned string. FUNCTION INTERIM_PATCHES_IN_HOME ( p_home_id IN RAW, p_delimiter IN VARCHAR2 ) RETURN VARCHAR2 IS TYPE T_VARCHAR2_TABLE IS TABLE OF VARCHAR2(4000); l_patch_ids T_VARCHAR2_TABLE; l_result VARCHAR2(4000); BEGIN BEGIN SELECT id BULK COLLECT INTO l_patch_ids FROM mgmt_inv_patch WHERE container_guid = p_home_id; IF (l_patch_ids IS NULL OR l_patch_ids.COUNT = 0) THEN RETURN NULL; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END; BEGIN l_result := l_patch_ids(1); FOR i IN 2..l_patch_ids.COUNT LOOP l_result := l_result || p_delimiter || l_patch_ids(i); END LOOP; RETURN l_result; EXCEPTION WHEN VALUE_ERROR THEN RETURN l_result; -- Truncated to value prior to value error. END; END INTERIM_PATCHES_IN_HOME; -- Function to return a concatenated list of bugs fixed by interim patches for a -- given Oracle Home. If the max varchar2 length is exceeded (VALUE_ERROR) -- a truncated result is returned. -- p_home_id Guid of the HOME whose fixed bugs are of interest. -- p_delimiter Used to separate the bugs in the returned string. FUNCTION FIXED_BUGS_IN_HOME ( p_home_id IN RAW, p_delimiter IN VARCHAR2 ) RETURN VARCHAR2 IS TYPE T_VARCHAR2_TABLE IS TABLE OF VARCHAR2(4000); l_bug_ids T_VARCHAR2_TABLE; l_result VARCHAR2(4000); BEGIN BEGIN SELECT distinct b.bug_number BULK COLLECT INTO l_bug_ids FROM mgmt_inv_patch_fixed_bug b, mgmt_inv_patch p WHERE p.container_guid = p_home_id AND p.patch_guid = b.patch_guid; IF (l_bug_ids IS NULL OR l_bug_ids.COUNT = 0) THEN RETURN NULL; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END; BEGIN l_result := l_bug_ids(1); FOR i IN 2..l_bug_ids.COUNT LOOP l_result := l_result || p_delimiter || l_bug_ids(i); END LOOP; RETURN l_result; EXCEPTION WHEN VALUE_ERROR THEN RETURN l_result; -- Truncated to value prior to value error. END; END FIXED_BUGS_IN_HOME; -- INSERT_AVAIL_SEARCHES -- This procedure is for integrators to add searches. -- Called from available_searches.sql PROCEDURE INSERT_AVAIL_SEARCHES( targettype VARCHAR2, classname VARCHAR2, searchtype VARCHAR2) IS BEGIN INSERT INTO MGMT_AVAILABLE_SEARCHES (TARGET_TYPE, CLASSNAME, SRCH_TYPE) VALUES (targettype, classname, searchtype); END INSERT_AVAIL_SEARCHES; -- GET_HIST_FOR_GROUP -- Given group name and interval it returns the member targets of the group, -- and the category changes count. PROCEDURE GET_HIST_FOR_GROUP( targetName VARCHAR2, targetType VARCHAR2, historyForNDays NUMBER DEFAULT 7, p_member_targets_cursor OUT sys_refcursor, p_change_counts_cursor OUT sys_refcursor ) IS BEGIN open p_member_targets_cursor for SELECT DISTINCT mt.target_type AS TARGETTYPE FROM mgmt_flat_target_assoc a, mgmt_targets ct, mgmt_targets mt WHERE a.is_membership = 1 AND a.source_target_guid = ct.target_guid AND a.assoc_target_guid = mt.target_guid AND NLS_UPPER(ct.target_name) = NLS_UPPER(targetName) AND ct.target_type = targetType; open p_change_counts_cursor for SELECT category AS CATEGORY_VALUE, COUNT(DISTINCT DELTAGUID) AS COUNT FROM mgmt$ecm_config_history hist WHERE (DELTATIME >= TRUNC(SYSDATE - historyForNDays)) AND (EXISTS (SELECT /*+ INDEX (a) */* FROM mgmt_flat_target_assoc a, mgmt_targets ct, mgmt_targets mt WHERE a.is_membership = 1 AND a.source_target_guid = ct.target_guid AND a.assoc_target_guid = mt.target_guid AND NLS_UPPER(ct.target_name) = NLS_UPPER(targetName) AND ct.target_type = targetType AND mt.target_name = hist.target_name AND mt.target_type = hist.target_type )) GROUP BY target_type, snapshottype, category; END GET_HIST_FOR_GROUP; ---------------------------------------------------------------------------------- -- Refresh ECM metadata materialized views. This procedure should be called after -- any changes to ECM metadata underlying tables. PROCEDURE UPDATE_ECM_METADATA IS num_err NUMBER := 0; -- number of errors repos_owner VARCHAR2(30) := MGMT_USER.GET_REPOSITORY_OWNER; upgrade_status NUMBER := null; BEGIN BEGIN SELECT status INTO upgrade_status FROM mgmt_versions WHERE component_name = '_UPGRADE_' AND component_mode = 'SYSTEM'; -- if in middle of upgrade, don't update anything til the end IF upgrade_status = 2 THEN RETURN; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; -- not in middle of upgrade END; BEGIN DBMS_MVIEW.REFRESH_DEPENDENT(num_err, repos_owner || '.MGMT_ECM_SNAPSHOT_METADATA,' || repos_owner || '.MGMT_ECM_SNAPSHOT_MD_TABLES,' || repos_owner || '.MGMT_ECM_SNAPSHOT_MD_COLUMNS'); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; -- no views to refresh END; IF num_err > 0 THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.ECM_METADATA_UPDATE_ERR, 'Failed to update ECM metadata. It now could be in an inconsistent ' || 'state. See alert log for more details.'); END IF; -- This call can be replaced with TRUNCATE TABLE MGMT_ECM_MD_HIST_TBLS -- if number of rows in the table becomes large. Slight advantage of -- DELETE FROM is that it can be done in the same transaction as the -- next statement. Truncate is DDL and thus commits right away; however, -- it is more performant, esp. when there are a lot of rows. DELETE FROM MGMT_ECM_MD_HIST_TBLS; -- Due to constaints on MGMT_ECM_MD_HIST_TBLS, order by clause is needed -- at the end of this statement. INSERT INTO MGMT_ECM_MD_HIST_TBLS (metadata_id, target_type, snapshot_type, name, num_hist_ui_keys, hist_ui_key1, hist_ui_key2, hist_ui_key3, hist_ui_key4, hist_ui_key5, hist_ui_key6, ui_name, ui_on, compare_on, compare_ui_on, history_on, history_ui_on, parent_table_name, full_table_path, is_single_row, tbl_order) SELECT t.METADATA_ID, m.TARGET_TYPE, m.SNAPSHOT_TYPE, t.NAME, NVL(t_sum.num_hist_ui_keys, 0) AS NUM_HIST_UI_KEYS, t_sum.HIST_UI_KEY1, t_sum.HIST_UI_KEY2, t_sum.HIST_UI_KEY3, t_sum.HIST_UI_KEY4, t_sum.HIST_UI_KEY5, t_sum.HIST_UI_KEY6, t.ui_name, t.ui_on, t.compare_on, t.compare_ui_on, t.history_on, t.history_ui_on, t.parent_table_name, t.full_table_path, t.is_single_row, t.tbl_order FROM mgmt_ecm_snapshot_metadata m, mgmt_ecm_snapshot_md_tables t, (SELECT metadata_id, table_name AS name, count(*) AS num_hist_ui_keys, max(decode(key_rank, 1, key_name)) AS hist_ui_key1, max(decode(key_rank, 2, key_name)) AS hist_ui_key2, max(decode(key_rank, 3, key_name)) AS hist_ui_key3, max(decode(key_rank, 4, key_name)) AS hist_ui_key4, max(decode(key_rank, 5, key_name)) AS hist_ui_key5, max(decode(key_rank, 6, key_name)) AS hist_ui_key6 FROM (SELECT metadata_id, table_name, name AS key_name, rank() over (partition BY metadata_id, table_name ORDER BY col_order) AS key_rank FROM mgmt_ecm_md_all_tbl_columns WHERE is_key = 'Y' AND history_on = 'Y' AND history_ui_on = 'Y') GROUP BY metadata_id, table_name) t_sum WHERE m.metadata_id = t.metadata_id AND m.kind = 'P' AND m.history_on = 'Y' AND m.history_ui_on = 'Y' AND t.history_on = 'Y' AND t.history_ui_on = 'Y' AND t.metadata_id = t_sum.metadata_id (+) AND t.name = t_sum.name (+) ORDER BY t.metadata_id, t.tbl_order ASC; COMMIT; END UPDATE_ECM_METADATA; END ECM_UTIL; / show errors;