Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/ecm/ecm_snapshot_pkgbody.sql /st_emcore_10.2.0.4.3db11.2.0.2/1 2010/05/03 14:06:00 agor Exp $ Rem Rem ecm_snapshot_pkgbody.sql Rem Rem Copyright (c) 2002, 2010, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem ecm_snapshot_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem rajranja 04/29/10 - potential sql-j bug 9588533 Rem pinshah 07/09/09 - Fix for Bug 8657870 - sql injection Rem vkhizder 06/18/09 - XbranchMerge vkhizder_bug6503115_10g from Rem st_emcore_10.2.0.1.0 Rem niramach 05/20/08 - Bug fix for 7010910 (SQL injection) Rem jochen 02/09/06 - XbranchMerge jochen_bug-5024185 from main Rem lappanmu 12/13/05 - XbranchMerge lappanmu_bug-4684929 from main Rem jochen 02/08/06 - Fix get_generic_comp identifier lengths Rem jochen 11/11/05 - Add get_generic_comparison_with_keys Rem akskumar 08/30/05 - bug-4503341 Rem akskumar 07/06/05 - bug-4186461 Rem agor 06/21/05 - fix 4148620 again - use bind params. Rem akskumar 05/24/05 - bug-4257895 Rem agor 05/23/05 - fix 4148620 Rem mgoodric 04/11/05 - fix bug 4148620 (SQL injection) Rem agor 03/03/05 - fix 4154174 Rem akskumar 10/08/04 - Rem smalathe 09/24/04 - Bug#3879246: Change STRING_TYPE_C to RAW_TYPE_C Rem akskumar 09/16/04 - Rem mgoodric 08/08/04 - Include all same values along with changes to support History Detail Record 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 - fix getkeylist query and make procedure Rem shuberma 06/16/03 - Updating so column and table names are double quoted when queries are generated Rem shuberma 06/12/03 - Making add_sames_for_dirllins more efficient Rem shuberma 06/11/03 - Use recorder object instead of save_comparison_delta Rem shuberma 05/05/03 - adding an option for setting the default flags Rem shuberma 03/18/03 - Adding new api procedures for fw Rem shuberma 03/17/03 - Record a delta snap the first time a target is refreshed. Return before actually doing a comparison Rem shuberma 03/14/03 - Fixing problem when tables being compared have no keys Rem shuberma 01/14/03 - Changes made to start_delta signature Rem shuberma 12/30/02 - History needs to check for history_on, not compare_on Rem shuberma 12/18/02 - Adding "SAME" for parent rows. Rem shuberma 12/13/02 - Adding methods for getting diff data Rem shuberma 12/10/02 - Use new save comparison method Rem shuberma 12/06/02 - Guid column is ECM_SNAPSHOT_ID not SNAPSHOT_GUID Rem shuberma 11/20/02 - Coding history body Rem shuberma 11/19/02 - Adding body Rem rpinnama 10/18/02 - Disable echo Rem shuberma 09/16/02 - shuberma_config_fw_diff Rem shuberma 09/12/02 - Created Rem CREATE OR REPLACE PACKAGE BODY MGMT_ECM_SNAPSHOT_PKG IS TARGET_NAME_PROP constant varchar2(20) := 'TargetName'; TARGET_TYPE_PROP constant varchar2(20) := 'TargetType'; HOST_NAME_PROP constant varchar2(20) := 'HostName'; IS_CURRENT_PROP constant varchar2(20) := 'IsCurrent'; TARGET_TIME_STAMP_PROP constant varchar2(20) := 'TargetTimestamp'; TARGET_TIMEZONE_PROP constant varchar2(20) := 'TargetTimezone'; -- This query will return the parent row columns for rows that have child diffs -- but don't yet have a parent diff. The results will be used to add a "same" -- diff entry. Must bind the snapshot_guid, parent_table_name and comp_id. Also -- it only looks at its children not children children, so it is important -- that the children get processed first. FIXUP_QUERY_TEMPLATE CONSTANT VARCHAR2(4000) := 'SELECT MGMT_NAME_VALUES( ), MGMT_DELTA_VALUES( ) FROM "" parent_table WHERE parent_table.ecm_snapshot_id = :snapshot_guid AND EXISTS ( SELECT * FROM mgmt_ecm_snapshot_md_tables t, mgmt_delta_comparison_deltas d, WHERE d.collection_type = t.name AND d.delta_comp_guid = :comp_guid AND t.parent_table_name = '''' ) AND NOT EXISTS ( SELECT * FROM mgmt_delta_comparison_deltas d, WHERE d.collection_type = '''' AND d.delta_comp_guid = :comp_guid )'; -- Two things, -- First a string representation of the MGMT_NAME_VALUES list of key values. -- Second, a string representation of the MGMT_DELTA_VALUES list of diffs. -- E.G. -- MGMT_NAME_VALUE( 'SOFTWARE_NAME', 'foobar', 'S' ), -- MGMT_NAME_VALUE( 'VENDOR_NAME', 'veritas', 'S' ), -- MGMT_NAME_VALUE( 'INSTALLED_LOCATION', '/here', 'S' ), -- MGMT_NAME_VALUE( 'SOFTWARE_VERSION', '3.1', 'S' ) K_NAME_VALUES CONSTANT CHAR(13) := ''; -- MGMT_DELTA_VALUE( 'DESCRIPTION', 'thid thing', 'thid thing', 'SAME', 'S') K_DELTA_VALUES CONSTANT CHAR(14) := ''; -- The parent table name K_PARENT_TABLE_NAME CONSTANT CHAR(19) := ''; -- a comman list of the mgmt_delta_key_cols with numbered alias: e.g.: -- ,mgmt_delta_key_cols k1, mgmt_delta_key_cols k2... K_KEY_COLS_TABS CONSTANT CHAR(15) := ''; -- a and list of the key alias join condition: e.g.: -- and k1.key_guid = d.key_guid -- and k1.NAME = 'KEY_NAME' -- and k1.VALUE = parent_table."KEY_NAME"... K_KEY_COLS_WHERE CONSTANT CHAR(16) := ''; -------------------------------------------------------------------------------------------- -- CRQT1 CONSTANT VARCHAR(7) := ''; -- CRQT2 CONSTANT VARCHAR(7) := ''; -- CRQT3 CONSTANT VARCHAR(7) := ''; -- CRQT4 CONSTANT VARCHAR(7) := ''; -- CRQT5 CONSTANT VARCHAR(7) := ''; -- CRQT6 CONSTANT VARCHAR(7) := ''; -- CRQT7 CONSTANT VARCHAR(7) := ''; -- CRQT8 CONSTANT VARCHAR(7) := ''; -- CRQT9 CONSTANT VARCHAR(7) := ''; -- CRQTA CONSTANT VARCHAR(7) := ''; COMPARE_RESULTS_QUERY_TEMPLATE CONSTANT VARCHAR2(800) := 'SELECT cd.status as row_status FROM MGMT_DELTA_SAVED_COMPARISON sc, MGMT_DELTA_COMPARISON_DELTAS cd WHERE sc.DELTA_COMP_GUID = :comp_guid AND sc.COMPARISON_TYPE = '''' AND sc.TARGET_TYPE = '''' AND sc.DELTA_COMP_GUID = cd.DELTA_COMP_GUID AND cd.COLLECTION_TYPE = :table_name '; ---------------------------------------------------------------------------- -- 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. FUNCTION GET_KEY_COLUMN_LIST ( p_table_name IN VARCHAR2, p_metadata_id IN RAW, p_whichOnFlag IN NUMBER ) RETURN VARCHAR2_TABLE IS l_key_list VARCHAR2_TABLE := NULL; BEGIN BEGIN SELECT c.name BULK COLLECT INTO l_key_list FROM ( SELECT name FROM mgmt_ecm_snapshot_md_tables t START WITH name = p_table_name AND metadata_id = p_metadata_id CONNECT BY name = PRIOR t.parent_table_name AND metadata_id = p_metadata_id ) t, mgmt_ecm_snapshot_md_columns c WHERE (c.table_name = t.name) AND (c.metadata_id = p_metadata_id) AND (c.IS_KEY = 'Y') AND ((p_whichOnFlag=UI_ON_C AND c.UI_ON = 'Y') OR (p_whichOnFlag=COMPARE_ON_C AND c.COMPARE_ON = 'Y') OR (p_whichOnFlag=HISTORY_ON_C AND c.HISTORY_ON = 'Y')); -- ignore if no key columns found EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; RETURN l_key_list; END GET_KEY_COLUMN_LIST; ------------------------------------------------------------------------------- -- 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. FUNCTION GET_NONKEY_COLUMN_LIST ( p_table_name IN VARCHAR2, p_metadata_id IN RAW, p_whichOnFlag IN NUMBER ) RETURN VARCHAR2_TABLE IS l_nonkey_list VARCHAR2_TABLE := NULL; BEGIN BEGIN SELECT name BULK COLLECT INTO l_nonkey_list FROM mgmt_ecm_snapshot_md_columns WHERE (table_name = p_table_name) AND (metadata_id = p_metadata_id) AND (is_key = 'N') AND ((p_whichOnFlag=UI_ON_C AND UI_ON = 'Y') OR (p_whichOnFlag=COMPARE_ON_C AND COMPARE_ON = 'Y') OR (p_whichOnFlag=HISTORY_ON_C AND HISTORY_ON = 'Y')); -- ignore when no non-key columns found EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; RETURN l_nonkey_list; END GET_NONKEY_COLUMN_LIST; ------------------------------------------------------------------------ PROCEDURE GET_STRINGS_FOR_CRQT( p_key_list VARCHAR2_TABLE, p_nonkey_list VARCHAR2_TABLE, p_crqt1 out VARCHAR2, p_crqt2 out VARCHAR2, p_crqt3 out VARCHAR2, p_crqt4 out VARCHAR2, p_crqt9 out VARCHAR2, p_crqtA out VARCHAR2 ) IS BEGIN IF p_key_list IS NOT NULL AND p_key_list.COUNT > 0 THEN -- Build strings for CRQT1,3 and 9; FOR i IN p_key_list.FIRST..p_key_list.LAST LOOP -- CRQT1 -- "key1".value as "key1" p_crqt1 := p_crqt1 || ', "' || EM_CHECK.SIMPLE_SQL_NAME(p_key_list(i)) || '".value as "' || EM_CHECK.SIMPLE_SQL_NAME(p_key_list(i)) || '"'; -- CRQT3 -- MGMT_DELTA_COMP_KEY_COLS "key1" p_crqt3 := p_crqt3 || ', MGMT_DELTA_COMP_KEY_COLS "' || EM_CHECK.SIMPLE_SQL_NAME(p_key_list(i)) || '"'; -- CRQT9 -- AND "key1".KEY_GUID = cd.KEY_GUID -- AND "key1".NAME = 'key1' p_crqt9 := p_crqt9 || ' AND "' || EM_CHECK.SIMPLE_SQL_NAME(p_key_list(i)) || '".KEY_GUID = cd.KEY_GUID AND "' || EM_CHECK.SIMPLE_SQL_NAME(p_key_list(i)) || '".NAME = ''' || EM_CHECK.SIMPLE_SQL_NAME(p_key_list(i)) || ''''; END LOOP; END IF; IF p_nonkey_list IS NOT NULL AND p_nonkey_list.COUNT > 0 THEN -- Build strings for CRQT2,4 and A; FOR i IN p_nonkey_list.FIRST..p_nonkey_list.LAST LOOP -- CRQT2 -- "nonkey1".status as "nonkey1_s", -- "nonkey1".left_value as "nonkey1_l", -- "nonkey1".right_value as "nonkey1_r" p_crqt2 := p_crqt2 || ', "' || EM_CHECK.SIMPLE_SQL_NAME(p_nonkey_list(i)) || '".status as "' || SUBSTR(EM_CHECK.SIMPLE_SQL_NAME(p_nonkey_list(i)),1,28) || '_s", "' || EM_CHECK.SIMPLE_SQL_NAME(p_nonkey_list(i)) || '".left_value as "' || SUBSTR(EM_CHECK.SIMPLE_SQL_NAME(p_nonkey_list(i)),1,28) || '_l", "' || EM_CHECK.SIMPLE_SQL_NAME(p_nonkey_list(i)) || '".right_value as "' || SUBSTR(EM_CHECK.SIMPLE_SQL_NAME(p_nonkey_list(i)),1,28) || '_r"'; -- CRQT4 -- MGMT_DELTA_COMP_DELTA_DETAILS nonkey1, p_crqt4 := p_crqt4 || ', MGMT_DELTA_COMP_DELTA_DETAILS "' || EM_CHECK.SIMPLE_SQL_NAME(p_nonkey_list(i)) || '"'; -- CRQTA -- AND "nonkey1".KEY_GUID = cd.KEY_GUID -- AND "nonkey1".NAME = 'nonkey1' p_crqtA := p_crqtA || ' AND "' || EM_CHECK.SIMPLE_SQL_NAME(p_nonkey_list(i)) || '".KEY_GUID (+) = cd.KEY_GUID AND "' || EM_CHECK.SIMPLE_SQL_NAME(p_nonkey_list(i)) || '".NAME (+) = ''' || EM_CHECK.SIMPLE_SQL_NAME(p_nonkey_list(i)) || ''''; END LOOP; END IF; END GET_STRINGS_FOR_CRQT; ------------------------------------------------------------------------ FUNCTION PREPARE_COMPARE_RESULTS_QUERY( p_snapshot_type VARCHAR2, p_target_type VARCHAR2, p_table_name VARCHAR2) RETURN VARCHAR2 IS l_key_list VARCHAR2_TABLE; l_nonkey_list VARCHAR2_TABLE; l_crqt1 VARCHAR2(8000) := ''; l_crqt2 VARCHAR2(8000) := ''; l_crqt3 VARCHAR2(8000) := ''; l_crqt4 VARCHAR2(8000) := ''; l_crqt9 VARCHAR2(8000) := ''; l_crqtA VARCHAR2(8000) := ''; qtext VARCHAR2(32000); l_metadata_id RAW(16); l_enquoted_snapshot_type VARCHAR2(64) := dbms_assert.enquote_literal(replace(p_snapshot_type, '''', '''''')); l_enquoted_target_type VARCHAR2(64) := dbms_assert.enquote_literal(replace(p_target_type, '''', '''''')); BEGIN -- Given the type, lookup the details of the snapshot and use it to formulate an -- appropriate query. -- Get a list of key columns and nonkey columns for this table in this snapshot -- First get the presentation metadata id for this snapshot type SELECT metadata_id INTO l_metadata_id FROM MGMT_ECM_SNAPSHOT_METADATA WHERE snapshot_type = p_snapshot_type AND target_type = p_target_type AND kind = 'P'; -- 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. l_key_list := GET_KEY_COLUMN_LIST ( p_table_name, l_metadata_id, COMPARE_ON_C ); l_nonkey_list := GET_NONKEY_COLUMN_LIST ( p_table_name, l_metadata_id, COMPARE_ON_C ); qtext := COMPARE_RESULTS_QUERY_TEMPLATE; GET_STRINGS_FOR_CRQT( l_key_list, l_nonkey_list, l_crqt1, l_crqt2, l_crqt3, l_crqt4, l_crqt9, l_crqtA ); qtext := REPLACE( qtext, CRQT1, l_crqt1 ); qtext := REPLACE( qtext, CRQT2, l_crqt2 ); qtext := REPLACE( qtext, CRQT3, l_crqt3 ); qtext := REPLACE( qtext, CRQT4, l_crqt4 ); qtext := REPLACE( qtext, CRQT6, l_enquoted_snapshot_type ); qtext := REPLACE( qtext, CRQT7, l_enquoted_target_type ); qtext := REPLACE( qtext, CRQT9, l_crqt9 ); qtext := REPLACE( qtext, CRQTA, l_crqtA ); RETURN qtext; END PREPARE_COMPARE_RESULTS_QUERY; ------------------------------------------------------------------------ PROCEDURE GET_STRINGS_FOR_TC( p_key_list VARCHAR2_TABLE, p_diff_list MGMT_DELTA_VALUES, p_nameValues out VARCHAR2, p_deltaValues out VARCHAR2, p_keyColsTabs out VARCHAR2, p_keyColsWhere out VARCHAR2 ) IS comma VARCHAR2(3) := ''; BEGIN p_nameValues := ''; p_deltaValues := ''; p_keyColsTabs := ''; p_keyColsWhere := ''; IF p_key_list IS NOT NULL AND p_key_list.COUNT > 0 THEN FOR j IN p_key_list.FIRST..p_key_list.LAST LOOP -- MGMT_NAME_VALUE( 'SOFTWARE_NAME', ''foobar', 'S' ), -- MGMT_NAME_VALUE( 'VENDOR_NAME', ''veritas', 'S' ), -- MGMT_NAME_VALUE( 'INSTALLED_LOCATION', '/here', 'S' ), -- MGMT_NAME_VALUE( 'SOFTWARE_VERSION', '3.1', 'S' ) p_nameValues := p_nameValues || comma || 'MGMT_NAME_VALUE( ''' || p_key_list(j) || ''', parent_table.' || p_key_list(j) || ' , ''S'' )'; -- Key columns select list. comma separated, one for each key column of the parent -- numbered k1, k2...kn. E.G. -- mgmt_delta_comp_key_cols k1, mgmt_delta_comp_key_cols k2, -- mgmt_delta_comp_key_cols k3, mgmt_delta_comp_key_cols k4 p_keyColsTabs := p_keyColsTabs || comma || ' mgmt_delta_comp_key_cols k' || j; -- k1.key_guid = key_guid.key_guid -- and k1.name = 'SOFTWARE_NAME' -- and k2.key_guid = key_guid.key_guid -- and k2.name = 'VENDOR_NAME' -- and k3.key_guid = key_guid.key_guid -- and k3.name = 'INSTALLED_LOCATION' -- and k4.key_guid = key_guid.key_guid -- and k4.name = 'SOFTWARE_VERSION' p_keyColsWhere := p_keyColsWhere || 'AND k' || j || '.key_guid = d.key_guid and k' || j || '.name = ''' || p_key_list(j) || ''' and k1.value = parent_table."' || p_key_list(j) || '"'; comma := ', '; END LOOP; END IF; comma := ''; IF p_diff_list IS NOT NULL AND p_diff_list.COUNT > 0 THEN FOR j IN p_diff_list.FIRST..p_diff_list.LAST LOOP -- MGMT_DELTA_VALUE( 'DESCRIPTION', 'thid thing', 'thid thing', 'SAME', 'S') p_deltaValues := p_deltaValues || comma || 'MGMT_DELTA_VALUE( ''' || p_diff_list(j).name || ''', parent_table.' || p_diff_list(j).name || ' , parent_table.' || p_diff_list(j).name || ' , ''SAME'', ''S'' )'; comma := ', '; END LOOP; END IF; END GET_STRINGS_FOR_TC; ------------------------------------------------------------------------ -- Utility function to lookup table flags in list. FUNCTION GET_FLAGS_VALUE( p_tableName VARCHAR2, p_tableFlagsOverrides TABLE_FLAGS_LIST, p_tableFlagsDefault SMALLINT ) RETURN SMALLINT IS l_flags SMALLINT := p_tableFlagsDefault; BEGIN IF p_tableFlagsOverrides IS NOT NULL THEN FOR i IN p_tableFlagsOverrides.FIRST..p_tableFlagsOverrides.LAST LOOP IF p_tableFlagsOverrides(i).TABLE_NAME = p_tableName THEN l_flags := p_tableFlagsOverrides(i).FLAGS; EXIT; END IF; END LOOP; END IF; return l_flags; END GET_FLAGS_VALUE; ------------------------------------------------------------------------ -- 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 IS l_compare_queries MGMT_DELTA_QUERIES; l_table_names VARCHAR2_TABLE; l_table_srf VARCHAR2_TABLE; -- single row flags l_delta_query MGMT_DELTA_QUERY; l_key_alias_list VARCHAR2_TABLE; l_qual_key_list VARCHAR2_TABLE; l_attribute_list MGMT_DELTA_VALUES; qtext VARCHAR2(4000); tempstr VARCHAR2(4000); l_query_tab_list VARCHAR2_TABLE; BEGIN -- Given the type, lookup the details of the snapshot and use it to formulate -- appropriate queries. Use the left and right guid as the id columns and the various -- keys as the match columns. -- now get a list of (compare) tables in this snapshot. -- BULK COLLECT IF p_whichOn = HISTORY_ON_C THEN SELECT name, is_single_row BULK COLLECT INTO l_table_names, l_table_srf FROM MGMT_ECM_SNAPSHOT_MD_TABLES WHERE metadata_id = p_metadata_id AND HISTORY_ON = 'Y'; ELSE SELECT name, is_single_row BULK COLLECT INTO l_table_names, l_table_srf FROM MGMT_ECM_SNAPSHOT_MD_TABLES WHERE metadata_id = p_metadata_id AND COMPARE_ON = 'Y'; END IF; l_compare_queries := MGMT_DELTA_QUERIES(); -- For each table, add a delta query to the list. FOR i IN l_table_names.FIRST..l_table_names.LAST LOOP -- We need to construct a MGMT_DELTA_QUERY -- Query Text -- Collection type (table name) -- List of Keys (VARCHAR2_TABLE) -- Attribute List (MGMT_DELTA_VALUES) -- Id List (MGMT_DELTA_VALUES) -- flags (SMALLINT) l_delta_query := MGMT_DELTA_QUERY(null, null, null, null, null, null, null); l_delta_query.collection_type := l_table_names(i); -- 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. l_qual_key_list := GET_KEY_COLUMN_LIST ( l_table_names(i), p_metadata_id, p_whichOn ); l_delta_query.key_list := l_qual_key_list; -- The 'MGMT_DELTA.SAME_C' indicates that we want the same columns on diff rows. IF p_whichOn = HISTORY_ON_C THEN SELECT MGMT_DELTA_VALUE( name, '', MGMT_DELTA.SAME_C, '', type ) BULK COLLECT INTO l_attribute_list FROM mgmt_ecm_snapshot_md_columns WHERE table_name = l_table_names(i) AND metadata_id = p_metadata_id AND is_key = 'N' AND history_on = 'Y'; ELSE SELECT MGMT_DELTA_VALUE( name, '', MGMT_DELTA.SAME_C, '', type ) BULK COLLECT INTO l_attribute_list FROM mgmt_ecm_snapshot_md_columns WHERE table_name = l_table_names(i) AND metadata_id = p_metadata_id AND is_key = 'N' AND compare_on = 'Y'; END IF; l_delta_query.attribute_list := l_attribute_list; l_delta_query.id_list := MGMT_DELTA_VALUES( MGMT_DELTA_VALUE( 'ECM_SNAPSHOT_ID', p_left_guid, p_right_guid, '', MGMT_DELTA.RAW_TYPE_C )); l_delta_query.flags := GET_FLAGS_VALUE( l_table_names(i), p_table_flags_overrides, p_table_flags_default ); qtext := l_table_names(i); l_delta_query.first_query_text := qtext; l_compare_queries.EXTEND; l_compare_queries(l_compare_queries.LAST) := l_delta_query; END LOOP; RETURN l_compare_queries; END PREPARE_DELTA_QUERIES; -------------------------------------------------------------------------------- -- ADD_SAMES_FOR_DRILLINS -- The goal is to make sure all child tables have a corresponding parent row -- for each row that would be shown in the gui (either because it has a diff, -- or because one of its childrent had a diff). So, for each parent table, we -- look for rows whose children appear in the delta table, but no row yet appears -- for the parent row. We do this by first getting a list of no-leaf tables -- ordered by the depth in the tree, roots last. Then we look at the children -- of these tables, one level deep, for deltas. For deltas found, a SAME entry -- for the parent is entered, if no diff already existed. PROCEDURE ADD_SAMES_FOR_DRILLINS( p_metadata_id RAW, p_compare_results_id RAW, p_compare_queries MGMT_DELTA_QUERIES, p_snapshot_guid RAW ) IS TYPE t_tlist IS TABLE OF mgmt_ecm_snapshot_md_tables.name%TYPE; l_tlist t_tlist; TYPE t_levelList IS TABLE OF NUMBER; l_levelList t_levelList; l_compareQuery MGMT_DELTA_QUERY; l_table_name VARCHAR(30); l_parent_sames_query VARCHAR2(21000); l_nameValues VARCHAR2(4000); l_deltaValues VARCHAR2(4000); l_keyColsTabs VARCHAR2(4000); l_keyColsWhere VARCHAR2(8000); tempsql VARCHAR2(32000); TYPE C1Type IS REF CURSOR; -- define weak REF CURSOR type c1 C1Type; k_list MGMT_NAME_VALUES; d_list MGMT_DELTA_VALUES; l_deltaRecorder MGMT_DELTA_ENTRY_RECORDER; l_deltaEntry MGMT_DELTA_ENTRY_OBJ; BEGIN -- First get a list of all non-leaf tables. Order them by the depth in the -- tree so that as we process each, we only need to look at its children, one -- level down. SELECT UNIQUE t.name, level BULK COLLECT INTO l_tlist,l_levelList FROM mgmt_ecm_snapshot_md_tables p, mgmt_ecm_snapshot_md_tables t WHERE p.metadata_id = p_metadata_id and t.metadata_id = p_metadata_id and p.parent_table_name = t.name START WITH t.parent_table_name IS NULL CONNECT BY PRIOR t.name = t.parent_table_name ORDER BY level DESC; -- Now for each IF l_tlist IS NOT NULL AND l_tlist.COUNT > 0 THEN FOR i IN l_tlist.FIRST..l_tlist.LAST LOOP FOR j IN p_compare_queries.FIRST..p_compare_queries.LAST LOOP IF p_compare_queries(i).collection_type = l_tlist(i) THEN l_compareQuery := p_compare_queries(i); EXIT; END IF; END LOOP; IF l_compareQuery.key_list IS NOT NULL AND l_compareQuery.key_list.COUNT > 0 THEN -- Prepare the query that gets the rows that we need to add -- sames for. l_parent_sames_query := FIXUP_QUERY_TEMPLATE; GET_STRINGS_FOR_TC( l_compareQuery.key_list, l_compareQuery.attribute_list, l_nameValues, l_deltaValues, l_keyColsTabs, l_keyColsWhere ); l_parent_sames_query := REPLACE( l_parent_sames_query, K_NAME_VALUES, l_nameValues ); l_parent_sames_query := REPLACE( l_parent_sames_query, K_DELTA_VALUES, l_deltaValues ); l_parent_sames_query := REPLACE( l_parent_sames_query, K_KEY_COLS_TABS, l_keyColsTabs ); l_parent_sames_query := REPLACE( l_parent_sames_query, K_KEY_COLS_WHERE, l_keyColsWhere ); l_parent_sames_query := REPLACE( l_parent_sames_query, K_PARENT_TABLE_NAME, em_check.sql_object_name(l_compareQuery.collection_type) ); l_deltaRecorder := MGMT_DELTA_ENTRY_RECORDER( sysdate, p_compare_results_id ); -- Run the query. OPEN c1 FOR l_parent_sames_query USING p_compare_results_id, p_snapshot_guid, p_compare_results_id; -- For each row, add a same diff entry. LOOP FETCH c1 INTO k_list, d_list; EXIT WHEN c1%NOTFOUND; l_deltaEntry := MGMT_DELTA_ENTRY_OBJ( MGMT_DELTA.SAME_C, l_compareQuery.collection_type, k_list, d_list ); l_deltaRecorder.RECORD_DELTA( l_deltaEntry ); END LOOP; END IF; END LOOP; END IF; END ADD_SAMES_FOR_DRILLINS; /*---------------------------------------------------------------------- The function populate_comparison_properties populates the comparion properties data structure that needs to be saved as part of the comparison of generic 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_gen_snapshot lhs, mgmt_ecm_gen_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_gen_snapshot lhs, mgmt_ecm_gen_snapshot rhs where lhs.snapshot_guid = lhs_guid and rhs.snapshot_guid = rhs_guid union select HOST_NAME_PROP property_key, lht.host_name lhs, rht.host_name rhs from mgmt_ecm_gen_snapshot lhs, mgmt_ecm_gen_snapshot rhs, mgmt_targets lht, mgmt_targets rht where lhs.snapshot_guid = lhs_guid and rhs.snapshot_guid = rhs_guid and lhs.target_guid = lht.target_guid and rhs.target_guid = rht.target_guid union select IS_CURRENT_PROP property_key, lhs.is_current lhs, rhs.is_current rhs from mgmt_ecm_gen_snapshot lhs, mgmt_ecm_gen_snapshot rhs where lhs.snapshot_guid = lhs_guid and rhs.snapshot_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_gen_snapshot lhs, mgmt_ecm_gen_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_gen_snapshot lhs, mgmt_ecm_gen_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(+); 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; -------------------------------------------------------------------------------- -- 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 IS l_compare_queries MGMT_DELTA_QUERIES; l_compare_results_id RAW(16); l_metadata_id RAW(16); BEGIN -- First get the presentation metadata id for this snapshot type SELECT metadata_id INTO l_metadata_id FROM MGMT_ECM_SNAPSHOT_METADATA WHERE snapshot_type = p_snapshot_type AND target_type = p_target_type AND kind = 'P'; l_compare_queries := PREPARE_DELTA_QUERIES( p_left_guid, p_right_guid, COMPARE_ON_C, p_table_flags_overrides, p_table_flags_default, l_metadata_id ); l_compare_results_id := MGMT_DELTA.DO_DIFF_QUERIES_AND_SAVE( l_compare_queries, p_snapshot_type, p_target_type, p_properties, p_session_id); ADD_SAMES_FOR_DRILLINS( l_metadata_id, l_compare_results_id, l_compare_queries, p_left_guid ); RETURN l_compare_results_id; END COMPARE_AND_SAVE; /*---------------------------------------------------------------------- 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 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; BEGIN -- in case of generic configs, it may be needed to set the table flags from java side -- for cases in which we need -- to get the "SAME" rows as well. -- for now mgmt_delta.raturn_all for all tables. --l_table_flags1.table_name := OS_SUMMARY_VIEW_NAME; --l_table_flags1.flags := mgmt_delta.return_all; l_comparison_properties := populate_comparison_properties( p_lhs_guid, p_rhs_guid); l_comparison_guid := compare_and_save( p_snapshot_type, p_target_type, p_lhs_guid, p_rhs_guid, p_session_id, l_comparison_properties, NULL,MGMT_DELTA.RETURN_ALL); return l_comparison_guid; END; ------------------------------------------------------------------------------- -- COMPARE_AND_SAVE_JOB -- This is a wrap around function to compare_gen_conf_and_save -- As it is 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 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_gen_snapshot s where s.snapshot_guid = p_right_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_right_guid,p_session_id ); return compare_gen_conf_and_save( p_snapshot_type, p_target_type, p_left_guid, p_right_guid, p_session_id); END COMPARE_AND_SAVE_JOB; -- Callback procedure for cleaning up of the saved comparisons, associated with -- the configuration comparison job being deleted. PROCEDURE ON_COMP_JOB_DELETE_CALLBACK(p_reason NUMBER, p_status NUMBER, p_job_id RAW, p_execution_id RAW) IS BEGIN -- dummy table to test the callback is being called --l_job_name MGMT_JOB.job_name%type; -- SELECT job_name INTO l_job_name FROM mgmt_job WHERE job_id=p_job_id; -- INSERT INTO COMP_CALLBACK VALUES('compare_job_deleted_callback called for ' || l_job_name); DELETE FROM mgmt_comp_snapshot_to_step_map WHERE execution_id = p_execution_id; DELETE FROM mgmt_delta_saved_comparison WHERE delta_comp_guid IN ( SELECT delta_comp_guid FROM mgmt_comp_result_to_job_map map where map.execution_id = p_execution_id ); COMMIT; END; /*---------------------------------------------------------------------- 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 ) IS BEGIN open p_cursor for select delta_comp_guid, lhs_config_guid,rhs_config_guid, lhs_target_type,lhs_target_name,rhs_target_name,lhs_host_name,rhs_host_name, lhs_collection_time,rhs_collection_time, /* The following decode computes overall result (configuration 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 result contain an 'ERROR' string, then we show an error at configuration comprison level - else show the 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 ( instr ( result, 'ERROR'), 0, decode ( instr(result, 'COW_RESULT_DIFFERENT'), 0,'COW_RESULT_SAME', 'COW_RESULT_DIFFERENT' ), 'ERROR_IN_COMPARISON' ) ) result,null as compare_url,null as view_url from ( select delta_comp_guid, lhs_config_guid,rhs_config_guid, lhs_target_type,lhs_target_name,rhs_target_name, lhs_host_name,rhs_host_name, lhs_collection_time,rhs_collection_time, step_status,result from ( with common_columns as ( select map.delta_comp_guid delta_comp_guid, a.lhs_config_guid lhs_config_guid, map.rhs_snapshot_guid rhs_config_guid, a.step_status step_status from ( select h.execution_id, h.step_id, jp.scalar_value lhs_config_guid, jst.rhs_snapshot_guid rhs_config_guid, h.step_status from mgmt_job_history h, mgmt_job_parameter jp, mgmt_job_target jt, mgmt_comp_snapshot_to_step_map jst where h.execution_id = p_execution_id and jp.execution_id = h.execution_id and jp.parameter_name = 'firstConfigGuid' and h.step_name = 'CompareTwoConfigs' and h.execution_id = jt.execution_id and h.iterate_param_index = jt.target_index and h.step_id=jst.step_id ) a, mgmt_comp_result_to_job_map map where a.execution_id = map.execution_id (+) and a.rhs_config_guid = map.rhs_snapshot_guid (+) ) select c.delta_comp_guid, c.lhs_config_guid,c.rhs_config_guid, tType.left_value lhs_target_type, tName.left_value lhs_target_name, tName.right_value rhs_target_name, hName.left_value lhs_host_name, hName.right_value rhs_host_name, tTime.left_value lhs_collection_time, tTime.right_value rhs_collection_time, 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' ) result from common_columns c, mgmt_delta_comp_summaries s, mgmt_delta_comp_properties tName, mgmt_delta_comp_properties hName, mgmt_delta_comp_properties tType, mgmt_delta_comp_properties tTime, table(s.errors)(+) e where c.delta_comp_guid = s.delta_comp_guid(+) and tName.delta_comp_guid = c.delta_comp_guid and tName.name = TARGET_NAME_PROP and hName.delta_comp_guid = c.delta_comp_guid and hName.name = HOST_NAME_PROP and tType.delta_comp_guid = c.delta_comp_guid and tType.name = TARGET_TYPE_PROP and tTime.delta_comp_guid = c.delta_comp_guid and tTime.name = TARGET_TIME_STAMP_PROP group by c.delta_comp_guid, c.lhs_config_guid,c.rhs_config_guid, tType.left_value, tName.left_value, tName.right_value, hName.left_value, hName.right_value, tTime.left_value, tTime.right_value, c.step_status )diff_qry group by delta_comp_guid, lhs_config_guid,rhs_config_guid, lhs_target_type,lhs_target_name,rhs_target_name,lhs_host_name,rhs_host_name, lhs_collection_time,rhs_collection_time, step_status, result )diff_qy1 ; /* to return the view_url and compare_url for given snapshot_type and target_type required to genereate view URLs for viewing config and comparison results*/ select distinct view_url into p_view_url from mgmt_job_parameter jp1, mgmt_job_parameter jp2, mgmt_ecm_snapshot_metadata m where jp1.execution_id = p_execution_id and jp1.PARAMETER_NAME='snapshotType' and m.SNAPSHOT_TYPE=jp1.scalar_value and jp2.execution_id = p_execution_id and jp2.parameter_name='targetType' and m.target_type = jp2.scalar_value and m.KIND='P'; select distinct compare_url into p_compare_url from mgmt_job_parameter jp1, mgmt_job_parameter jp2, mgmt_ecm_snapshot_metadata m where jp1.execution_id = p_execution_id and jp1.PARAMETER_NAME='snapshotType' and m.SNAPSHOT_TYPE=jp1.scalar_value and jp2.execution_id = p_execution_id and jp2.parameter_name='targetType' and m.target_type = jp2.scalar_value and m.KIND='P'; END; -------------------------------------------------------------------------------- -- 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 ) IS l_compare_queries MGMT_DELTA_QUERIES; l_metadata_id RAW(16); BEGIN -- First get the presentation metadata id for this snapshot type SELECT metadata_id INTO l_metadata_id FROM MGMT_ECM_SNAPSHOT_METADATA WHERE snapshot_type = p_snapshot_type AND target_type = p_target_type AND kind = 'P'; l_compare_queries := PREPARE_DELTA_QUERIES( p_left_guid, p_right_guid, COMPARE_ON_C, p_table_flags_overrides, p_table_flags_default, l_metadata_id ); MGMT_DELTA.DO_DIFF_QUERIES( l_compare_queries, p_deRef, p_dsRef ); END COMPARE_SNAPSHOTS; -------------------------------------------------------------------------------- -- DO_AND_SAVE_HISTORY -- A utility procedure 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 ) IS n_snaptime DATE := NULL; o_snaptime DATE := NULL; n_target_name VARCHAR2(256) := NULL; o_target_name VARCHAR2(256) := NULL; deltaGuid RAW(16); l_metadata_id RAW(16); BEGIN -- First get the presentation metadata id for this snapshot type SELECT metadata_id INTO l_metadata_id FROM MGMT_ECM_SNAPSHOT_METADATA WHERE snapshot_type = p_snapshot_type AND target_type = p_target_type AND 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_GEN_SNAPSHOT n WHERE n.SNAPSHOT_GUID = p_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_GEN_SNAPSHOT o WHERE o.SNAPSHOT_GUID = p_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. deltaGuid := MGMT_DELTA.START_DELTA(p_new_guid, n_snaptime, n_target_name, p_old_guid, o_snaptime, o_target_name, p_target_type, p_snapshot_type, 'HISTORY'); -- Return is old snapshot doesn't exist. IF o_snaptime IS NULL THEN RETURN; END IF; MGMT_DELTA.DO_HISTORY_DIFFS( PREPARE_DELTA_QUERIES( p_new_guid, p_old_guid, HISTORY_ON_C, NULL, MGMT_DELTA.RETURN_DIFFS, l_metadata_id ), n_snaptime ); END DO_AND_SAVE_HISTORY; -------------------------------------------------------------------------------- -- 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 ) IS BEGIN DELETE FROM MGMT_DELTA_SAVED_COMPARISON WHERE DELTA_COMP_GUID = p_comparison_guid; END DELETE_SAVED_COMPARISON; ------------------------------------------------------------------------------------ -- PURGE_HISTORY -- -- 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 IS BEGIN RETURN MGMT_DELTA.PURGE_HISTORY( p_numberOfDays, p_snapshot_type, p_target_type, p_target_name ); END PURGE_HISTORY; ----------------------------------------------------------------------------- -- 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. -- FUNCTION GET_COMPARISON_RESULTS_QUERY( p_snapshot_type VARCHAR2, p_target_type VARCHAR2, p_table_name VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN PREPARE_COMPARE_RESULTS_QUERY( p_snapshot_type, p_target_type, p_table_name); END GET_COMPARISON_RESULTS_QUERY; ----------------------------------------------------------------------------- -- 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 ) IS l_comp_results_query VARCHAR2(21000); l_snapshot_type VARCHAR2(64); l_target_type VARCHAR2(64); BEGIN BEGIN -- Get the snapshot_type and target_type for the saved comparison. SELECT c.COMPARISON_TYPE, c.TARGET_TYPE INTO l_snapshot_type, l_target_type FROM MGMT_DELTA_SAVED_COMPARISON c WHERE c.DELTA_COMP_GUID = p_comparison_guid; EXCEPTION -- Return if the comparison Guid doesn't exist. WHEN NO_DATA_FOUND THEN RETURN; END; l_comp_results_query := GET_COMPARISON_RESULTS_QUERY(l_snapshot_type, l_target_type, p_table_name); IF p_keyColumn_name IS NOT NULL AND p_keyColumn_value IS NOT NULL THEN BEGIN l_comp_results_query:=l_comp_results_query||' AND '|| EM_CHECK.SIMPLE_SQL_NAME(p_keyColumn_name)||'.value='|| EM_CHECK.ENQUOTE_LITERAL(p_keyColumn_value); EXCEPTION --WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'get_generic_comparison_results: Invalid Value!'); END; END IF; --Fix for SQL Injection bug 7010910 open p_cursor for l_comp_results_query using p_comparison_guid, p_table_name; END get_generic_comparison_results; ----------------------------------------------------------------------------- -- ** 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 ) IS l_comp_results_query VARCHAR2(21000); l_snapshot_type VARCHAR2(64); l_target_type VARCHAR2(64); BEGIN BEGIN -- Get the snapshot_type and target_type for the saved comparison. SELECT c.COMPARISON_TYPE, c.TARGET_TYPE INTO l_snapshot_type, l_target_type FROM MGMT_DELTA_SAVED_COMPARISON c WHERE c.DELTA_COMP_GUID = p_comparison_guid; EXCEPTION -- Return if the comparison Guid doesn't exist. WHEN NO_DATA_FOUND THEN RETURN; END; l_comp_results_query := GET_COMPARISON_RESULTS_QUERY(l_snapshot_type, l_target_type, p_table_name); IF p_keys IS NOT NULL THEN BEGIN FOR i IN p_keys.FIRST..p_keys.LAST LOOP l_comp_results_query:=l_comp_results_query||' AND '|| EM_CHECK.SIMPLE_SQL_NAME(p_keys(i).keyname)||'.value='|| EM_CHECK.ENQUOTE_LITERAL(p_keys(i).keyval); END LOOP; EXCEPTION --WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'get_generic_comparison_with_keys: Invalid Value!'); END; END IF; --Fix for SQL Injection bug 7010910 open p_cursor for l_comp_results_query using p_comparison_guid, p_table_name; END get_generic_comp_with_keys; END MGMT_ECM_SNAPSHOT_PKG; / show errors;