Rem Rem $Header: ecm_gendiff_pkgbody.sql 18-jan-2006.08:56:08 jochen Exp $ Rem Rem ecm_gendiff_pkgbody.sql Rem Rem Copyright (c) 2005, 2006, Oracle. All rights reserved. Rem Rem NAME Rem ecm_gendiff_pkgbody.sql Rem Rem DESCRIPTION Rem Package to provide data to generic snapshot comparison viewer. Rem Gets diff data for child tables and descendants of an ECM table Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem jochen 01/18/06 - XbranchMerge jochen_bug-4917117 from main Rem jochen 01/17/06 - Fix sql injection in get_child_diffs Rem jochen 01/05/06 - XbranchMerge jochen_bug-4917117 from main Rem jochen 01/05/06 - EM_CHECK table name Rem lappanmu 12/27/05 - XbranchMerge lappanmu_generic_ui_new_fix from Rem main Rem jochen 12/21/05 - Code review Rem lappanmu 12/11/05 - Rem jochen 11/09/05 - getDiffs for generic comparison viewer Rem jochen 11/09/05 - Created Rem SET ECHO ON SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 CREATE OR REPLACE PACKAGE BODY ecm_gendiff IS -------------------------------------------------------------------------- -- Function: has_diffs -- -- Quick way to determine if the comparison of the list of tables -- given resulted in any different values. -- -- Return 1 if the any of the tables in the given list have -- any differences for the given comparision guid -- 0 if all comparison results for the given tables are the same -------------------------------------------------------------------------- FUNCTION has_diffs(p_compGuid RAW, p_tables ecm_gendiff_tablename_table) RETURN INTEGER IS -- Query template - Return a row if any of the input table names -- have a comparison result that's different l_sqlTemplate CONSTANT VARCHAR2(1000) := 'SELECT 1 FROM DUAL WHERE EXISTS( SELECT 1 FROM mgmt_delta_comp_summaries WHERE delta_comp_guid = :compGuid AND () AND (different_count > 0 OR left_count > 0 OR right_count > 0))'; -- Add OR clause for all the tables in the list l_tableNameClause VARCHAR2(4000) := null; -- Complete sql query for determining diffs l_sql VARCHAR2(21000) := l_sqlTemplate; l_cursorName INTEGER; l_numResultRows INTEGER; -- separator for forming query l_separator VARCHAR2(3) := ''; BEGIN -- -- Add an OR clause for each table -- FOR i in p_tables.FIRST..p_tables.LAST LOOP l_tableNameClause := l_tableNameClause || l_separator || ' collection_type = :table' || i; l_separator := ' OR'; END LOOP; l_sql := REPLACE(l_sqlTemplate, '', l_tableNameClause); -- -- Bind the descendent table names and execute -- l_cursorName := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(l_cursorName, l_sql, DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(l_cursorName, ':compGuid', p_compGuid); FOR i IN p_tables.FIRST..p_tables.LAST LOOP DBMS_SQL.BIND_VARIABLE(l_cursorName, ':table' || i, p_tables(i)); END LOOP; l_numResultRows := DBMS_SQL.EXECUTE_AND_FETCH(l_cursorName); DBMS_SQL.CLOSE_CURSOR(l_cursorName); -- -- If any rows were returned, one of the tables has diffs. -- IF l_numResultRows > 0 THEN RETURN 1; ELSE RETURN 0; END IF; EXCEPTION WHEN OTHERS THEN IF DBMS_SQL.IS_OPEN(l_cursorName) THEN DBMS_SQL.CLOSE_CURSOR(l_cursorName); END IF; RAISE; END has_diffs; -------------------------------------------------------------------------- -- Function: getOneRowDiff -- -- Bind the requested row of keys to the given diff query and return -- true if there were diffs for the descendant tables and the row of keys -- -- PARAMS -- p_keys Set of all keys and keynames -- p_rowNum The row number of the keys to check -- p_cursorName The dbms_sql cursor name -- -------------------------------------------------------------------------- FUNCTION getOneRowDiff(p_keys ecm_gendiff_keys_table, p_rowNum SMALLINT, p_cursorName INTEGER) RETURN BOOLEAN IS -- the number of rows returned from the query - if > 0, the -- comparison had diffs l_numResultRows INTEGER; BEGIN -- -- Bind the table name and key values for the query -- IF p_keys IS NOT NULL THEN FOR k IN p_keys.FIRST..p_keys.LAST LOOP DBMS_SQL.BIND_VARIABLE(p_cursorName, ':keyname' || k, p_keys(k).keyname); DBMS_SQL.BIND_VARIABLE(p_cursorName, ':keyval' || k, p_keys(k).vals(p_rowNum)); END LOOP; END IF; l_numResultRows := DBMS_SQL.EXECUTE_AND_FETCH(p_cursorName); RETURN (l_numResultRows > 0); END getOneRowDiff; -- -- procedure print_long ( p_str in varchar2 ) -- is -- l_str long := p_str; -- begin -- loop -- exit when l_str is null; -- dbms_output.put_line( substr( l_str, 1, 250 ) ); -- l_str := substr( l_str, 251 ); -- end loop; -- end; ------------------------------------------------------------------------ -- FUNCTION get_child_diffs -- -- In the UI, we are trying to display the following table with -- comparison results for two targets. -- -- keyname1 | keyname2 | key |Descendant tables -- -------------|--------------|--------|------------------ -- row1_keyval1 | row1_keyval2 | ... | = -- -------------|--------------|--------|------------------ -- row2_keyval1 | row2_keyval2 | ... | != -- -------------|--------------|--------|------------------ -- -- This table shows rows of keys for an ECM table. Assume that -- there are two targets that have been compared. For each row of -- key values, we want to determine whether the table's descedant tables -- have data values that are different in the comparison, or if the -- descendant table values are all the same for this row of keys. -- -- e.g. for a database configuration, the Tablespaces table has a child -- table called Datafiles. (In this example, the Tablespaces table only -- has one key column and one child table, but this function handles any -- number of key columns and descendant tables.) This function would return -- != and = values, meaning that for the two databases being compared, -- the SYSTEM tablespace has different datafiles, and the SYSAUX tablespace -- has the same datafiles. -- -- MGMT_ECM_DB_TABLESPACES | MGMT_ECM_DB_DATAFILES | -- ------------------------|-----------------------| -- SYSTEM | != | -- ------------------------|-----------------------| -- SYSAUX | = | -- ------------------------|-----------------------| -- -- PARAMETERS -- p_compGuid The delta_comp_guid -- p_keys A nested table. Each table element contains a record -- consisting of the the name of a key, and an array of key -- values for that key. -- For an array of key values, the values must be in the -- same order as the arrays for other keys. -- p_descs Nested table of descendant table names -- -- RETURNS -- Array of results for the table and its descendants -- The array is in the same order as the given key arrays -- -- Creates and executes a sql query that looks like this, for any number -- of keys: -- -- SELECT 1 FROM DUAL -- WHERE EXISTS( -- SELECT 1 FROM mgmt_delta_comparison_deltas d -- ,mgmt_delta_comp_key_cols k1,mgmt_delta_comp_key_cols k2,mgmt_delta_comp_key_cols k3 -- WHERE d.delta_comp_guid = :compGuid -- AND (d.collection_type='ECM_GENTEST_TABLE' OR d.collection_type='ECM_GENTEST_COLUMN') -- AND d.status != 'SAME' -- AND k1.delta_comp_guid = :compGuid -- AND k1.delta_comp_guid = d.delta_comp_guid -- AND d.collection_type = k1.collection_type -- -- AND k1.key_guid = d.key_guid -- AND k1.name = :keyname1 -- AND k1.value = :keyval1 AND k2.delta_comp_guid = :compGuid -- AND k2.delta_comp_guid = d.delta_comp_guid -- AND d.collection_type = k2.collection_type -- AND k2.key_guid = d.key_guid -- AND k2.name = :keyname2 -- AND k2.value = :keyval2 AND k3.delta_comp_guid = :compGuid -- AND k3.delta_comp_guid = d.delta_comp_guid -- AND d.collection_type = k3.collection_type -- AND k3.key_guid = d.key_guid -- AND k3.name = :keyname3 -- AND k3.value = :keyval3 ------------------------------------------------------------------------- FUNCTION get_child_diffs( p_compGuid RAW, p_keys ecm_gendiff_keys_table, p_descs ecm_gendiff_tablename_table) RETURN ecm_gendiff_res_array IS -- SQL template for main query l_sqlTemplate CONSTANT VARCHAR2(1000) := 'SELECT 1 FROM DUAL WHERE EXISTS( SELECT 1 FROM mgmt_delta_comparison_deltas d WHERE d.delta_comp_guid = :compGuid AND () AND d.status != ''SAME'' )'; -- SQL template for clause added for each key column l_oneKeyClauseTemplate CONSTANT VARCHAR2(500):= 'AND k.delta_comp_guid = :compGuid AND k.delta_comp_guid = d.delta_comp_guid AND d.collection_type = k.collection_type AND k.key_guid = d.key_guid AND k.name = :keyname AND k.value = :keyval '; l_diffs ecm_gendiff_res_array; -- DEBUG l_keyVals ecm_gendiff_keyvalue_array; -- The number of input UI rows l_numRows INTEGER; -- the number of keys in a row l_numKeys INTEGER := 0; -- sql query and clauses l_keyTablesClause VARCHAR2(3000); l_oneKeyClause VARCHAR2(1000); l_sql VARCHAR2(21000); l_allKeysClause VARCHAR2(21000); l_tablesClause VARCHAR2(3000); l_cursorName INTEGER; l_hasDiffs BOOLEAN; l_separator VARCHAR2(4) := ''; BEGIN -- -- debug - dump the keys -- -- IF p_keys IS NOT NULL --THEN -- FOR i IN p_keys.FIRST..p_keys.LAST -- LOOP -- dbms_output.put('keyname is ' || p_keys(i).keyname); -- l_keyVals := p_keys(i).vals; -- FOR j IN l_keyVals.FIRST..l_keyVals.LAST -- LOOP -- dbms_output.put_line(l_keyVals(j)); -- END LOOP; -- dbms_output.put_line('----------------------'); -- END LOOP; -- l_numRows := p_keys(1).vals.COUNT; --ELSE -- l_numRows := 1; --END IF; -- -- debug dump the list of tables -- --FOR j IN p_descs.FIRST..p_descs.LAST --LOOP -- dbms_output.put(p_descs(j) || ' '); --END LOOP; --dbms_output.put_line('----------------------'); -- -- Figure out how many rows of keys we have -- IF p_keys IS NOT NULL THEN l_numRows := p_keys(1).vals.COUNT; ELSE -- If there are no keys, then the parent is a single row table -- with no keys. We will just return one value indicating -- whether the child tables had any diffs. l_numRows := 1; END IF; -- -- Allocate an array to hold the results. We will return a result for -- each row of keys -- l_diffs := ecm_gendiff_res_array(); l_diffs.EXTEND(l_numRows); -- -- Form the clause to look at the list of descendant tables -- l_separator := ''; l_tablesClause := ''; FOR i IN p_descs.FIRST..p_descs.LAST LOOP l_tablesClause := l_tablesClause || l_separator || 'd.collection_type = :table' || i; l_separator := ' OR '; END LOOP; l_sql := REPLACE(l_sqlTemplate, '', l_tablesClause); -- -- Form the key table clause for the query -- and Form the query to check comparison result for -- one row of keys -- l_keyTablesClause := ''; l_allKeysClause := ''; IF p_keys IS NOT NULL THEN l_numKeys := p_keys.COUNT; FOR i IN p_keys.FIRST..p_keys.LAST LOOP l_keyTablesClause := l_keyTablesClause || ',mgmt_delta_comp_key_cols k' || i; -- Form the clause for one key value l_oneKeyClause := REPLACE(l_oneKeyClauseTemplate, '', i); l_allKeysClause := l_allKeysClause || l_oneKeyClause; END LOOP; END IF; l_sql := REPLACE(l_sql, '', l_keyTablesClause); l_sql := REPLACE(l_sql, '', l_allKeysClause); -- -- Parse the query -- l_cursorName := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(l_cursorName, l_sql, DBMS_SQL.NATIVE); -- debug print_long(l_sql); -- -- For each row of keys, get results for each child table. -- FOR i IN 1..l_numRows LOOP DBMS_SQL.BIND_VARIABLE(l_cursorName, ':compGuid', p_compGuid); FOR i IN p_descs.FIRST..p_descs.LAST LOOP DBMS_SQL.BIND_VARIABLE(l_cursorName, ':table' || i, p_descs(i)); END LOOP; -- -- Check if the child table has diffs -- l_hasDiffs := getOneRowDiff(p_keys, i, l_cursorName); -- -- Set the result in the diff list -- IF l_hasDiffs THEN l_diffs(i) := s_result_different; ELSE l_diffs(i) := s_result_same; END IF; END LOOP; DBMS_SQL.CLOSE_CURSOR(l_cursorName); -- debug dump the output -- dbms_output.put_line('------ output ----------------'); -- FOR i IN l_diffs.FIRST..l_diffs.LAST --LOOP -- dbms_output.put_line(l_diffs(i)); -- dbms_output.put_line('---------'); --END LOOP; RETURN l_diffs; EXCEPTION WHEN OTHERS THEN IF DBMS_SQL.IS_OPEN(l_cursorName) THEN DBMS_SQL.CLOSE_CURSOR(l_cursorName); END IF; RAISE; END get_child_diffs; -------------------------------------------------------------------------- -- Function: get_child_rowcounts -- -- For each table in the list of given tables, determine how many -- rows there are in each table for a given snapshot. -- -- E.g. if the tables are MGMT_DB_DATAFILES_ECM and MGMT_DB_TABLES_ECM -- this function would return the number of rows in -- MGMT_DB_DATAFILES_ECM for the given snapshot guid, and the number -- of rows in MGMT_DB_TABLES_ECM for the snapshot guid -- -- PARAMETERS -- p_guid The snapshot guid -- p_tables Nested table of table names -- -- RETURN -- Return ecm_gendiff_rowcounts_table -- This is a nested table of records. Each record consists of -- a tablename and the rowcount for that table. -- -------------------------------------------------------------------------- FUNCTION get_child_rowcounts( p_guid RAW, p_tables ecm_gendiff_tablename_table) RETURN ecm_gendiff_rowcounts_table IS -- Sql template for getting child table count l_sqlTemplate CONSTANT VARCHAR2(200) := 'SELECT COUNT(*) FROM WHERE ecm_snapshot_id = :viewGuid'; -- Complete sql query l_sql VARCHAR2(21000) := l_sqlTemplate; -- Output results for all tables l_counts ecm_gendiff_rowcounts_table; -- Output results for one table l_oneTabResults ecm_gendiff_rowcount_rec; -- One result l_oneCount INTEGER; l_res NUMBER; BEGIN -- Get the results for each table l_counts := ecm_gendiff_rowcounts_table(); l_counts.EXTEND(p_tables.COUNT); FOR t IN p_tables.FIRST..p_tables.LAST LOOP -- Create the query l_sql := REPLACE(l_sqlTemplate, '', EM_CHECK.SIMPLE_SQL_NAME(p_tables(t))); EXECUTE IMMEDIATE l_sql INTO l_oneCount USING p_guid ; l_oneTabResults := ecm_gendiff_rowcount_rec(p_tables(t), l_oneCount); l_counts(t) := l_oneTabResults; END LOOP; -- all tables RETURN l_counts; END get_child_rowcounts; END ecm_gendiff; / SHOW ERRORS;