Rem Rem $Header: ecm_hc_delta_pkgbody.sql 05-sep-2004.00:36:36 mgoodric Exp $ Rem Rem ecm_hc_delta_pkgbody.sql Rem Rem Copyright (c) 2002, 2004, Oracle. All rights reserved. Rem Rem NAME Rem ecm_hc_delta_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem mgoodric 07/30/04 - Include all same values along with changes to support History Detail Record Rem shuberma 06/26/03 - History calculations for one-off patches use referencing wrong query Rem shuberma 03/17/03 - make sure initial refresh gets recorded Rem shuberma 03/13/03 - File system should be matched on resource name, type and location Rem shuberma 03/13/03 - File system should be matched on resource name, type and location Rem shuberma 01/14/03 - Missing start_delta_call Rem shuberma 12/12/02 - Adding type param to delta_values Rem shuberma 11/21/02 - Switch over to generic history Rem shuberma 09/16/02 - Fixing bug in record_hc_diffs Rem shuberma 08/23/02 - Remove vendor component history Rem rmenon 08/21/02 - incorporated the new column hostname_aliases in the history logic - also removed fs mount time from history collection code Rem shuberma 08/14/02 - Add history for nic and files Rem shuberma 07/08/02 - Using NVL for null value columns during comparison of vendor sw. Rem shuberma 06/24/02 - Adding vendor sw tracking Rem shuberma 06/21/02 - Adding vendor software history Rem rmenon 06/17/02 - added changes for the new columns in mgmt_hc_os_summary table Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Created Rem CREATE OR REPLACE PACKAGE BODY MGMT_HC_DELTA IS -------------------------------------------------------------------------------- -- DO_AND_SAVE_HISTORY -- -- A utility function to be called by the ECM Java loader to perform a history -- comparison of the new and old versions of the snapshot for a host. The results -- are saved in the generic history tables. -- -- PARAMETERS -- -- p_snapshot_type The type of the snapshot, e.g. 'host_configuration' -- p_target_type Identifies the type of target, since snapshot_type is only -- unique within a target type. -- new_guid The snapshot_guid of the new current snapshot. -- old_guid The snapshot_guid of the previous current snapshot. -- PROCEDURE DO_AND_SAVE_HISTORY( p_snapshot_type VARCHAR2, p_target_type VARCHAR2, new_guid RAW, old_guid RAW ) IS n_snaptime DATE := NULL; o_snaptime DATE := NULL; n_target_name VARCHAR2(256) := NULL; o_target_name VARCHAR2(256) := NULL; delta_guid RAW(16); l_metadata_id RAW(16); BEGIN -- First get the presentation metadata id for this snapshot type SELECT m.metadata_id INTO l_metadata_id FROM MGMT_ECM_SNAPSHOT_METADATA m WHERE m.SNAPSHOT_TYPE = p_snapshot_type AND m.TARGET_TYPE = p_target_type AND m.KIND = 'P'; BEGIN -- get the snapshot information for the first snapshot. (The new one). SELECT n.START_TIMESTAMP, n.TARGET_NAME INTO n_snaptime, n_target_name FROM MGMT_ECM_SNAPSHOT n WHERE n.SNAPSHOT_GUID = new_guid; EXCEPTION -- Return if the snapshot doesn't exist. WHEN NO_DATA_FOUND THEN RETURN; END; -- Now get the snapshot information for the second (old) snapshot. -- Do not return right away. First record that the history was calculated -- even if for the first time the target was refreshed. -- Now get the snapshot information for the second (old) snapshot. -- Do not return right away. First record that the history was calculated -- even if for the first time the target was refreshed. BEGIN SELECT o.START_TIMESTAMP, o.TARGET_NAME INTO o_snaptime, o_target_name FROM MGMT_ECM_SNAPSHOT o WHERE o.SNAPSHOT_GUID = old_guid; EXCEPTION WHEN NO_DATA_FOUND THEN o_snaptime := NULL; END; -- Record the fact that history was on, even if the old snapshot -- doesn't exist. delta_guid := MGMT_DELTA.START_DELTA(new_guid, n_snaptime, n_target_name, old_guid, o_snaptime, o_target_name, p_target_type, p_snapshot_type, 'HISTORY'); -- Return if old snapshot doesn't exist. IF o_snaptime IS NULL THEN RETURN; END IF; MGMT_DELTA.DO_HISTORY_DIFFS( MGMT_ECM_SNAPSHOT_PKG.PREPARE_DELTA_QUERIES( new_guid, old_guid, MGMT_ECM_SNAPSHOT_PKG.HISTORY_ON_C, NULL, MGMT_DELTA.RETURN_DIFFS, l_metadata_id ), n_snaptime ); END DO_AND_SAVE_HISTORY; ------------------------------------------------------------------------------------ -- RECORD_HC_DIFFS -- -- This procedure is responsible for collecting history differences for host -- host configurations. It is a special purpose procedure with the details -- of the hc tables built into it. It should be replaced in the future with -- a general purpose procedure that uses register metadata to perform the -- history comparison. -- -- PARAMETERS -- -- first_target_name The target name of the first snapshot (current) -- first_target_type The target type of the first snapshot -- first_snapshot_type The type of the first snapshot. This is -- always 'host_configuration' -- second_target_name Except when testing, this is always the same as -- the first. It just isn't current. -- second_target_type Same as first. -- second_snapshot_type Except for testing, this is currently always -- 'DELTA' -- comparison_type Doesn't really matter, but is set to 'DELTA' -- PROCEDURE RECORD_HC_DIFFS( first_target_name VARCHAR2, first_target_type VARCHAR2, first_snapshot_type VARCHAR2, second_target_name VARCHAR2, second_target_type VARCHAR2, second_snapshot_type VARCHAR2, comparison_type VARCHAR2 ) IS f_snapguid RAW(16); s_snapguid RAW(16) := NULL; f_timestamp DATE; s_timestamp DATE := NULL; BEGIN -- Check to see if we want to capture the diffs. For example, if there wasn't a -- pre-exsiting snapshot then don't bother. However, record the a delta snap -- to inidicate history was being recorded. -- REALLY SHOULD BE PASSING SNAPSHOT_GUIDS instead of the name, type and type. BEGIN -- Get the first (new) target's info. If doesn't exist, then return. SELECT f.SNAPSHOT_GUID, f.START_TIMESTAMP INTO f_snapguid, f_timestamp FROM MGMT_ECM_SNAPSHOT f WHERE f.TARGET_NAME = first_target_name AND f.TARGET_TYPE = first_target_type AND f.SNAPSHOT_TYPE = first_snapshot_type AND f.IS_CURRENT = 'Y'; EXCEPTION -- Return WHEN NO_DATA_FOUND THEN RETURN; END; -- Get the second (old) info. If this doesn't exist, then don't return yet. BEGIN SELECT s.SNAPSHOT_GUID, s.START_TIMESTAMP INTO s_snapguid, s_timestamp FROM MGMT_ECM_SNAPSHOT s WHERE s.TARGET_NAME = second_target_name AND s.TARGET_TYPE = second_target_type AND s.SNAPSHOT_TYPE = second_snapshot_type AND s.IS_CURRENT = 'Y'; EXCEPTION WHEN NO_DATA_FOUND THEN s_snapguid := NULL; END; DO_AND_SAVE_HISTORY ( first_snapshot_type, first_target_type, f_snapguid, s_snapguid ); END RECORD_HC_DIFFS; END MGMT_HC_DELTA; / show errors;