Rem Rem $Header: ecm_snapshot_pkgdef.sql 13-dec-2005.23:41:45 lappanmu Exp $ Rem Rem ecm_snapshot_pkgdef.sql Rem Rem Copyright (c) 2002, 2005, Oracle. All rights reserved. Rem Rem NAME Rem ecm_snapshot_pkgdef.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem lappanmu 12/13/05 - XbranchMerge lappanmu_bug-4684929 from main Rem jochen 11/11/05 - Add get_generic_comparison_with_keys Rem akskumar 08/31/05 - on jobdelete callback for saved comparisons Rem agor 06/21/05 - remove GET_COMPARISON_RESULTS_QUERY from def Rem akskumar 10/08/04 - Rem akskumar 09/16/04 - Rem mgoodric 08/31/04 - integrate configHistory Rem akskumar 08/12/04 - add procedure to get generic configuration Rem comparison summary Rem akskumar 07/06/04 - add fucction compare_and_save_job Rem awarkar 11/15/03 - Bug Fix For Issue#3255739 Rem shuberma 06/20/03 - Expose getKeyColumnList Rem shuberma 06/12/03 - Deprecate compare_snapshots Rem shuberma 05/05/03 - Adding an optional argument for setting the default flag value Rem shuberma 05/02/03 - Adding compare properties and flags to interface Rem shuberma 03/18/03 - Adding new api procedures for fw Rem shuberma 12/13/02 - Addmethods for getting diff result data Rem shuberma 11/22/02 - Rem shuberma 11/19/02 - Mathcing body's signatures Rem shuberma 09/16/02 - shuberma_config_fw_diff Rem shuberma 09/12/02 - Created Rem CREATE OR REPLACE PACKAGE MGMT_ECM_SNAPSHOT_PKG IS UI_ON_C CONSTANT NUMBER := 1; COMPARE_ON_C CONSTANT NUMBER := 2; HISTORY_ON_C CONSTANT NUMBER := 3; TYPE TABLE_FLAGS IS RECORD ( TABLE_NAME VARCHAR2(64), FLAGS SMALLINT ); TYPE TABLE_FLAGS_LIST IS TABLE OF TABLE_FLAGS; ---------------------------------------------------------------------------- -- Get the key list. Need to look at the metadata of this table and the -- parent tables, even though the actual table has all the columns in it. -- PARAMETERS: -- p_table_name The name of the table, registered in the metadata for -- which we want to get the key list. -- p_metadata_id The id of the metadata. This identifies which set of tables -- in the snapshot. -- p_whichOnFlag COMPARE_ON, HISTORY_ON, UI_ON -- RETURNS: -- A list of column names. FUNCTION GET_KEY_COLUMN_LIST ( p_table_name IN VARCHAR2, p_metadata_id IN RAW, p_whichOnFlag IN NUMBER ) RETURN VARCHAR2_TABLE; ------------------------------------------------------------------------------- -- Get the non-key list. Need to look at the metadata of this table and the -- parent tables, even though the actual table has all the columns in it. -- PARAMETERS: -- p_table_name The name of the table, registered in the metadata for -- which we want to get the non key list. -- p_metadata_id The id of the metadata. This identifies which set of tables -- in the snapshot. -- p_whichOnFlag COMPARE_ON, HISTORY_ON, UI_ON -- RETURNS: -- A list of column names. FUNCTION GET_NONKEY_COLUMN_LIST ( p_table_name IN VARCHAR2, p_metadata_id IN RAW, p_whichOnFlag IN NUMBER ) RETURN VARCHAR2_TABLE; ------------------------------------------------------------------------ -- PREPARE_DELTA_QUERIES -- Utility function to create the MGMT_DELTA_QUERIES structure given -- the left and right guids. -- PARAMETERS -- p_left_guid - identifies the left or first snapshot instance to compare. -- p_right_guid - identifies the right or second snapshot instance to compare. -- p_whichOnFlag - COMPARE_ON_C, HISTORY_ON_C, UI_ON_C -- HISTORY_ON_C if the queries are being prepared for history. -- COMPARE_ON_C if the queries are for comparison. -- p_table_flags_overrides - Default null, is list of table names and flags indicating -- whether to MGMT_DELTA.RETURN_ALL, MGMT_DELTA.RETURN_NONE or MGMT_DELTA.RETURN_DIFFS. -- p_table_flags_default - The default value of table flags if no override is given. -- p_metadata_id - The id of the metadata. This identifies which set of tables -- in the snapshot. -- RETURNS -- MGMT_DELTA_QUERIES -- FUNCTION PREPARE_DELTA_QUERIES( p_left_guid RAW, p_right_guid RAW, p_whichOn NUMBER, p_table_flags_overrides TABLE_FLAGS_LIST, p_table_flags_default SMALLINT, p_metadata_id RAW ) RETURN MGMT_DELTA_QUERIES; /*---------------------------------------------------------------------- The function compare_gen_conf_and_save is a wrapper around steve's API and is used to compare and save results of two generic configs. 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 now, it sets the flag of "RETURN_ALL" for all generic configs 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_gen_conf_and_save (p_snapshot_type VARCHAR2, p_target_type VARCHAR2, p_lhs_guid RAW, p_rhs_guid RAW, p_session_id in raw default SYS_GUID() ) return RAW; ------------------------------------------------------------------------------- -- COMPARE_AND_SAVE_JOB -- This is a wrap around function to compare_gen_conf_and_save -- As it was stated in host_config_comparison.compare_two_hosts_job -- before launching compare, 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. -- PARAMETERS -- snapshot_type - Identifies the type of snapshot being compared. -- target_type - Identifies the type of target, since snapshot_type is only -- unique within a target type. -- left_guid - identifies the left or first snapshot instance to compare. -- right_guid - identifies the right or second snapshot instance to compare. -- p_job_step_id - the job's step id (passed from the job) -- session_id - identifies the session in which this comparison is saved. -- RETURNS -- A guid pointing to the saved comparison. (In MGMT_DELTA_SAVED_COMPARISONS). -- FUNCTION COMPARE_AND_SAVE_JOB( p_snapshot_type VARCHAR2, p_target_type VARCHAR2, p_left_guid RAW, p_right_guid RAW, p_job_step_id in number, p_session_id RAW DEFAULT SYS_GUID()) RETURN RAW; PROCEDURE ON_COMP_JOB_DELETE_CALLBACK(p_reason NUMBER, p_status NUMBER, p_job_id RAW, p_execution_id RAW); /*---------------------------------------------------------------------- The procedure get_multi_configs_comp_summary returns the summary information of comparing one configurations with multiple configurations 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 configuration to multiple configurations. Output parameters ----------------- 1. p_cursor - ref cursor result of multi-configurations comparison result. 2. p_view_url - output varchar - view_url for unique target type,snapshot type for this comparison job required to generate URLs to view config 3. p_compare_url - output varchar - compare_url for unique target type,snapshot type for this comparison job required to generate URLs to view comparison results -----------------------------------------------------------------------*/ procedure get_multi_configs_comp_summary ( p_execution_id raw, p_cursor out sys_refcursor, p_view_url out VARCHAR2, p_compare_url out VARCHAR2); ------------------------------------------------------------------------------- -- COMPARE_AND_SAVE -- Function that can be called from the gui or job to initiate a comparison -- and save the results in the repository. A key to the saved results is returned. -- PARAMETERS -- snapshot_type - Identifies the type of snapshot being compared. -- target_type - Identifies the type of target, since snapshot_type is only -- unique within a target type. -- left_guid - identifies the left or first snapshot instance to compare. -- right_guid - identifies the right or second snapshot instance to compare. -- session_id - identifies the session in which this comparison is saved. -- properties - name value-value triplet list. Used to same properties along with saved -- comparison. -- table_flags_override - Default null, is list of table names and flags indicating -- whether to MGMT_DELTA.RETURN_ALL, MGMT_DELTA.RETURN_NONE or MGMT_DELTA.RETURN_DIFFS. -- table_flags_default - The default value of table flags if no override is given. -- RETURNS -- A guid pointing to the saved comparison. (In MGMT_DELTA_SAVED_COMPARISONS). -- FUNCTION COMPARE_AND_SAVE( p_snapshot_type VARCHAR2, p_target_type VARCHAR2, p_left_guid RAW, p_right_guid RAW, p_session_id RAW DEFAULT SYS_GUID(), p_properties MGMT_DELTA.COMPARISON_PROPERTIES := NULL, p_table_flags_overrides TABLE_FLAGS_LIST := NULL, p_table_flags_default SMALLINT := MGMT_DELTA.RETURN_DIFFS ) RETURN RAW; -- *************************** -- @deprecated!!!!!!! DO NOT CALL!!!!! -- COMPARE_SNAPSHOTS -- Similar to COMPARE_AND_SAVE. In this case, the results are not saved in -- the respository but instead are returned directly. -- PARAMETERS -- snapshot_type - Identifies the type of snapshot being compared. -- target_type - Identifies the type of target, since snapshot_type is only -- unique within a target type. -- left_guid - identifies the left or first snapshot instance to compare. -- right_guid - identifies the right or second snapshot instance to compare. -- p_deRef - A cursor pointing to MGMT_DELTA_ENTRY rows. -- p_dsRef - A cursor pointing to MGMT_DELTA_SUMMARY rows. -- table_flags_override - Default null, is list of table names and flags indicating -- whether to MGMT_DELTA.RETURN_ALL, MGMT_DELTA.RETURN_NONE or MGMT_DELTA.RETURN_DIFFS. -- table_flags_default - The default value of table flags if no override is given. -- PROCEDURE COMPARE_SNAPSHOTS( p_snapshot_type VARCHAR2, p_target_type VARCHAR2, p_left_guid RAW, p_right_guid RAW, p_deRef out MGMT_DELTA.DELTA_ENTRY_REF, p_dsRef out MGMT_DELTA.DELTA_SUMMARY_REF, p_table_flags_overrides TABLE_FLAGS_LIST := NULL, p_table_flags_default SMALLINT := MGMT_DELTA.RETURN_DIFFS ); ------------------------------------------------------------------------------------ -- DO_AND_SAVE_HISTORY -- A utility function to be called by the 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' -- target_type - Identifies the type of target, since snapshot_type is only -- unique within a target type. -- p_new_guid - The snapshot_guid of the new current snapshot. -- p_old_guid - The snapshot_guid of the previous current snapshot. -- PROCEDURE DO_AND_SAVE_HISTORY( p_snapshot_type VARCHAR2, p_target_type VARCHAR2, p_new_guid RAW, p_old_guid RAW ); ------------------------------------------------------------------------------------ -- DELETE_SAVED_COMPARISON -- A utility procedure to delete a saved comparison. If the comparison -- exists, it is deleted. If it doesn't exist, no action. -- PARAMETERS -- p_comparison_guid - A guid returned from COMPARE_AND_SAVED() function. -- PROCEDURE DELETE_SAVED_COMPARISON( p_comparison_guid RAW ); ------------------------------------------------------------------------------------ -- PURGE_HISTORY_RECORDS -- -- PURPOSE -- Cleans up all history records older than the number of days passed in. -- Returns the number of history entries purged. -- -- PARAMETERS -- -- p_numberOfDays The number of days, prior to which the history should be -- purged. -- p_snapshot_type Restrict the history purging to this snapshot_type, provided -- it isn't null. Otherwise, do any snapshot type. -- p_target_type Restrict the history purging to this target_type, provided -- it isn't null. Otherwise, do any target type. -- p_target_name Restrict the history purging to this target_name, provided -- it isn't null. Otherwise, do any p_target_name. -- -- RETURNS The number of history entries purged. -- FUNCTION PURGE_HISTORY( p_numberOfDays NUMBER := 0, p_snapshot_type VARCHAR2 := NULL, p_target_type VARCHAR2 := NULL, p_target_name VARCHAR2 := NULL ) RETURN NUMBER; --------------------------------------------------------------------------------- -- GET_COMPARISON_RESULTS_QUERY -- A utility function that returns a query that retrieves comparison results -- PARAMETERS -- p_snapshot_type - AKA comparison_type, maps to the snapshot type used in the -- comparison. -- p_target_type - The target type of the comparison objects. -- p_table_name - A table/view which is in fw snapshot identified by the -- snapshot_type and the target_type. -- -- The following is an example of the query returned given that the snapshot -- type is 'My_Snapshot_Type', the target type is 'My_Target_Type', the table -- name is 'My_Table'. For this example, My_Table has N key columns named "key1", -- "key2"..."keyN" and M non key columns named "nonkey1"..."nonkeyM" -- -- SELECT -- cd.status as row_status, -- key1.value as key1, -- key1.type as key1_type, -- key2.value as key2, -- key2.type as key2_type, -- ... -- keyN.value as keyN, -- keyN.type as keyN_type, -- nonkey1.status as nonkey1_status, -- nonkey1.left_value as nonkey1_left, -- nonkey1.right_value as nonkey1_right, -- nonkey1.type as nonkey1_type, -- nonkey2.status as nonkey2_status, -- nonkey2.left_value as nonkey2_left, -- nonkey2.right_value as nonkey2_right, -- nonkey2.type as nonkey2_type, -- ... -- nonkeyM.status as nonkeyM_status, -- nonkeyM.left_value as nonkeyM_left, -- nonkeyM.right_value as nonkeyM_right, -- nonkeyM.type as nonkeyM_type, -- FROM -- MGMT_DELTA_SAVED_COMPARISON sc, -- MGMT_DELTA_COMPARISON_DELTAS cd, -- MGMT_DELTA_COMP_KEY_COLS key1, -- MGMT_DELTA_COMP_KEY_COLS key2, -- ... -- MGMT_DELTA_COMP_KEY_COLS keyN, -- MGMT_DELTA_COMP_DELTA_DETAILS nonkey1, -- MGMT_DELTA_COMP_DELTA_DETAILS nonkey2, -- ... -- MGMT_DELTA_COMP_DELTA_DETAILS nonkeyM -- WHERE sc.DELTA_COMP_GUID = :comparison_guid -- AND sc.COMPARISON_TYPE = 'My_Snapshot_Type' -- AND sc.TARGET_TYPE = 'My_Target_Type' -- AND sc.DELTA_COMP_GUID = cd.DELTA_COMP_GUID -- AND cd.COLLECTION_TYPE = 'My_Table' -- AND key1.KEY_GUID = cd.KEY_GUID -- AND key2.KEY_GUID = cd.KEY_GUID -- ... -- AND keyN.KEY_GUID = cd.KEY_GUID -- AND nonkey1.KEY_GUID = cd.KEY_GUID -- AND nonkey1.NAME = 'nonkey1' -- AND nonkey2.KEY_GUID = cd.KEY_GUID -- AND nonkey2.NAME = 'nonkey2' -- ... -- AND nonkeyM.KEY_GUID = cd. KEY_GUID -- AND nonkeyM.NAME = 'nonkeyM'; FUNCTION GET_COMPARISON_RESULTS_QUERY( p_snapshot_type VARCHAR2, p_target_type VARCHAR2, p_table_name VARCHAR2 ) RETURN VARCHAR2; ----------------------------------------------------------------------------- -- The procedure get_generic_comparison_results retrieves comparison results. -- it uses the utility function get_comparison_results_query. -- -- Input parameters ------------------ -- p_table_name - A table/view which is in fw snapshot identified by the -- snapshot_type and the target_type. -- p_comparison_guid - generic config comparison GUID. -- p_keyColumn_name - key Column name for filtering Drill down results. -- p_keyColumn_value - key Column value for filtering Drill down results -- Output parameters -- ----------------- -- 1. p_cursor - ref cursor result of configurations comparison result. -- ----------------------------------------------------------------------------- procedure get_generic_comparison_results ( p_table_name VARCHAR2, p_comparison_guid RAW, p_keyColumn_name VARCHAR2, p_keyColumn_value VARCHAR2, p_cursor out sys_refcursor); ----------------------------------------------------------------------------- -- ** NOTE ** -- This procedure is a clone of get_generic_comparison_results -- with the difference that an array of key column names and values -- is accepted rather than just one pair. -- -- This procedure is only used by the 10.2.0.2 generic comparison -- viewer. It has been cloned to avoid perturbing the existing -- code base. -- -- The procedure get_generic_comp_with_keys retrieves comparison -- results. -- it uses the utility function get_comparison_results_query. -- -- Input parameters ------------------ -- p_table_name - A table/view which is in fw snapshot identified by the -- snapshot_type and the target_type. -- p_comparison_guid - generic config comparison GUID. -- p_keys - table of keyname/value records for filtering Drill -- down results. -- Output parameters -- ----------------- -- 1. p_cursor - ref cursor result of configurations comparison result. -- ----------------------------------------------------------------------------- PROCEDURE get_generic_comp_with_keys ( p_table_name VARCHAR2, p_comparison_guid RAW, p_keys ecm_gendiff_compkeys_table, p_cursor out sys_refcursor ); END MGMT_ECM_SNAPSHOT_PKG; / show errors;