Rem Rem $Header: ecm_host_config_compare_pkgbody.sql 18-oct-2006.10:25:25 agor Exp $ Rem Rem ecm_host_config_compare_pkgbody.sql Rem Rem Copyright (c) 2003, 2006, Oracle. All rights reserved. Rem Rem NAME Rem ecm_host_config_compare_pkgbody.sql Rem Rem DESCRIPTION Rem ecm_host_config_compare_pkgbody.sql - contains execution of Rem functions/procedures used in host config comparison Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem agor 10/18/06 - fix sort too long issue Rem akskumar 08/30/05 - bug-4503341 Rem akskumar 07/06/05 - bug-4186461 Rem agor 06/09/05 - also show swap space in compare results Rem akskumar 03/29/05 - replace mgmt_ecm_loaded_files with Rem mgmt_ecm_savedhostconfig 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 09/10/03 - Bug Fix For Issue#3130695 Rem awarkar 07/31/03 - Fix For File Purging Policy Issue Rem awarkar 07/02/03 - Bug Fix For Issue#3012317 Rem rmenon 06/25/03 - fixed 3007819 - the previous bug fix Rem had introduced another bug due to which Rem duplicate entries were being shown in the Rem multi comparison summary page ui in case Rem of step failures. Rem rmenon 06/16/03 - fixed 3007819 Rem rmenon 06/14/03 - code cleanup Rem shuberma 06/12/03 - Fixing create problem 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 procedures/functions used in rem comparing and retrieving results of comparing two host rem configuration snapshots. rem - IMPLEMENTAION NOTE: I need to convert all constant strings rem - to constant variables - I was under the impression that rem - you cant use constants in sql where clause (because rem - if you do it from sql, you get an error!) create or replace package body host_config_comparison IS -- constants begin TARGET_NAME_PROP constant varchar2(20) := 'TargetName'; TARGET_TYPE_PROP constant varchar2(20) := 'TargetType'; HOST_NAME_PROP constant varchar2(20) := 'HostName'; FILE_NAME_PROP constant varchar2(20) := 'FileName'; TARGET_TIME_STAMP_PROP constant varchar2(20) := 'TargetTimestamp'; TARGET_TIMEZONE_PROP constant varchar2(20) := 'TargetTimezone'; OS_SUMMARY_PROP constant varchar2(20) := 'OsSummary'; OS_COMP_TYPE_PROP constant varchar2(20) := 'OsComponentType'; HW_SUMMARY_PROP constant varchar2(20) := 'HwSummary'; SUMMARY_RESULT_IS_SAME constant integer := 0; ERROR_IN_COMPARISON constant integer := -1; HW_SUMMARY_CASE constant integer := 0; OS_SUMMARY_CASE constant integer := 1; ALL_ERRORS_CASE constant integer := 2; OS_PROPERTIES_VIEW_NAME constant varchar2(50) := 'ECM$OS_PROPERTIES'; CPU_DETAILS_VIEW_NAME constant varchar2(50) := 'ECM$CPU_DETAILS'; IOCARD_DETAILS_VIEW_NAME constant varchar2(50) := 'ECM$IOCARD_DETAILS'; NIC_DETAILS_VIEW_NAME constant varchar2(50) := 'ECM$NIC_DETAILS'; FS_MOUNT_DETAILS_VIEW_NAME constant varchar2(50) := 'ECM$FS_MOUNT_DETAILS'; OS_COMPONENTS_VIEW_NAME constant varchar2(50) := 'ECM$OS_COMPONENTS'; OS_PATCHES_VIEW_NAME constant varchar2(50) := 'ECM$OS_PATCHES'; OS_SUMMARY_VIEW_NAME constant varchar2(50) := 'ECM$OS_SUMMARY'; HARDWARE_MASTER_VIEW_NAME constant varchar2(50) := 'ECM$HARDWARE_MASTER'; ORACLE_TOPLEVEL_VIEW_NAME constant varchar2(50) := 'ECM$ORACLE_TOPLEVEL'; OS_REGISTERED_SW_VIEW_NAME constant varchar2(50) := 'ECM$OS_REGISTERED_SW'; HOST_CONFIG_SNAPSHOT_TYPE constant varchar2(50) := 'host_configuration'; HOST_CONFIG_COMPARISON_TYPE constant varchar2(50) := 'HOST_CONFIGURATION'; HOST_CONFIG_TARGET_TYPE constant varchar2(50) := 'host'; HOST_CONFIG_HW_VIEW_LIST constant varchar2(200) := 'ECM$CPU_DETAILS,ECM$HARDWARE_MASTER,ECM$NIC_DETAILS,ECM$IOCARD_DETAILS'; HOST_CONFIG_OS_VIEW_LIST constant varchar2(250) := 'ECM$OS_SUMMARY,ECM$OS_COMPONENTS,ECM$OS_PATCHES,ECM$OS_PROPERTIES,ECM$FS_MOUNT_DETAILS'; -- constants end /***-------- PACKAGE PRIVATE PROCEDURES AND FUNCTIONS ------------*/ /*---------------------------------------------------------------------- The function comp_hc_based_on_name_and_save is a wrapper around the compare_hc_and_save. This takes two targets that are hosts as input; gets their snapshot guids and invokes the compare_hc_and_save 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_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 comp_hc_based_on_name_and_save( p_lhs_host_or_file_name in varchar2, p_rhs_host_target_name in varchar2, p_file_contents_guid in raw default null ) return raw is l_lhs_host_or_file_guid raw(16); l_rhs_host_guid raw(16); begin l_lhs_host_or_file_guid := get_curr_host_or_file_guid( p_lhs_host_or_file_name, p_file_contents_guid ); -- the rhs host can not be file as yet -this is because -- this function is not used to compare file to -- file - so p_rhs_host_target_name is always a host. l_rhs_host_guid := get_curr_host_or_file_guid( p_rhs_host_target_name ); return compare_hc_and_save (l_lhs_host_or_file_guid, l_rhs_host_guid ); end; /*---------------------------------------------------------------------- The function insert_job_step_targets inserts the mapping info that maps a job step to the rhs target guid in a host comparison job step. The information in Input parameters ---------------- 1. p_rhs_host_target_name - target name of host against which the current step is performing comparison 2. p_job_step_id - current job step id Output parameters ---------------- -----------------------------------------------------------------------*/ procedure insert_job_step_targets( p_rhs_host_target_name in varchar2, p_job_step_id in number ) is l_target_guid mgmt_targets.target_guid%type; begin select t.target_guid into l_target_guid from mgmt_ecm_snapshot s, mgmt_targets t where s.target_name = p_rhs_host_target_name and s.target_type = HOST_CONFIG_TARGET_TYPE and s.snapshot_type = HOST_CONFIG_SNAPSHOT_TYPE and s.is_current = 'Y' and s.target_name = t.target_name and s.target_type = t.target_type; insert into mgmt_job_step_targets ( step_id, target_guid ) values( p_job_step_id, l_target_guid ); end; /*---------------------------------------------------------------------- The function set_comp_result_indicator sets the "component"'s result indicator. Here a component represents a logical entity ( hw or os). This method finds out if there is any comparison error in the tables associated with the component - If so, it sets the p_is_summary_different to ERROR_IN_COMPARISON. If not and if the result is SAME, it sets the p_is_summary_different to 0, otherwise to a nonzero integer. Input parameters ---------------- 1. p_comparison_guid - comparison guid 2. p_which_component - indicates which component (hw or os) Output parameters ----------------- 1. p_is_summary_different - an integer whose value is set to: a. ERROR_IN_COMPARISON - in case of comparison error b. 0, if result is same c. a nonzero integer, if result is different. Returns ------- the comparison properties that get saved of type MGMT_DELTA.COMPARISON_PROPERTIES. It contains data such as: PropertyName LeftValue RightValue ------------ --------- ---------- TargetName lothar a-sm-regression1 -----------------------------------------------------------------------*/ procedure set_comp_result_indicator( p_comparison_guid in raw, p_which_component in integer, p_is_summary_different out integer ) is begin /* If the error count is 0, then p_is_summary_different = whatever the summary result should be, i.e. 0 if it is same and nonzero if it is different. If the error count > 0, then we set it to ERROR_IN_COMPARISON */ select decode( a.error_count, 0, b.summary_result_indicator_count, ERROR_IN_COMPARISON ) into p_is_summary_different from ( /* get the error count - the following somewhat unusual count(*) (i.e. using a count from dual plus an exists clause) is faster than the simple count(*) because here count(*) is just an indicator - nonzero as "error", 0 as "no error". */ select count(*) error_count from dual where exists ( select e.column_value from mgmt_delta_comp_summaries s, table(s.errors) e where s.delta_comp_guid = p_comparison_guid and instr( decode( p_which_component, HW_SUMMARY_CASE, HOST_CONFIG_HW_VIEW_LIST, OS_SUMMARY_CASE, HOST_CONFIG_OS_VIEW_LIST, s.collection_type ), s.collection_type) != 0 ) ) a, ( /* get the summary result indicator - 0 if "SAME" and nonzero if "DIFFERENT". If the count(*) seems overly complex to you, see above for the rationale. */ select count(*) summary_result_indicator_count from dual where exists ( select collection_type from mgmt_delta_comp_summaries where delta_comp_guid = p_comparison_guid and instr( decode( p_which_component, HW_SUMMARY_CASE, HOST_CONFIG_HW_VIEW_LIST, OS_SUMMARY_CASE, HOST_CONFIG_OS_VIEW_LIST, collection_type ), collection_type) != 0 and (different_count+left_count+right_count > 0) ) ) b; end; /*---------------------------------------------------------------------- The function populate_comparison_properties populates the comparion properties data structure that needs to be saved as part of the comparison of host config snapshots. These are values such as target name, target time stamp etc that need to be displayed in UI but are not part of the saved comparison result. We should use this function to store any values that we can not retrieve from comparison result but nevertheless need in subsequent UI pages. Please note that this function is NOT EXPOSED via the package definition as part of the interface defined by this package. Input parameters ---------------- 1. p_lhs_guid - snapshot guid of first (or LHS) host configuration 2. p_rhs_guid - snapshot guid of second (or RHS)host configuration Output parameters ----------------- Returns ------- the comparison properties that get saved of type MGMT_DELTA.COMPARISON_PROPERTIES. It contains data such as: PropertyName LeftValue RightValue ------------ --------- ---------- TargetName lothar a-sm-regression1 -----------------------------------------------------------------------*/ function populate_comparison_properties ( p_lhs_guid in raw, p_rhs_guid in raw ) return mgmt_delta.comparison_properties is cursor l_c( lhs_guid raw, rhs_guid raw) is select TARGET_NAME_PROP property_key, lhs.target_name lhs, rhs.target_name rhs from mgmt_ecm_snapshot lhs, mgmt_ecm_snapshot rhs where lhs.snapshot_guid = lhs_guid and rhs.snapshot_guid = rhs_guid union select TARGET_TYPE_PROP property_key, lhs.target_type lhs, rhs.target_type rhs from mgmt_ecm_snapshot lhs, mgmt_ecm_snapshot rhs where lhs.snapshot_guid = lhs_guid and rhs.snapshot_guid = rhs_guid union select HOST_NAME_PROP property_key, lhs.target_name lhs, rhs.target_name rhs from mgmt_ecm_snapshot lhs, mgmt_ecm_snapshot rhs where lhs.snapshot_guid = lhs_guid and rhs.snapshot_guid = rhs_guid union select FILE_NAME_PROP, max( lhs ) lhs, max( rhs ) rhs from ( select lhs_file.filename lhs, null rhs from mgmt_ecm_savedhostconfig lhs_file where lhs_file.hostconfig_contents_guid = lhs_guid union select null lhs, rhs_file.filename rhs from mgmt_ecm_savedhostconfig rhs_file where rhs_file.hostconfig_contents_guid = rhs_guid ) union select TARGET_TIME_STAMP_PROP, to_char(lhs.start_timestamp, 'YYYY-MM-DD HH24:MI:SS') lhs_time_stamp, to_char(rhs.start_timestamp, 'YYYY-MM-DD HH24:MI:SS') rhs_time_stamp from mgmt_ecm_snapshot lhs, mgmt_ecm_snapshot rhs where lhs.snapshot_guid = lhs_guid and rhs.snapshot_guid = rhs_guid union select TARGET_TIMEZONE_PROP, tl.timezone_region, tr.timezone_region from mgmt_ecm_snapshot lhs, mgmt_ecm_snapshot rhs, mgmt_targets tl, mgmt_targets tr where lhs.snapshot_guid = lhs_guid and rhs.snapshot_guid = rhs_guid and lhs.target_name = tl.target_name(+) and lhs.target_type = tl.target_type(+) and rhs.target_name = tr.target_name(+) and rhs.target_type = tr.target_type(+) union select OS_SUMMARY_PROP, lhs.name|| ' ' || lhs.base_version || decode(lhs.update_level, null,'',' '|| lhs.update_level) || decode ( lhs.address_length_in_bits, null, '', ' (' || lhs.address_length_in_bits || ')') lhs_os_summary, rhs.name|| ' ' || rhs.base_version || decode(rhs.update_level, null,'',' '|| rhs.update_level) || decode ( rhs.address_length_in_bits, null, '', ' (' || rhs.address_length_in_bits || ')') rhs_os_summary from mgmt_hc_os_summary lhs, mgmt_hc_os_summary rhs where lhs.snapshot_guid = lhs_guid and rhs.snapshot_guid = rhs_guid union select HW_SUMMARY_PROP, lhs.system_config || decode(lhs.cpu_count, null,'', ', '|| lhs.cpu_count || ' CPUs') || ','|| decode(lhs.memory_size_in_mb, null,'',' '|| lhs.memory_size_in_mb || ' MB Memory') lhs_hw_summary, rhs.system_config || decode(rhs.cpu_count, null,'', ', '|| rhs.cpu_count || ' CPUs') || ','|| decode(rhs.memory_size_in_mb, null,'',' '|| rhs.memory_size_in_mb || ' MB Memory') rhs_hw_summary from mgmt_hc_hardware_master lhs, mgmt_hc_hardware_master rhs where lhs.snapshot_guid = lhs_guid and rhs.snapshot_guid = rhs_guid union select distinct OS_COMP_TYPE_PROP, upper(type), type from mgmt_hc_os_components where snapshot_guid = lhs_guid or snapshot_guid = rhs_guid; l_cnt number default 0; l_rec l_c%rowtype; l_result_rec mgmt_delta.comparison_property ; l_result mgmt_delta.comparison_properties := mgmt_delta.comparison_properties(); begin open l_c(p_lhs_guid, p_rhs_guid); loop fetch l_c into l_rec; exit when l_c%notfound; l_cnt := l_cnt + 1; l_result_rec.NAME := l_rec.property_key ; l_result_rec.LEFT_VALUE := l_rec.lhs; l_result_rec.RIGHT_VALUE := l_rec.rhs; l_result.extend; l_result(l_cnt) := l_result_rec; end loop; close l_c; return l_result; end; /****---------- PUBLIC PROCEDURES AND FUNCTIONS ------------***/ /*---------------------------------------------------------------------- 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 is l_comparison_guid raw(16); l_comparison_properties mgmt_delta.comparison_properties; l_table_flags1 mgmt_ecm_snapshot_pkg.table_flags; l_table_flags2 mgmt_ecm_snapshot_pkg.table_flags; l_table_flags3 mgmt_ecm_snapshot_pkg.table_flags; l_table_flags4 mgmt_ecm_snapshot_pkg.table_flags; begin -- set return_all flags for cases in which we need -- to get the "SAME" rows as well. l_table_flags1.table_name := OS_SUMMARY_VIEW_NAME; l_table_flags1.flags := mgmt_delta.return_all; l_table_flags2.table_name := HARDWARE_MASTER_VIEW_NAME; l_table_flags2.flags := mgmt_delta.return_all; l_table_flags3.table_name := ORACLE_TOPLEVEL_VIEW_NAME; l_table_flags3.flags := mgmt_delta.return_all; l_table_flags4.table_name := OS_REGISTERED_SW_VIEW_NAME; l_table_flags4.flags := mgmt_delta.return_all; l_comparison_properties := populate_comparison_properties( p_lhs_guid, p_rhs_guid); l_comparison_guid := mgmt_ecm_snapshot_pkg.compare_and_save( HOST_CONFIG_COMPARISON_TYPE, HOST_CONFIG_TARGET_TYPE, p_lhs_guid, p_rhs_guid, p_session_id, l_comparison_properties, mgmt_ecm_snapshot_pkg.table_flags_list( l_table_flags1, l_table_flags2, l_table_flags3, l_table_flags4)); return l_comparison_guid; end; /*---------------------------------------------------------------------- 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 is l_targets MGMT_JOB_TARGET_LIST := MGMT_JOB_TARGET_LIST(); r_target_name MGMT_TARGETS.TARGET_NAME%TYPE; r_target_type MGMT_TARGETS.TARGET_TYPE%TYPE; begin select s.target_name,s.target_type into r_target_name,r_target_type from mgmt_ecm_snapshot s where s.snapshot_guid = p_rhs_guid; l_targets.extend(1); l_targets(1) := MGMT_JOB_TARGET_RECORD(r_target_name, r_target_type); MGMT_JOB_ENGINE.set_step_targets(p_job_step_id, l_targets); -- in case of Comparison invoked from 1-N Comparison Job, p_session_id is nothing but -- execution_id of the job, and it will be stored in mgmt_delta_saved_comparison -- under the Session_Id column. This information will be required at the time -- of Job deletion, all the comparisons saved against the given job execution Ids -- will be purged. insert into mgmt_comp_snapshot_to_step_map ( step_id, rhs_snapshot_guid,execution_id ) values( p_job_step_id, p_rhs_guid,p_session_id ); return compare_hc_and_save( p_lhs_guid, p_rhs_guid, p_session_id ); end; /*---------------------------------------------------------------------- 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. This information is used by the job team to find the targets that a step works on. Following is what Sriram Kini (skini) said in an email to explain why this insert needs to be carried out regardless of if the multi comparison summary ui (or any part of the comparison ui) uses it or not. "In most cases, the job system does not depend on the integrators' doing anything at all. The built-in commands - remoteOp, putFile, getFile, fileTransfer - will automatically populate the step targets table, since they know exactly what targets they are dealing with. The only exception is the repSQL command, and special commands written by integrators. When integrators write their own commands, it is the responsibility of the command to insert into the step targets table... With the repSQL command, it is not obvious to the command which targets it is operating on (or even whether it is operating on any targets). For the repSQL command, we either need a way for the integrator to tell the command which targets, if any, are involved, or we provide an API to insert targets for the step and have integrators insert them. I am not sure that we would be able to do the first for 4.1, that is why I suggest you do the second. " 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 is begin insert_job_step_targets( p_rhs_host_target_name, p_job_step_id ); return comp_hc_based_on_name_and_save( p_lhs_host_or_file_name, p_rhs_host_target_name , p_file_contents_guid ); end; /*---------------------------------------------------------------------- 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 is l_curr_host_or_file_guid raw(16); begin select a.host_or_file_guid into l_curr_host_or_file_guid from /* In the union of following two selects, only one will return any guid. If p_host_or_file_name is a host name then the first query should normally get a record and the second should not. If p_host_or_file_name is a file name, it would be the other way round. */ ( select s.snapshot_guid host_or_file_guid from mgmt_ecm_snapshot s, mgmt_targets t where s.target_name = p_host_or_file_name and s.target_type = HOST_CONFIG_TARGET_TYPE and s.snapshot_type = HOST_CONFIG_SNAPSHOT_TYPE and s.is_current = 'Y' and s.target_name = t.target_name and s.target_type = t.target_type union select f.hostconfig_contents_guid from mgmt_ecm_savedhostconfig f where f.hostconfig_contents_guid = p_file_contents_guid ) a where rownum <= 1; return l_curr_host_or_file_guid; end; /*---------------------------------------------------------------------- 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 Implementation Notes -------------------- None. -----------------------------------------------------------------------*/ 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 ) is l_list_of_table_names comma_sep_table_name_list; begin p_is_summary_different := SUMMARY_RESULT_IS_SAME; -- regardless of whether you have an error or not, -- we have to set the p_errors_cursor otherwise -- we get an error in the calling Java code. get_comparison_errors( p_comparison_guid, p_which_component, p_errors_cursor ); set_comp_result_indicator( p_comparison_guid, p_which_component, p_is_summary_different ); end; /*---------------------------------------------------------------------- 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. Implementation Notes -------------------- Documented inline within the query. -----------------------------------------------------------------------*/ procedure get_multi_hosts_comp_summary ( p_execution_id raw, p_cursor out sys_refcursor) is begin open p_cursor for select delta_comp_guid, lhs_file_guid, lhs_target_name, rhs_host_name, /* The following decode computes overall result (host level) if step_status is null then results are being computed (job has not been submitted - this should not be the case usually). Depending on the value of step_status show results for conditions corresponding to an incomplete job. if job is successfully completed, then if any of the hw/sw/oracle/os registered sw results contain an 'ERROR' string, then we shown an error at host comprison level - else show the result- phew! */ decode ( step_status, null, 'JOB_NOT_YET_SCHEDULED', 1, 'STATUS_SCHEDULED', 2, 'BEING_COMPUTED_RESULT', -- executing 3, 'STATUS_ABORTED', 4, 'STATUS_FAILED', 5, -- completed - find out the result decode ( instr ( hw_result||os_result || oracle_result || os_registered_sw_result, 'ERROR'), 0, decode ( instr(hw_result||os_result || oracle_result || os_registered_sw_result, 'COW_RESULT_DIFFERENT'), 0,'COW_RESULT_SAME', 'COW_RESULT_DIFFERENT' ), 'ERROR_IN_COMPARISON' ) ) result, /* The following decodes computes result for os/hw/oracle and os registered sw components respectively. If there is an error in the job system itself, it should show the job specific error message. If there is an error in the comparison code execution, it should show that separately. if step_status is null then job has not been scheduled yet. Depending on the value of step_status we show results for conditions corresponding to an incomplete job. Note esp. that status 2 means job is executing but we show our own display string that says "Being Computed" or the translated version of it. if job is successfully completed, then if any of the hw/sw/oracle/os registered sw results contain an 'ERROR' string, then we shown corresponding error string depicting the component in which comparison error was generated. Note that this error has nothing to do with job itself (because its status is 5 implying successfully completed) */ decode ( step_status, null, 'JOB_NOT_YET_SCHEDULED', 1, 'STATUS_SCHEDULED', 2, 'BEING_COMPUTED_RESULT', -- executing 3, 'STATUS_ABORTED', 4, 'STATUS_FAILED', 5, -- completed - find out the result decode( hw_result, 'ERROR', 'HW_COMPARISON_ERROR', hw_result ) ) hw_result, decode ( step_status, null, 'JOB_NOT_YET_SCHEDULED', 1, 'STATUS_SCHEDULED', 2, 'BEING_COMPUTED_RESULT', -- executing 3, 'STATUS_ABORTED', 4, 'STATUS_FAILED', 5, -- completed - find out the result decode( os_result, 'ERROR', 'OS_COMPARISON_ERROR', os_result ) ) os_result, decode ( step_status, null, 'JOB_NOT_YET_SCHEDULED', 1, 'STATUS_SCHEDULED', 2, 'BEING_COMPUTED_RESULT', -- executing 3, 'STATUS_ABORTED', 4, 'STATUS_FAILED', 5, -- completed - find out the result decode( oracle_result, 'ERROR', 'ORACLE_COMPARISON_ERROR', oracle_result ) ) oracle_result, decode ( step_status, null, 'JOB_NOT_YET_SCHEDULED', 1, 'STATUS_SCHEDULED', 2, 'BEING_COMPUTED_RESULT', -- executing 3, 'STATUS_ABORTED', 4, 'STATUS_FAILED', 5, -- completed - find out the result decode( os_registered_sw_result, 'ERROR', 'OS_REGISTERED_SW_COMPARISON_ERROR', os_registered_sw_result ) ) os_registered_sw_result from ( select delta_comp_guid, lhs_file_guid, lhs_target_name, rhs_host_name, step_status, max(hw_result) hw_result, max(os_result) os_result, max(oracle_result) oracle_result, max(os_registered_sw_result) os_registered_sw_result from ( with common_columns as ( select map.delta_comp_guid delta_comp_guid, a.lhs_file_guid lhs_file_guid, a.lhs_target_name lhs_target_name, a.rhs_host_name rhs_host_name, a.step_status step_status from ( select h.execution_id, h.step_id, jp.scalar_value lhs_target_name, h.step_status, t.target_name rhs_host_name, jp2.scalar_value lhs_file_guid from mgmt_job_history h, mgmt_job_parameter jp, mgmt_job_parameter jp2, mgmt_job_target jt, mgmt_targets t where h.execution_id = p_execution_id and jp.execution_id = h.execution_id and jp.parameter_name = 'firstCompareTarget' and jp2.execution_id = h.execution_id and jp2.parameter_name = 'fileGUID' and h.step_name = 'CompareTwoHosts' and h.execution_id = jt.execution_id and h.iterate_param_index = jt.target_index and t.target_guid = jt.target_guid ) a, mgmt_comp_result_to_job_map map where a.execution_id = map.execution_id (+) and a.rhs_host_name = map.rhs_host_name(+) ) select c.delta_comp_guid, c.lhs_file_guid, c.lhs_target_name, c.rhs_host_name, c.step_status, decode(count(e.column_value), 0, decode(max(s.different_count+s.left_count+s.right_count), 0, 'COW_RESULT_SAME', 'COW_RESULT_DIFFERENT'), 'ERROR' ) hw_result, null os_result, null oracle_result, null os_registered_sw_result from common_columns c, mgmt_delta_comp_summaries s, table(s.errors)(+) e where c.delta_comp_guid = s.delta_comp_guid(+) and instr(HOST_CONFIG_HW_VIEW_LIST, nvl(s.collection_type, HOST_CONFIG_HW_VIEW_LIST)) != 0 group by c.delta_comp_guid, c.lhs_file_guid, c.lhs_target_name, c.rhs_host_name, c.step_status union select c.delta_comp_guid, c.lhs_file_guid, c.lhs_target_name, c.rhs_host_name, c.step_status, null hw_result, decode(count(e.column_value), 0, decode(max(s.different_count+s.left_count+s.right_count), 0, 'COW_RESULT_SAME', 'COW_RESULT_DIFFERENT'), 'ERROR' ) os_result, null oracle_result, null os_registered_sw_result from common_columns c, mgmt_delta_comp_summaries s, table(s.errors)(+) e where c.delta_comp_guid = s.delta_comp_guid(+) and instr(HOST_CONFIG_OS_VIEW_LIST, nvl(s.collection_type, HOST_CONFIG_OS_VIEW_LIST)) != 0 group by c.delta_comp_guid, c.lhs_file_guid, c.lhs_target_name, c.rhs_host_name, c.step_status union select c.delta_comp_guid, c.lhs_file_guid, c.lhs_target_name, c.rhs_host_name, c.step_status, null hw_result, null os_result, decode(count(e.column_value), 0, decode(max(s.different_count+s.left_count+s.right_count), 0, 'SAME_INSTALLED_VERSIONS_RESULT', 'DIFFERENT_INSTALLED_VERSIONS_RESULT'), 'ERROR' ) oracle_result, null os_registered_sw_result from common_columns c, mgmt_delta_comp_summaries s, table(s.errors)(+) e where c.delta_comp_guid = s.delta_comp_guid(+) and instr(ORACLE_TOPLEVEL_VIEW_NAME, nvl(s.collection_type, ORACLE_TOPLEVEL_VIEW_NAME)) != 0 group by c.delta_comp_guid, c.lhs_file_guid, c.lhs_target_name, c.rhs_host_name, c.step_status union select c.delta_comp_guid, c.lhs_file_guid, c.lhs_target_name, c.rhs_host_name, c.step_status, null hw_result, null os_result, null oracle_result, decode(count(e.column_value), 0, decode(max(s.different_count+s.left_count+s.right_count), 0, 'SAME_INSTALLED_VERSIONS_RESULT', 'DIFFERENT_INSTALLED_VERSIONS_RESULT'), 'ERROR') os_registered_sw_result from common_columns c, mgmt_delta_comp_summaries s, table(s.errors)(+) e where c.delta_comp_guid = s.delta_comp_guid(+) and instr(OS_REGISTERED_SW_VIEW_NAME, nvl(s.collection_type, OS_REGISTERED_SW_VIEW_NAME)) != 0 group by c.delta_comp_guid, c.lhs_file_guid, c.lhs_target_name, c.rhs_host_name, c.step_status ) diff_qry group by delta_comp_guid, lhs_file_guid, lhs_target_name, rhs_host_name, step_status ) diff_qry1; end; /*---------------------------------------------------------------------- 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) is begin open p_cursor for select name, left_value, right_value from mgmt_delta_comp_properties where delta_comp_guid = p_comparison_guid; end; /*---------------------------------------------------------------------- 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) is begin open p_cursor for select left_value, right_value from mgmt_delta_comp_properties where delta_comp_guid = p_comparison_guid and name = p_property_name; end; /*---------------------------------------------------------------------- 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) is l_table_name mgmt_delta_comparison_deltas.collection_type%type := ORACLE_TOPLEVEL_VIEW_NAME; begin open p_cursor for select result, max(decode( name, 'EXTERNAL_NAME', value, ' ' )) name, max(decode( name, 'VERSION',value, ' ' )) version, max(nvl(lhs, 0)) lhs, max(nvl(rhs,0)) rhs from ( select cd.key_guid key_guid, cd.status result, kc.name name, kc.value value, det.left_value lhs, det.right_value rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_key_cols kc, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and kc.key_guid = cd.key_guid and kc.key_guid = det.key_guid(+) ) a group by key_guid, result order by result; end; /*---------------------------------------------------------------------- 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 Implementation Notes -------------------- Please see the explanation in the "Implementation Notes" section of get_oracle_comp_summary. -----------------------------------------------------------------------*/ procedure get_os_reg_sw_comp_summary ( p_comparison_guid raw, p_cursor out sys_refcursor) is l_table_name mgmt_delta_comparison_deltas.collection_type%type := OS_REGISTERED_SW_VIEW_NAME; begin open p_cursor for select result, max(decode( name, 'NAME', value, ' ' )) name, max(decode( name, 'VERSION',value, ' ' )) version, max(decode( name, 'VENDOR_NAME', value, ' ' )) vendor_name, max(nvl(lhs, 0)) lhs, max(nvl(rhs,0)) rhs from ( select cd.key_guid key_guid, cd.status result, kc.name name, kc.value value, det.left_value lhs, det.right_value rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_key_cols kc, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and kc.key_guid = cd.key_guid and kc.key_guid = det.key_guid(+) ) a group by key_guid, result order by result; end; /*---------------------------------------------------------------------- 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 Implementation Notes -------------------- Please see the explanation in the "Implementation Notes" section of get_oracle_comp_summary. -----------------------------------------------------------------------*/ procedure get_hw_master_comparison_dtls ( p_comparison_guid raw, p_cursor out sys_refcursor) is l_table_name mgmt_delta_comparison_deltas.collection_type%type := HARDWARE_MASTER_VIEW_NAME; begin open p_cursor for select decode( det.left_value, det.right_value, 'SAME', 'DIFFERENT') result, nvl(det.name, ' ') name, nvl(det.left_value, ' ') lhs, nvl(det.right_value, ' ') rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_delta_details det, mgmt_ecm_snapshot_md_columns mdc where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and mdc.table_name = cd.collection_type and mdc.name = det.name and cd.key_guid = det.key_guid(+) and det.name not in ( 'FAN_COUNT', 'POWER_SUPPLY_COUNT' ) order by mdc.col_order; end; /*---------------------------------------------------------------------- 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) is l_table_name mgmt_delta_comparison_deltas.collection_type%type := CPU_DETAILS_VIEW_NAME; begin open p_cursor for select result, max(decode( name, 'VENDOR_NAME', name, ' ' )) vendor_name_col, max(decode( name, 'VENDOR_NAME', value, ' ' )) vendor_name, max(decode( name, 'FREQ_IN_MHZ',name, ' ' )) freq_in_mhz_col, max(decode( name, 'FREQ_IN_MHZ',value, ' ' )) freq_in_mhz, max(decode( name, 'ECACHE_IN_MB',name, ' ' )) ecache_in_mb_col, max(decode( name, 'ECACHE_IN_MB',value, ' ' )) ecache_in_mb, max(decode( name, 'IMPL',name, ' ' )) impl_col, max(decode( name, 'IMPL',value, ' ' )) impl, max(decode( name, 'REVISION',name, ' ' )) revision_col, max(decode( name, 'REVISION',value, ' ' )) revision, max(decode( name, 'MASK',name, ' ' )) mask_col, max(decode( name, 'MASK',value, ' ' )) mask, max(nvl(lhs, 0)) lhs, max(nvl(rhs,0)) rhs from ( select cd.key_guid key_guid, cd.status result, kc.name name, kc.value value, det.left_value lhs, det.right_value rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_key_cols kc, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and kc.key_guid = cd.key_guid and kc.key_guid = det.key_guid(+) ) a group by key_guid, result order by result; end; /*---------------------------------------------------------------------- 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 Implementation Notes -------------------- Please see the explanation in the "Implementation Notes" section of get_oracle_comp_summary. -----------------------------------------------------------------------*/ procedure get_iocard_comparison_dtls ( p_comparison_guid raw, p_cursor out sys_refcursor) is l_table_name mgmt_delta_comparison_deltas.collection_type%type := IOCARD_DETAILS_VIEW_NAME; begin open p_cursor for select result, max(decode( name, 'NAME', name, ' ' )) name_col, max(decode( name, 'NAME', value, ' ' )) name, max(decode( name, 'VENDOR_NAME',name, ' ' )) vendor_name_col, max(decode( name, 'VENDOR_NAME',value, ' ' )) vendor_name, max(decode( name, 'FREQ_IN_MHZ',name, ' ' )) freq_in_mhz_col, max(decode( name, 'FREQ_IN_MHZ',value, ' ' )) freq_in_mhz, max(decode( name, 'BUS',name, ' ' )) bus_col, max(decode( name, 'BUS',value, ' ' )) bus, max(decode( name, 'REVISION',name, ' ' )) revision_col, max(decode( name, 'REVISION',value, ' ' )) revision, max(nvl(lhs, 0)) lhs, max(nvl(rhs,0)) rhs from ( select cd.key_guid key_guid, cd.status result, kc.name name, kc.value value, det.left_value lhs, det.right_value rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_key_cols kc, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and kc.key_guid = cd.key_guid and kc.key_guid = det.key_guid(+) ) a group by key_guid, result order by result; end; /*---------------------------------------------------------------------- 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) is l_table_name mgmt_delta_comparison_deltas.collection_type%type := NIC_DETAILS_VIEW_NAME; begin open p_cursor for select result, max(decode( name, 'NAME', name, ' ' )) name_col, max(decode( name, 'NAME', value, ' ' )) name, max(decode( name, 'MAX_TRANSFER_UNIT',name, ' ' )) max_trasnsfer_unit_col, max(decode( name, 'MAX_TRANSFER_UNIT',value, ' ' )) max_trasnsfer_unit, max(decode( name, 'MASK',name, ' ' )) mask_col, max(decode( name, 'MASK',value, ' ' )) mask, max(decode( name, 'FLAGS',name, ' ' )) flags_col, max(decode( name, 'FLAGS',value, ' ' )) flags, max(nvl(lhs, 0)) lhs, max(nvl(rhs,0)) rhs from ( select cd.key_guid key_guid, cd.status result, kc.name name, kc.value value, det.left_value lhs, det.right_value rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_key_cols kc, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and kc.key_guid = cd.key_guid and kc.key_guid = det.key_guid(+) ) a group by key_guid, result order by result; end; /*---------------------------------------------------------------------- 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 Implementation Notes ------------------ Please see the explanation in the "Implementation Notes" section of get_oracle_comp_summary. ---------------------------------------------------------------------*/ procedure get_os_prop_comparison_dtls ( p_comparison_guid raw, p_cursor out sys_refcursor) is l_table_name mgmt_delta_comparison_deltas.collection_type%type := OS_PROPERTIES_VIEW_NAME; begin open p_cursor for select result, max(decode( name, 'NAME', value, ' ' )) name, max(decode( name, 'TYPE',value, ' ' )) type, max(nvl(lhs, ' ')) lhs, max( nvl(rhs, ' ') ) rhs from ( select cd.key_guid key_guid, cd.status result, kc.name name, kc.value value, det.left_value lhs, det.right_value rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_key_cols kc, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and kc.key_guid = cd.key_guid and kc.key_guid = det.key_guid(+) ) a group by key_guid, result order by result; end; /*---------------------------------------------------------------------- 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 Implementation Notes -------------------- The "order_col. is to ensure the following order: 1. Non or component types (e.g. file systems, os propreties etc. 2. OS components other than Patches. 3. Patches The order_col order also makes it trivial to change the order between any of the above sets. The os_component_type is used in the calling code because the logic for displaying each time is different. -----------------------------------------------------------------------*/ procedure get_os_comparison_summary( p_comparison_guid raw, p_cursor out sys_refcursor) is begin open p_cursor for select os_component_type, type, different_count, left_count, right_count from ( ( select 1 order_col, 'NON_OS_COMPONENT_TYPE' os_component_type, collection_type type, different_count, left_count, right_count from mgmt_delta_comp_summaries where delta_comp_guid = p_comparison_guid and collection_type in( OS_PROPERTIES_VIEW_NAME , FS_MOUNT_DETAILS_VIEW_NAME) ) union ( select 2 order_col, 'OS_COMPONENT_NON_PATCH_TYPE' os_component_type, type, max(decode(result, 'DIFFERENT', count, null)) different_count, max(decode(result, 'LEFT_ONLY', count, null)) left_count, max(decode(result, 'RIGHT_ONLY', count, null)) right_count from ( select cd.status result, kc.value type, count(*) count from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_key_cols kc, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = OS_COMPONENTS_VIEW_NAME and kc.key_guid = cd.key_guid and kc.key_guid = det.key_guid(+) and kc.name = 'TYPE' group by kc.value, cd.status ) group by 2, 'OS_COMPONENT_NON_PATCH_TYPE', type ) union ( select 3 order_col, 'OS_COMPONENT_PATCH_TYPE' os_component_type, collection_type type, different_count, left_count, right_count from mgmt_delta_comp_summaries where delta_comp_guid = p_comparison_guid and collection_type =OS_PATCHES_VIEW_NAME ) ) a order by order_col, type; end; /*---------------------------------------------------------------------- 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 Implementation Notes -------------------- Please see the explanation in the "Implementation Notes" section of get_oracle_comp_summary. -----------------------------------------------------------------------*/ procedure get_fs_comparison_dtls( p_comparison_guid raw, p_cursor out sys_refcursor) is l_table_name mgmt_delta_comparison_deltas.collection_type%type := FS_MOUNT_DETAILS_VIEW_NAME; begin open p_cursor for select result, max(decode( name, 'RESOURCE_NAME', name, ' ' )) resource_name_col, max(decode( name, 'RESOURCE_NAME', value, ' ' )) resource_name, max(decode( name, 'TYPE',name, ' ' )) type_col, max(decode( name, 'TYPE',value, ' ' )) type, max(decode( name, 'MOUNT_LOCATION',name, ' ' )) mount_location_col, max(decode( name, 'MOUNT_LOCATION',value, ' ' )) mount_location, max( nvl(diff_col_name, ' ') ) diff_col_name, max(nvl(lhs, ' ')) lhs, max( nvl(rhs, ' ') ) rhs from ( select cd.key_guid key_guid, cd.status result, kc.name name, kc.value value, det.name diff_col_name, det.left_value lhs, det.right_value rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_key_cols kc, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and kc.key_guid = cd.key_guid and kc.key_guid = det.key_guid(+) ) a group by key_guid, result order by result; end; /*---------------------------------------------------------------------- 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) is l_table_name mgmt_delta_comparison_deltas.collection_type%type := OS_COMPONENTS_VIEW_NAME; begin open p_cursor for select result, max(decode( name, 'NAME', name, ' ' )) name_col, max(decode( name, 'NAME', value, ' ' )) name, max( nvl(diff_col_name, ' ') ) diff_col_name, max(nvl(lhs, ' ')) lhs, max( nvl(rhs, ' ') ) rhs from ( select cd.key_guid key_guid, cd.status result, kc.name name, kc.value value, det.name diff_col_name, det.left_value lhs, det.right_value rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_key_cols kc, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and kc.key_guid = cd.key_guid and kc.key_guid = det.key_guid(+) and kc.key_guid in ( select kc1.key_guid from mgmt_delta_comp_key_cols kc1 where kc1.name = 'TYPE' and kc1.value = p_component_type) ) a group by key_guid, result order by result; end; /*---------------------------------------------------------------------- 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) is l_table_name mgmt_delta_comparison_deltas.collection_type%type := OS_PATCHES_VIEW_NAME; begin open p_cursor for select result, max(decode( name, 'NAME', name, ' ' )) name_col, max(decode( name, 'NAME', value, ' ' )) name from ( select cd.key_guid key_guid, cd.status result, kc.name name, kc.value value from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_key_cols kc where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and kc.key_guid = cd.key_guid ) a group by key_guid, result order by result; end; /*---------------------------------------------------------------------- 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) is l_table_name mgmt_delta_comparison_deltas.collection_type%type := OS_SUMMARY_VIEW_NAME; begin open p_cursor for select result, lhs_name || ' '|| lhs_base_version || decode(lhs_update_level, null,'',' '|| lhs_update_level)|| decode ( lhs_address_length_in_bits, null, '', ' (' || lhs_address_length_in_bits || ')' || decode( lhs_max_swap_space_in_mb, null, '', ' ' || lhs_max_swap_space_in_mb || ' MB Maximum Swap Space') ) lhs_os_summary, rhs_name || ' '|| rhs_base_version || decode(rhs_update_level, null,'',' '|| rhs_update_level)|| decode ( rhs_address_length_in_bits, null, '', ' (' || rhs_address_length_in_bits || ')' || decode( rhs_max_swap_space_in_mb, null, '', ' ' || rhs_max_swap_space_in_mb || ' MB Maximum Swap Space') ) rhs_os_summary from ( select vendor.result result, vendor.lhs lhs_vendor_name, vendor.rhs rhs_vendor_name, name.lhs lhs_name, name.rhs rhs_name, bv.lhs lhs_base_version, bv.rhs rhs_base_version, ul.lhs lhs_update_level, ul.rhs rhs_update_level, dv.lhs lhs_distributor_version, dv.rhs rhs_distributor_version, ms.lhs lhs_max_swap_space_in_mb, ms.rhs rhs_max_swap_space_in_mb, al.lhs lhs_address_length_in_bits, al.rhs rhs_address_length_in_bits from ( select cd.key_guid key_guid, cd.status result, det.name name, det.left_value lhs, det.right_value rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and det.name = 'VENDOR_NAME' and cd.key_guid = det.key_guid(+) ) vendor, ( select cd.key_guid key_guid, cd.status result, det.name name, det.left_value lhs, det.right_value rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and det.name = 'NAME' and cd.key_guid = det.key_guid(+) ) name, ( select cd.key_guid key_guid, cd.status result, det.name name, det.left_value lhs, det.right_value rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and det.name = 'BASE_VERSION' and cd.key_guid = det.key_guid(+) ) bv, ( select cd.key_guid key_guid, cd.status result, det.name name, det.left_value lhs, det.right_value rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and det.name = 'UPDATE_LEVEL' and cd.key_guid = det.key_guid(+) ) ul, ( select cd.key_guid key_guid, cd.status result, det.name name, det.left_value lhs, det.right_value rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and det.name = 'DISTRIBUTOR_VERSION' and cd.key_guid = det.key_guid(+) ) dv, ( select cd.key_guid key_guid, cd.status result, det.name name, det.left_value lhs, det.right_value rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and det.name = 'MAX_SWAP_SPACE_IN_MB' and cd.key_guid = det.key_guid(+) ) ms, ( select cd.key_guid key_guid, cd.status result, det.name name, det.left_value lhs, det.right_value rhs from mgmt_delta_comparison_deltas cd, mgmt_delta_comp_delta_details det where cd.delta_comp_guid = p_comparison_guid and cd.collection_type = l_table_name and det.name = 'ADDRESS_LENGTH_IN_BITS' and cd.key_guid = det.key_guid(+) ) al where vendor.result=name.result and vendor.key_guid = name.key_guid and bv.result=name.result and bv.key_guid = name.key_guid and ul.result=name.result and ul.key_guid = name.key_guid and dv.result=name.result and dv.key_guid = name.key_guid and ms.result=name.result and ms.key_guid = name.key_guid and al.result=name.result and al.key_guid = name.key_guid order by result ); end; /*---------------------------------------------------------------------- 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) is begin select hextoraw( p_first_string), hextoraw( p_second_string ) into p_first_raw_result, p_second_raw_result from dual; end; /*---------------------------------------------------------------------- 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. Implementation Notes ------------------ None ---------------------------------------------------------------------*/ procedure get_comparison_errors ( p_comparison_guid in raw, p_which_component in integer, p_errors_cursor out sys_refcursor) is begin open p_errors_cursor for select ee.column_value from mgmt_delta_comp_summaries s, table(s.errors) ee where s.delta_comp_guid = p_comparison_guid and instr( decode( p_which_component, HW_SUMMARY_CASE, HOST_CONFIG_HW_VIEW_LIST, OS_SUMMARY_CASE, HOST_CONFIG_OS_VIEW_LIST, s.collection_type ), s.collection_type) != 0; end; end host_config_comparison; / show errors;