Rem Rem $Header: ecm_host_config_compare_pkgdef.sql 12-jul-2004.23:53:39 akskumar Exp $ Rem Rem ecm_host_config_compare_pkgdef.sql Rem Rem Copyright (c) 2003, 2004, Oracle. All rights reserved. Rem Rem NAME Rem ecm_host_config_compare_pkgdef.sql Rem Rem DESCRIPTION Rem ecm_host_config_compare_pkgdef.sql - contains execution of Rem functions/procedures used in host config comparison Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem akskumar 07/06/04 - add function compare_hc_save_job Rem rpinnama 06/12/04 - Remove set ECHO Rem awarkar 11/15/03 - Bug Fix For Issue#3255739 Rem awarkar 07/31/03 - Fix For File Purging Policy Issue Rem awarkar 07/01/03 - Bug Fix For Issue#3012317 Rem rmenon 06/16/03 - fixed 3007819 Rem rmenon 06/14/03 - one to n checkinbug fixes plus clean up Rem rmenon 06/10/03 - rmenon_1_to_n_and_1_to_1_checkin Rem rmenon 06/10/03 - Created rem The following package defines functions/procedures used in comparison of rem host configuration snapshots. create or replace package host_config_comparison is -- subtypes begin subtype comma_sep_table_name_list is varchar2(4000); /*---------------------------------------------------------------------- The function compare_hc_and_save_job is a wrapper around steve's compare_hc_and_save. This takes two snapshot guids and invokes the comp_hc_and_save. Before doing that however, it takes a job-specific action which inserts a record in the table mgmt_job_step_targets. This is to store the relation between step and target that I can use later during retrieving information in the gui. and is used to compare and save results of two host config. snapshots. Input parameters ---------------- 1. p_lhs_guid - snapshot guid of first (or LHS) host config 2. p_rhs_guid - snapshot guid of second (or RHS) host config 3. p_job_step_id - the job's step id (passed from the job). 4. p_session_id - session id of the session in which this comparison is done. Output parameters ---------------- Returns ------ the comparison result guid -----------------------------------------------------------------------*/ function compare_hc_and_save_job ( p_lhs_guid in raw, p_rhs_guid in raw, p_job_step_id in number, p_session_id in raw default SYS_GUID() ) return raw; /*---------------------------------------------------------------------- The function compare_hc_and_save is a wrapper around steve's API and is used to compare and save results of two host config. snapshots. It does the following 1. Saves the comparison properties using the two guids. These are properties that are needed in GUI after comparison but are not saved as part of the comparison result itself. 2. sets appropriate flags for tables that dont follow the default flag of "RETURN_DIFF". For example, it sets the flag of "RETURN_ALL" for the os summary view because we need the results in UI even if the comparison result is "SAME". 3. calls steve's comparison API to perform the actual comparison. Input parameters ---------------- 1. p_lhs_guid - snapshot guid of first (or LHS) host config 2. p_rhs_guid - snapshot guid of second (or RHS) host config 3. p_session_id - session id of the session in which this comparison is done. Output parameters ---------------- Returns ------ the comparison result guid -----------------------------------------------------------------------*/ function compare_hc_and_save ( p_lhs_guid in raw, p_rhs_guid in raw, p_session_id in raw default SYS_GUID() ) return raw; /*---------------------------------------------------------------------- The function compare_two_hosts_job is a wrapper around the comp_hc_based_on_name_and_save. This takes two targets that are hosts as input; gets their snapshot guids and invokes the comp_hc_based_on_name_and_save. Before doing that however, it takes a job-specific action which inserts a record in the table mgmt_job_step_targets. This is to store the relation between step and target that I can use later during retrieving information in the gui. Input parameters ---------------- 1. p_lhs_host_or_file_name - target name of first (or LHS) host config this would be the file name for file to multiple hosts comparison 2. p_rhs_host_target_name - target name of second (or RHS) host config 3. p_job_step_id - the job's step id (passed from the job) 4. p_file_contents_guid - if it is a file to n hosts comparison case this is the value of the file contents guid; otherwise it is a null or empty string. Output parameters ---------------- Returns ------ the comparison result guid -----------------------------------------------------------------------*/ function compare_two_hosts_job( p_lhs_host_or_file_name in varchar2, p_rhs_host_target_name in varchar2, p_job_step_id in number, p_file_contents_guid in raw default null ) return raw; /*---------------------------------------------------------------------- The function get_curr_host_or_file_guid gets the current snapshot guid given a host target name. Input parameters ---------------- 1. p_host_or_file_name - target name of host config or file 2. p_file_contents_guid - file contents guid if it is a file to n hosts comparison case. Returns ------ the current snapshot guid of the host target -----------------------------------------------------------------------*/ function get_curr_host_or_file_guid( p_host_or_file_name in varchar2, p_file_contents_guid in raw default null ) return raw; /*---------------------------------------------------------------------- The procedure get_comparison_summary_result returns the result of comparison summary at a component level of hardware, os etc. The level at which the summary is calculated is passed as an integer parameter ( p_which_component) . Currently it works for hardware or os depending on the value of p_which_component parameter. It can be easily extended to deal with other cases, if required. Input parameters ---------------- 1. p_comparison_guid - the comparison result guid from Steve's API 2. p_which_component - should be equal to HW_SUMMARY_CASE if you want to get hardware's summary result, otherwise should be OS_SUMMARY_CASE constant. If you define a third one, the constant should be defined in this file and a matching constant should be defined in the calling program. Output parameters ---------------- p_is_summary_different - if there are no errors, then this parameter gets an integer value of SUMMARY_RESULT_IS_SAME, if summary result is SAME, otherwise a non-zero integer implying that summary result is different. if there is an error in the comparison result (stored during the comparison), this parameter gets a value of ERROR_IN_COMPARISON. p_errors_cursor - if there are no errors in comparison result, then this contains null. if there is an error (implying that p_is_summary_different parameter is set to ERROR_IN_COMPARISON) then -----------------------------------------------------------------------*/ procedure get_comparison_summary_result( p_comparison_guid in raw, p_which_component in integer, p_is_summary_different out integer, p_errors_cursor out sys_refcursor ); /*---------------------------------------------------------------------- The procedure get_multi_hosts_comp_summary returns the summary information of comparing one host with multiple hosts via a submitted job. It takes the job's ID as the required parameter. Input parameters ---------------- 1. p_execution_id - execution ID of the submitted job that compares one host to multiple hosts. Output parameters ----------------- 1. p_cursor - ref cursor result of multi-hosts comparison result. -----------------------------------------------------------------------*/ procedure get_multi_hosts_comp_summary ( p_execution_id raw, p_cursor out sys_refcursor); /*---------------------------------------------------------------------- The procedure get_comparison_properties returns the stored comparison properties of a given host config comparison. After the comparison, some of the results are not stored as part of the comparison result guid associated information. Since the two guids are not accessible later (e.g. in case comparison is a job), we store as part of comparison some comparison properties needed in later UI. The procedure returns a cursor to the comparison properties table contents. (also see populate_comparison_properties comments for more details. Input parameters ---------------- 1. p_comparison_guid - snapshot guid of comparison result Output parameters ----------------- 1. p_cursor - ref cursor result of comparison properties -----------------------------------------------------------------------*/ procedure get_comparison_properties ( p_comparison_guid raw, p_cursor out sys_refcursor); /*---------------------------------------------------------------------- The procedure get_comparison_property returns the stored comparison property of a given host configuration comparison for a given property name see get_comparison_properties comments for more details. Input parameters ---------------- 1. p_comparison_guid - snapshot guid of comparison result 2. p_property_name - name of the property that you want to retrieve Output parameters ----------------- 1. p_cursor - ref cursor result of comparison properties associated with the property name passed -----------------------------------------------------------------------*/ procedure get_comparison_property ( p_comparison_guid raw, p_property_name varchar2, p_cursor out sys_refcursor); /*---------------------------------------------------------------------- The procedure get_oracle_comp_summary returns the results of Oracle top level comparison summary. Input parameters ---------------- 1. p_comparison_guid - snapshot guid of comparison result Output parameters ----------------- 1. p_cursor - ref cursor result Implementation Notes -------------------- The "max(decode)" construct is used to generate a transpose view of a query result where we know the values of certain columns to be a known set. For example, in the query below the column "mgmt_delta_comp_key_cols.name" is known to take only two values - (the ones in the decode). To understand how the concept of "transpose" works, run the internal query, then run the whole query without the "max" applied, and finally run the entire query. The intermediate results should clarify the working. -----------------------------------------------------------------------*/ procedure get_oracle_comp_summary ( p_comparison_guid raw, p_cursor out sys_refcursor); /*---------------------------------------------------------------------- The procedure get_os_reg_sw_comp_summary returns the results of os-registered software comparison summary. Input parameters -------------------- 1. p_comparison_guid - snapshot guid of comparison result Output parameters ----------------- 1. p_cursor - ref cursor result -----------------------------------------------------------------------*/ procedure get_os_reg_sw_comp_summary ( p_comparison_guid raw, p_cursor out sys_refcursor); /*---------------------------------------------------------------------- The procedure get_hw_master_comparison_dtls returns the results of hardware master comparison details. Input parameters -------------------- 1. p_comparison_guid - snapshot guid of comparison result Output parameters ----------------- 1. p_cursor - ref cursor result -----------------------------------------------------------------------*/ procedure get_hw_master_comparison_dtls ( p_comparison_guid raw, p_cursor out sys_refcursor); /*---------------------------------------------------------------------- The procedure get_cpu_comparison_dtls returns the results of cpu comparison details. Input parameters -------------------- 1. p_comparison_guid - snapshot guid of comparison result Output parameters ----------------- 1. p_cursor - ref cursor result Implementation Notes -------------------- Please see the explanation in the "Implementation Notes" section of get_oracle_comp_summary. -----------------------------------------------------------------------*/ procedure get_cpu_comparison_dtls ( p_comparison_guid raw, p_cursor out sys_refcursor); /*---------------------------------------------------------------------- The procedure get_iocard_comparison_dtls returns the results of iocard comparison details. Input parameters -------------------- 1. p_comparison_guid - snapshot guid of comparison result Output parameters ----------------- 1. p_cursor - ref cursor result -----------------------------------------------------------------------*/ procedure get_iocard_comparison_dtls ( p_comparison_guid raw, p_cursor out sys_refcursor); /*---------------------------------------------------------------------- The procedure get_nic_comparison_dtls returns the results of NIC comparison details. Input parameters -------------------- 1. p_comparison_guid - snapshot guid of comparison result Output parameters ----------------- 1. p_cursor - ref cursor result Implementation Notes -------------------- Please see the explanation in the "Implementation Notes" section of get_oracle_comp_summary. -----------------------------------------------------------------------*/ procedure get_nic_comparison_dtls ( p_comparison_guid raw, p_cursor out sys_refcursor); /*---------------------------------------------------------------------- The procedure get_os_prop_comparison_dtls returns the details of OS properties comparison. Input parameters ------------------ 1. p_comparison_guid - snapshot guid of comparison result Output parameters --------------- 1. p_cursor - ref cursor result ---------------------------------------------------------------------*/ procedure get_os_prop_comparison_dtls ( p_comparison_guid raw, p_cursor out sys_refcursor); /*---------------------------------------------------------------------- The procedure get_os_comparison_summary returns the comparison summary of os comparison. Input parameters ---------------- 1. p_comparison_guid - snapshot guid of comparison result Output parameters ----------------- 1. p_cursor - ref cursor result -----------------------------------------------------------------------*/ procedure get_os_comparison_summary( p_comparison_guid raw, p_cursor out sys_refcursor); /*---------------------------------------------------------------------- The procedure get_fs_comparison_dtls returns the comparison summary of os file systems comparison. Input parameters ---------------- 1. p_comparison_guid - snapshot guid of comparison result Output parameters ----------------- 1. p_cursor - ref cursor result -----------------------------------------------------------------------*/ procedure get_fs_comparison_dtls( p_comparison_guid raw, p_cursor out sys_refcursor); /*---------------------------------------------------------------------- The procedure get_os_comp_comparison_dtls returns the comparison details of os components comparison (other than patches). Input parameters ---------------- 1. p_comparison_guid - snapshot guid of comparison result 2. p_comparison_guid - snapshot guid of comparison result Output parameters ----------------- 1. p_cursor - ref cursor result Implementation Notes -------------------- Please see the explanation in the "Implementation Notes" section of get_oracle_comp_summary. -----------------------------------------------------------------------*/ procedure get_os_comp_comparison_dtls( p_comparison_guid raw, p_component_type varchar2, p_cursor out sys_refcursor); /*---------------------------------------------------------------------- The procedure get_os_patch_comparison_dtls returns the comparison summary of os patches Input parameters ---------------- 1. p_comparison_guid - snapshot guid of comparison result Output parameters ----------------- 1. p_cursor - ref cursor result Implementation Notes -------------------- Please see the explanation in the "Implementation Notes" section of get_oracle_comp_summary. -----------------------------------------------------------------------*/ procedure get_os_patch_comparison_dtls( p_comparison_guid raw, p_cursor out sys_refcursor); /*---------------------------------------------------------------------- The procedure get_os_general_info returns the results of os comparison details for os general info (where only ecm$os_summary view entries are compared.) Input parameters ---------------- 1. p_comparison_guid - snapshot guid of comparison result Output parameters ----------------- 1. p_cursor - ref cursor result Implementation Notes -------------------- Please see the explanation in the "Implementation Notes" section of get_oracle_comp_summary. -----------------------------------------------------------------------*/ procedure get_os_general_info ( p_comparison_guid raw, p_cursor out sys_refcursor); /*---------------------------------------------------------------------- The procedure convert_strings_to_raw converts at the most two string values to their raw numbers. The restriction of two string values is there because the requirement first came out for converting two string values at a time (in comparison code) if you want to just convert one string then pass second string parameter as null. Input parameters ---------------- 1. p_first_string - first string to be converted into raw 2. p_second_string - second string to be converted into raw Output parameters ----------------- 1. p_first_raw_result - result of converting first string to raw 2. p_second_raw_result - result of converting second string to raw -----------------------------------------------------------------------*/ procedure convert_strings_to_raw ( p_first_string in varchar2, p_second_string in varchar2, p_first_raw_result out raw, p_second_raw_result out raw); /*---------------------------------------------------------------------- The procedure get_comparison_errors returns the errors that occurred during the comparison and have been stored in the repository with comparison results. Input parameters ---------------- 1. p_comparison_guid - the comparison result guid from Steve's API 2. p_which_component - should be equal to a. HW_SUMMARY_CASE if you want to get hardware summary errors, b. OS_SUMMARY_CASE if you want to get os summary errors, c. ALL_ERRORS_CASE if you want to get all errors. Output parameters ---------------- p_errors_cursor - cursor that returns varchar2 rows - the error string can be computed by concatenating all the rows into a final string at the calling code. -----------------------------------------------------------------------*/ procedure get_comparison_errors ( p_comparison_guid in raw, p_which_component in integer, p_errors_cursor out sys_refcursor); end host_config_comparison; / show errors;