Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/ecm/ecm_util_pkgdef.sql /st_emcore_10.2.0.4.3db11.2.0.2/1 2010/05/01 04:30:45 niramach Exp $ Rem Rem ecm_util_pkgdef.sql Rem Rem Copyright (c) 2002, 2010, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem ecm_util_pkgdef.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem niramach 04/28/10 - XbranchMerge niramach_bug-8837368 from Rem st_emcore_10.2.0.1.0 Rem rrawat 09/11/07 - Bug-6379261 Rem akskumar 12/01/05 - Backport akskumar_bug-4623295 from main Rem akskumar 11/22/05 - bug-4623295 Rem anusharm 08/16/05 - add search for exact values in Rem get_clone_from_source Rem anusharm 05/25/05 - moving out comp names to xml in GET_CLONE Rem mgoodric 10/25/04 - move ECM_UTIL functions to MGMT_VIEW_UTIL Rem vkhizder 10/04/04 - adding refresh for ecm materialized views Rem mgoodric 09/26/04 - fix CONFIG_HISTORY views returning key columns Rem niramach 09/22/04 - Removed INSERT_HIST_GROUPS procedure definition. Rem niramach 09/22/04 - Modified the definition of procedure Rem GET_HIST_FOR_GROUP. Rem mgoodric 09/15/04 - fix using MGMT_MESSAGE.GET_MESSAGE Rem mgoodric 09/13/04 - integrate with new history_ui_on flag 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 vkhizder 10/31/03 - adding functions for getting Rem patchsets/patches/bugs for a home 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 shuberma 04/16/03 - Removing procedure that is not longer used Rem shuberma 02/12/03 - Document new column in returned 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 call Rem rmenon 10/18/02 - added CONCAT_COMPONENT_VERSION definition Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Created Rem rem******************************************************************** rem rem PURPOSE rem rem The ECM_UTIL package contains procedures and functions for various rem purposes including returning a list of target names for jobs, or admins rem for rules. rem rem PROCEDURES and FUNCTIONS rem rem JOB_TARGET_LIST rem ARGUMENTS: All are IN parameters unless otherwise noted. rem job_id -- The internal job id for which to return the target list. rem RETURNS: a VARCHAR2 which is the space separated target list. rem rem HOST_HOME_TARGET_LIST rem ARGUMENTS: All are IN parameters unless otherwise noted. rem host_name -- The name of the host for which to compare for ORACLE_HOME. rem oracle_home -- The path of the ORACLE_HOME for which to compare the host. rem target_type -- The type of target for which to compare for ORACLE_HOME. rem RETURNS: a VARCHAR2 which is the comma separated target list. rem rem NOTES rem rem The methods in this package do not make any assumptions about rem transacations. Essentially, its up to the call to commit or rollback, rem unless otherwise noted. rem -- token list for splitting internal comp names in GET_CLONE_SOURCE CREATE OR REPLACE TYPE TOKEN_LIST IS TABLE OF VARCHAR2(256) / CREATE OR REPLACE PACKAGE ECM_UTIL IS -- The following constants are used to communicate the search criteria for -- get_clone_source. -- 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 APP_SERVER_NAME CONSTANT VARCHAR2(10) := 'oracle_ias'; DB_NAME CONSTANT VARCHAR2(15) := 'oracle_database'; HOST_NAME CONSTANT VARCHAR2(9) := 'HOST_NAME'; HOME_LOCATION CONSTANT VARCHAR2(13) := 'HOME_LOCATION'; OCS_NAME CONSTANT VARCHAR2(10) := 'oracle_ocs'; HOME_NAME CONSTANT VARCHAR2(9) := 'HOME_NAME'; PLATFORM CONSTANT VARCHAR2(8) := 'PLATFORM'; PRODUCT_NAME CONSTANT VARCHAR2(12) := 'PRODUCT_NAME'; FUNCTION_NOT_IMPLEMENTED_ERR CONSTANT INTEGER := -20002; function_not_implemented EXCEPTION; PRAGMA EXCEPTION_INIT(function_not_implemented, -20002); TYPE CLONE_SOURCE_REF IS REF CURSOR; TYPE RESULT_CURSOR IS REF CURSOR; --DEPRECATED: DONT USE THIS FUNCTION.THIS CAUSES SQL INJECTION ISSUE (Bug 8837368) -- 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; FUNCTION CONCAT_RESULT_COL ( c1 RESULT_CURSOR, DELIMETER VARCHAR2 := ' ', COL_LIMIT NUMBER := -1) RETURN VARCHAR2; FUNCTION HOST_HOME_LIST ( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2 ) RETURN VARCHAR2; FUNCTION PATCH_ADVISORY_LIST ( p_patch_guid IN RAW) RETURN VARCHAR2; FUNCTION FIXED_BUGS_IN_PATCH ( p_patch_guid IN RAW) RETURN VARCHAR2; FUNCTION CSA_CLIENT_RULE_LIST ( p_snapshot_id IN RAW) RETURN VARCHAR2; 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; FUNCTION CSA_TARGET_LIST ( p_application_id IN VARCHAR2) RETURN VARCHAR2; FUNCTION HOST_HOME_SID_LIST( v_host_name IN VARCHAR2, v_oracle_home IN VARCHAR2 ) RETURN VARCHAR2; FUNCTION HOST_HOME_LSNR_LIST( v_host_name IN VARCHAR2, v_oracle_home IN VARCHAR2 ) RETURN VARCHAR2; FUNCTION HOST_HOME_TARGET_LIST( v_host_name IN VARCHAR2, v_oracle_home IN VARCHAR2, v_target_type IN VARCHAR2 ) RETURN VARCHAR2; -- 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; -- This functions concatenates oracle component version - -- used in the oracle components comparison query FUNCTION CONCAT_COMPONENT_VERSION( p_component_name IN VARCHAR2, p_container_guid IN RAW ) RETURN VARCHAR2; -- 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_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. -- 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; -- 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; -- 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; -- 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; -- Register a search. PROCEDURE INSERT_AVAIL_SEARCHES( targettype VARCHAR2, classname VARCHAR2, searchtype VARCHAR2); -- Procedure to get history for a group for n number of days. -- Will return member targets of the group and history counts. -- PROCEDURE GET_HIST_FOR_GROUP( targetName VARCHAR2, targetType VARCHAR2, historyForNDays NUMBER := 7, p_member_targets_cursor OUT sys_refcursor, p_change_counts_cursor OUT sys_refcursor ); -- Refresh ECM metadata materialized views. This procedure should be called after -- any changes to ECM metadata underlying tables. PROCEDURE UPDATE_ECM_METADATA; END ECM_UTIL; / show errors;