Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/ecm/ecm_delta_pkgbody.sql /st_emcore_10.2.0.4.4db11.2.0.3/1 2011/06/28 10:53:26 rajranja Exp $
Rem
Rem ecm_delta_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2011, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem ecm_delta_pkgbody.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem vkhizder 06/18/09 - XbranchMerge vkhizder_bug6503115_10g from
Rem st_emcore_10.2.0.1.0
Rem rrawat 06/01/07 - Bug-6082851
Rem rrawat 07/03/07 - Backport rrawat_6082851_rr from main
Rem rrawat 06/01/07 - Bug-6082851
Rem rrawat 06/27/07 - Backport rrawat_bug-6082851 from main
Rem jsmoler 01/12/06 - fix bug 4902525
Rem asaraswa 12/08/05 - Backport asaraswa_bug-4200301 from main
Rem asaraswa 10/25/05 - fixing 4200301
Rem rrawat 06/20/05 - fix 4438657
Rem agor 06/17/05 - fix 3967064
Rem agor 04/12/05 - fix 4148619
Rem agor 03/08/05 - take care of keys which can be null
Rem smalathe 11/26/04 - Bug#3903438: Modify query on MGMT_DELTA_IDS
Rem jsmoler 11/04/04 - fix bug
Rem smalathe 09/17/04 - Bug#3879246: Change GET_CURSOR function to
Rem incorporate RAW datatype
Rem mgoodric 07/16/04 - Add NLS key lookup
Rem awarkar 12/05/03 - Bug Fix For Issue#3284443
Rem awarkar 11/15/03 - Bug Fix For Issue#3255739
Rem shuberma 11/10/03 - Making target purge policy system based
Rem shuberma 09/18/03 - Adding exception block
Rem shuberma 09/18/03 - Add purge policy callback
Rem vkhizder 08/06/03 - fix for history related to single row tables
Rem shuberma 07/11/03 - Fixing problem that prevents compilation of body
Rem shuberma 07/08/03 - Fixing 2K database problem
Rem shuberma 06/27/03 - Improvements to COMPARE_KEYS as per vitaliy''s comments/
Rem shuberma 06/25/03 - Fixing the compare_keys procedure so it handles null key values
Rem shuberma 06/11/03 - Save directly to the repository instead of accumulating diffs in memory
Rem shuberma 04/17/03 - Adding new areg to the retrieve comparison procedure
Rem shuberma 03/20/03 - Adding optional arguments to purge history
Rem shuberma 01/27/03 - Add method for getting entire save comparison
Rem shuberma 01/14/03 - Changing start_delta signature
Rem shuberma 12/30/02 - Adding work around for plsql bug 2013606
Rem shuberma 12/10/02 - adding new save comparison method
Rem shuberma 11/20/02 - Making comparison use object for recording deltas
Rem shuberma 11/19/02 - Modifying to allow use of delta query for both history can compare
Rem shuberma 11/01/02 - Performance
Rem shuberma 10/24/02 - Speeding up comparison
Rem shuberma 10/17/02 - Adding PURGE_HISTORY procedure
Rem shuberma 09/20/02 - passing two queries instead of one
Rem shuberma 09/12/02 - Moving sql generation for compare into plsql
Rem shuberma 09/05/02 - Adding method for doing comparisons and saving
Rem shuberma 08/21/02 - add summary info to diff_query processing
Rem shuberma 08/23/02 - Changes to allow for saving comparisons
Rem shuberma 08/14/02 - fix bug when deltavalues are null for exist
Rem shuberma 06/21/02 - Allow insert and deletes without values
Rem shuberma 06/05/02 - Use delta values instead of name values for passing attributes..
Rem shuberma 05/22/02 - .
Rem shuberma 05/17/02 - Adding type specific methods..
Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts
Rem rpinnama 05/15/02 - Created
Rem
CREATE OR REPLACE PACKAGE BODY MGMT_DELTA
IS
-- First a couple of constants used as templates for the sql queries.
EACH_QUERY_TEMPLATE CONSTANT VARCHAR2(100) :=
'select <TC1> from <TC2> f where <TC3> <TC4>';
-- <comma_separated_list_from_first_key_list_and_diff_list>
TC1 CONSTANT VARCHAR2(5) := '<TC1>';
-- <one_query_in_parens_or_table_name_or_view_name>
TC2 CONSTANT VARCHAR2(5) := '<TC2>';
-- <id_first_and_list>
TC3 CONSTANT VARCHAR2(5) := '<TC3>';
-- <order_by_comma_separated_match_character_col_list>
TC4 CONSTANT VARCHAR2(5) := '<TC4>';
TYPE T_ROW_GUID_TABLE IS TABLE OF MGMT_DELTA_ENTRY.ROW_GUID%TYPE;
-- Constants used when selected configuration-item-type-instances are compared (only applicable to host)
-- in these cases direct calls are made to DO_DIFF_QUERIES/DO_DIFF_QUERIES_AND_SAVE instead of
-- MGMT_ECM_SNAPSHOT_PKG.COMPARE_GEN_CONF_AND_SAVE
QUERY_PASSED_NOT_SIMPLE_NAME CONSTANT VARCHAR2(20) := 'QUERY_PASSED:';
QUERY_INDICATOR_LENGTH CONSTANT SMALLINT := length(QUERY_PASSED_NOT_SIMPLE_NAME);
-- Vendor Software Components (java flow is never hit so dont need to define here)
-- COMPARE_QUERY_CONSTANT_VSCI CONSTANT VARCHAR2(20) := QUERY_PASSED_NOT_SIMPLE_NAME || 'VSCI';
-- COMPARE_QUERY_VSCI CONSTANT VARCHAR2( 4000) :=
-- 'SELECT name, type, version, vendor_software_guid' ||
-- ' FROM MGMT_HC_VENDOR_SW_COMPONENTS' ||
-- ' WHERE type = ';
-- Dependees For Oracle Component
COMPARE_QUERY_CONSTANT_DFOC CONSTANT VARCHAR2(20) := QUERY_PASSED_NOT_SIMPLE_NAME || 'DFOC';
COMPARE_QUERY_DFOC CONSTANT VARCHAR2( 4000) :=
' ( ' ||
'select unique ' ||
' component.name, ' ||
' ecm_util.CONCAT_COMPONENT_VERSION(component.name, ' ||
' component.container_guid) version, ' ||
' component.external_name, ' ||
' component_home.container_location, ' ||
' component_home.snapshot_guid ' ||
'from ' ||
' mgmt_inv_component component, ' ||
' ( ' ||
' SELECT UNIQUE relationship.dependee_guid ' ||
' FROM mgmt_inv_dependency_rule relationship ' ||
' START WITH relationship.referencer_guid = ' ||
' ( SELECT component_guid ' ||
' FROM mgmt_inv_component r, mgmt_inv_container rh ' ||
' WHERE rh.snapshot_guid = :b1 ' ||
' AND rh.container_location = :b4 ' ||
' AND rh.container_guid = r.container_guid ' ||
' AND r.name = :b2 ' ||
' AND r.version = :b3) ' ||
' CONNECT BY relationship.referencer_guid = PRIOR relationship.dependee_guid ' ||
' ) rel , ' ||
' mgmt_inv_container component_home ' ||
'where component.component_guid = rel.dependee_guid ' ||
' and component.container_guid = component_home.container_guid ' ||
' ) ';
-- Oracle PatchSets In Container
COMPARE_QUERY_CONSTANT_OPSIC CONSTANT VARCHAR2(20) := QUERY_PASSED_NOT_SIMPLE_NAME || 'OPSIC';
COMPARE_QUERY_OPSIC CONSTANT VARCHAR2( 4000) :=
' ( ' ||
' SELECT home.snapshot_guid as snapshot_guid, ' ||
' home.container_location as location, ' ||
' p.external_name as name, p.version as version ' ||
' FROM MGMT_INV_PATCHSET p, MGMT_INV_CONTAINER home ' ||
' WHERE p.container_guid = home.container_guid ' ||
' ) ';
-- Oracle Interim-patches In Container
COMPARE_QUERY_CONSTANT_OIPIC CONSTANT VARCHAR2(20) := QUERY_PASSED_NOT_SIMPLE_NAME || 'OIPIC';
COMPARE_QUERY_OIPIC CONSTANT VARCHAR2( 4000) :=
' ( ' ||
' SELECT home.snapshot_guid as snapshot_guid, ' ||
' home.container_location as location, p.id as patch_id' ||
' FROM MGMT_INV_PATCH p, MGMT_INV_CONTAINER home ' ||
' WHERE p.container_guid = home.container_guid ' ||
' ) ';
-----------------------------------------------------------------------
-- A convenience function that returns a string that is all the keys
-- of all the key's name-value pairs. This can be used to compare keys.
-- Example result "VAL1VAL2"
--
PROCEDURE GET_KEY_STRINGS( v_key MGMT_NAME_VALUES,
v_key_string out VARCHAR2,
v_key_string_rest out VARCHAR2 )
IS
i NUMBER;
BEGIN
v_key_string := '';
v_key_string_rest := '';
IF v_key IS NULL OR v_key.COUNT = 0 THEN
v_key_string := 'DEFAULT KEY STRING FOR SINGLE ROW KEYLESS SNAPSHOT TABLES';
RETURN;
END IF;
i := v_key.FIRST;
BEGIN
WHILE i IS NOT NULL
LOOP
v_key_string := v_key_string || ' ' || LENGTH(v_key(i).VALUE) || ' ' || v_key(i).VALUE;
i := v_key.NEXT(i);
END LOOP;
EXCEPTION
WHEN VALUE_ERROR THEN
WHILE i IS NOT NULL
LOOP
v_key_string_rest := v_key_string_rest || ' ' || LENGTH(v_key(i).VALUE) || ' ' || v_key(i).VALUE;
i := v_key.NEXT(i);
END LOOP;
END;
EXCEPTION
WHEN VALUE_ERROR THEN
RETURN; -- Truncated.
END GET_KEY_STRINGs;
------------------------------------------------------------------------
-- Register an object key for a table. It returns a unique key, independent from the
-- actual row values that identify the row.
-- If already registered, no action is taken except to return the delta guid id for the row.
-- v_collection_type The name of the table for which to record delta operations OR, an identifying
-- category for the data being recorded, e.g. KERNAL_PARAMETERS.
-- v_new_objkey A list (MGMT_NAME_VALUES) of name value pairs (MGMT_NAME_VALUE) that
-- make of the key, or identifying information for the record being changed. The name
-- component of each is typically a column name.
-- RETURNS A guid representing the delta guid id used to identify all delta entries
-- for the associated history rows.
PROCEDURE REGISTER_OBJECT( v_collection_type VARCHAR2,
v_new_objkey MGMT_NAME_VALUES,
v_new_entryguid RAW,
v_last_entryguid out RAW,
v_row_guid out RAW )
IS
l_tempkey MGMT_DELTA_IDS.KEY_STRING%TYPE;
l_tempkeyrest MGMT_DELTA_IDS.KEY_STRING_REST%TYPE;
BEGIN
-- First see if the new obj is already registered.
v_row_guid := NULL;
v_last_entryguid := NULL;
MGMT_DELTA.GET_KEY_STRINGS( v_new_objkey,
l_tempkey,
l_tempkeyrest );
-- A row was found, and the old and new row ids are different.
-- Update the registration.
UPDATE MGMT_DELTA_IDS
SET LAST_DELTA_ENTRY_GUID = v_new_entryguid, LAST_UPDATED_TIME = SYSDATE
WHERE KEY_STRING = l_tempkey
AND COLLECTION_TYPE = v_collection_type
AND nvl(KEY_STRING_REST, ' ') = nvl(l_tempkeyrest, ' ')
RETURNING LAST_DELTA_ENTRY_GUID, ROW_GUID INTO v_last_entryguid, v_row_guid;
IF SQL%NOTFOUND
THEN
INSERT INTO MGMT_DELTA_IDS( LAST_DELTA_ENTRY_GUID,
COLLECTION_TYPE,
KEY_STRING,
KEY_STRING_REST,
LAST_UPDATED_TIME )
VALUES (v_new_entryguid, v_collection_type, l_tempkey, l_tempkeyrest,
SYSDATE )
RETURNING ROW_GUID into v_row_guid;
--Add the entries only if the key is not null and are not 0
IF (v_new_objkey IS NOT NULL AND v_new_objkey.COUNT > 0 ) THEN
FOR i IN v_new_objkey.FIRST .. v_new_objkey.LAST
LOOP
INSERT INTO MGMT_DELTA_ID_VALUES( DELTA_IDS_GUID, NAME, VALUE, TYPE )
VALUES (v_row_guid, v_new_objkey(i).NAME, v_new_objkey(i).VALUE, v_new_objkey(i).TYPE );
END LOOP;
END IF;
END IF;
END REGISTER_OBJECT;
------------------------------------------------------------------------------
PROCEDURE RECORD_DELTA_ENTRY_VALUES( v_delta_entryguid RAW,
v_column_values MGMT_DELTA_VALUES )
IS
BEGIN
IF v_column_values IS NULL OR v_column_values.COUNT <= 0 THEN RETURN; END IF;
FOR i IN v_column_values.FIRST..v_column_values.LAST
LOOP
INSERT INTO MGMT_DELTA_ENTRY_VALUES( DELTA_ENTRY_GUID,
NAME,
VALUE,
OLD_VALUE,
TYPE )
VALUES (v_delta_entryguid,
v_column_values(i).NAME,
v_column_values(i).LEFT_VALUE,
v_column_values(i).RIGHT_VALUE,
v_column_values(i).TYPE );
END LOOP;
END RECORD_DELTA_ENTRY_VALUES;
--------------------------------------------------------------------------
-- Record a peration on the named table.
-- v_collection_type: The name of the table for which to record a delete operation OR, an identifying
-- category for the data being recorded, e.g. KERNAL_PARAMETERS.
-- v_objkey: A list (MGMT_NAME_VALUES) of name value pairs (MGMT_NAME_VALUE) that
-- make of the key, or identifying information for the record being deleted. The name
-- component of each is typically a column name.
-- v_column_values: This is a set (MGMT_DELTA_VALUES) of MGMT_DELTA_VALUE
-- (column_name-new_column_value-old_column_value triplets)
-- that capture all the column values for the row being deleted.
-- The new value is left null. Typically, the
-- name field is a column name and the values, the column value. However, when the
-- table information being captured is a NAME-VALUE pair type table, then the name field of
-- MGMT_DELTA_VALUE may actually be the value of the source table's NAME column.
-- v_operation: Typically INSERT_C, UPDATE_C or DELETE_C
PROCEDURE RECORD_OPERATION( v_collection_type VARCHAR2,
v_objkey MGMT_NAME_VALUES,
v_column_values MGMT_DELTA_VALUES,
v_operation VARCHAR2,
v_time DATE := SYSDATE )
IS
l_row_guid RAW(16);
l_deltaguid RAW(16);
l_new_entryguid RAW(16);
l_last_entryguid RAW(16);
BEGIN
-- First lookup the delta guid for the current transaction.
l_deltaguid := GET_CURRENT_DELTA_GUID();
l_new_entryguid := SYS_GUID();
REGISTER_OBJECT( v_collection_type,
v_objkey,
l_new_entryguid,
l_last_entryguid,
l_row_guid );
INSERT INTO MGMT_DELTA_ENTRY( DELTA_GUID,
ROW_GUID,
OPERATION,
DELTA_ENTRY_GUID,
PREV_DELTA_ENTRY_GUID,
DELTA_TIME)
VALUES (l_deltaguid,
l_row_guid,
v_operation,
l_new_entryguid,
l_last_entryguid,
v_time );
RECORD_DELTA_ENTRY_VALUES( l_new_entryguid,
v_column_values );
END RECORD_OPERATION;
--------------------------------------------------------------------------
-- Record a delete operation on the named table.
-- v_collection_type: The name of the table for which to record a delete operation OR, an identifying
-- category for the data being recorded, e.g. KERNAL_PARAMETERS.
-- v_objkey: A list (MGMT_NAME_VALUES) of name value pairs (MGMT_NAME_VALUE) that
-- make of the key, or identifying information for the record being deleted. The name
-- component of each is typically a column name.
-- v_old_column_values: This is a set (MGMT_DELTA_VALUES) of MGMT_DELTA_VALUE
-- (column_name-new_column_value-old_column_value triplets)
-- that capture all the column values for the row being deleted.
-- The new value is left null. Typically, the
-- name field is a column name and the values, the column value. However, when the
-- table information being captured is a NAME-VALUE pair type table, then the name field of
-- MGMT_DELTA_VALUE may actually be the value of the source table's NAME column.
--
PROCEDURE RECORD_DELETE( v_collection_type VARCHAR2,
v_objkey MGMT_NAME_VALUES,
v_old_column_values MGMT_DELTA_VALUES,
v_time DATE := SYSDATE )
IS
BEGIN
RECORD_OPERATION( v_collection_type, v_objkey, v_old_column_values, DELETE_C, v_time );
END RECORD_DELETE;
-- Record an update operation on the named table.
-- v_collection_type: The name of the table for which to record an update operation OR, an identifying
-- category for the data being recorded, e.g. KERNAL_PARAMETERS.
-- v_objkey: A list (MGMT_NAME_VALUES) of name value pairs (MGMT_NAME_VALUE) that
-- make of the key, or identifying information for the record being deleted. The name
-- component of each is typically a column name.
-- v_old_column_values: This is a set (MGMT_DELTA_VALUES) of MGMT_DELTA_VALUE
-- (column_name-new_column_value-old_column_value triplets)
-- that capture all the column values for the row being updated. Typically, the
-- name field is a column name and the values, the column value. However, when the
-- table information being captured is a NAME-VALUE pair type table, then the name field of
-- MGMT_DELTA_VALUE may actually be the value of the source table's NAME column.
PROCEDURE RECORD_UPDATE( v_collection_type VARCHAR2,
v_objkey MGMT_NAME_VALUES,
v_updated_column_values MGMT_DELTA_VALUES,
v_time DATE := SYSDATE )
IS
BEGIN
IF v_updated_column_values IS NOT NULL AND v_updated_column_values.COUNT > 0
THEN
RECORD_OPERATION( v_collection_type, v_objkey, v_updated_column_values, UPDATE_C, v_time );
END IF;
END RECORD_UPDATE;
-- Record an insert operation on the named table.
-- v_collection_type: The name of the table for which to record an insert operation OR, an identifying
-- category for the data being recorded, e.g. KERNAL_PARAMETERS.
-- v_objkey: A list (MGMT_NAME_VALUES) of name value pairs (MGMT_NAME_VALUE) that
-- make of the key, or identifying information for the record being inserted. The name
-- component of each is typically a column name.
-- v_inserted_column_value This is a set (MGMT_DELTA_VALUES) of
-- MGMT_DELTA_VALUE (column_name-new_column_value-old_column_value triplets)
-- that capture all the column values for the row being inserted.
-- The old value field is left NULL.
-- Typically, the name field is a column name and the values, the column value.
-- However, when the table information being captured is a NAME-VALUE
-- pair type table, then the name field of MGMT_DELTA_VALUE may actually
-- be the value of the source table's NAME column.
PROCEDURE RECORD_INSERT( v_collection_type VARCHAR2,
v_objkey MGMT_NAME_VALUES,
v_inserted_column_values MGMT_DELTA_VALUES,
v_time DATE := SYSDATE )
IS
BEGIN
RECORD_OPERATION( v_collection_type, v_objkey, v_inserted_column_values, INSERT_C, v_time );
END RECORD_INSERT;
------------------------------------------------------------------
-- This procedure is a convenience function that compares the old and new values and
-- only adds an entry to the MGMT_DELTA_VALUES list if the two are different.
-- v_updatetable The list of delta values. Could be passed in as null. If
-- null, it will be assigned a new MGMT_DELTA_VALUES object.
-- v_attribute_name The name of the attribute or column used to identify the value in
-- the name-new_val-old_val triplet.
-- v_right_value The old value of the attribute
-- v_left_value The new value of the attribute.
--
PROCEDURE TEST_AND_ADD_DELTA( v_updatetable in out MGMT_DELTA_VALUES,
v_attribute_name in VARCHAR2,
v_right_value in VARCHAR2,
v_left_value in VARCHAR2,
v_attribute_type in CHAR )
IS
BEGIN
IF NVL(v_right_value,' ') <> NVL(v_left_value,' ')
THEN
IF v_updatetable IS NULL
THEN
v_updatetable := MGMT_DELTA_VALUES();
END IF;
v_updatetable.EXTEND;
v_updatetable(v_updatetable.LAST) :=
MGMT_DELTA_VALUE( v_attribute_name,
v_left_value,
v_right_value,
DIFFERENT_C,
v_attribute_type );
END IF;
END TEST_AND_ADD_DELTA;
------------------------------------------------------------------------------------
-- DO_TEST
-- This function evaluates the p_pred rule that is passed in and returns the resulting
-- value. If the predicate being passed in is null, then a default of an inequality comparison
-- is performed. The condition is a sql conditional expression with the identifiers left and right
-- being used to represent the left and right values.
--
FUNCTION DO_TEST( p_pred VARCHAR2,
left_value VARCHAR2,
right_value VARCHAR2 )
RETURN boolean
IS
RES NUMBER;
rule VARCHAR2(1000);
BEGIN
IF p_pred IS NULL
THEN
RETURN nvl(left_value, ' ') <> nvl(right_value, ' ');
ELSE
rule := 'declare left VARCHAR2(1000); right VARCHAR2(1000); BEGIN LEFT := :1; RIGHT := :2; select case when ' || EM_CHECK.NOOP(p_pred) || ' THEN 1 ELSE 0 END INTO :3 from dual; END;';
EXECUTE IMMEDIATE rule USING left_value, right_value, out RES;
IF RES = 1 THEN RETURN true; END IF;
END IF;
RETURN false;
END;
FUNCTION DO_TEST( p_pred VARCHAR2,
left_value TIMESTAMP,
right_value TIMESTAMP )
RETURN boolean
IS
RES NUMBER;
rule VARCHAR2(1000);
BEGIN
IF p_pred IS NULL
THEN
RETURN nvl(to_char(left_value), ' ') <> nvl(to_char(right_value), ' ');
ELSE
rule := 'declare left TIMESTAMP; right TIMESTAMP; BEGIN LEFT := :1; RIGHT := :2; select case when ' || EM_CHECK.NOOP(p_pred) || ' THEN 1 ELSE 0 END INTO :3 from dual; END;';
EXECUTE IMMEDIATE rule USING left_value, right_value, out RES;
IF RES = 1 THEN RETURN true; END IF;
END IF;
return false;
END;
FUNCTION DO_TEST( p_pred VARCHAR2,
left_value NUMBER,
right_value NUMBER )
RETURN boolean
IS
RES NUMBER;
rule VARCHAR2(1000);
BEGIN
IF p_pred IS NULL
THEN
RETURN nvl(left_value, 0) <> nvl(right_value, 0);
ELSE
rule := 'declare left NUMBER; right NUMBER; BEGIN LEFT := :1; RIGHT := :2; select case when ' || EM_CHECK.NOOP(p_pred) || ' THEN 1 ELSE 0 END INTO :3 from dual; END;';
EXECUTE IMMEDIATE rule USING left_value, right_value, out RES;
IF RES = 1 THEN RETURN true; END IF;
END IF;
return false;
END;
------------------------------------------------------------------
-- This procedure is a convenience function that compares the old and new values and
-- only adds an entry to the MGMT_DELTA_VALUES list if the two are different.
-- v_updatetable The list of delta values. Could be passed in as null. If
-- null, it will be assigned a new MGMT_DELTA_VALUES object.
-- v_attribute A name value pair, the name is the name of the attribute,
-- and the value is a rule for testing.
-- v_left_value The new value of the attribute
-- v_right_value The old value of the attribute.
-- v_left_value The new value of the attribute
-- v_right_value The old value of the attribute.
FUNCTION TEST_AND_ADD_DIFF( v_updatetable in out MGMT_DELTA_VALUES,
v_attribute in MGMT_DELTA_VALUE,
v_left_value in VARCHAR2,
v_right_value in VARCHAR2,
v_flags in SMALLINT )
RETURN BOOLEAN
IS
BEGIN
IF DO_TEST(v_attribute.LEFT_VALUE, v_left_value, v_right_value ) THEN
IF v_updatetable IS NULL
THEN
v_updatetable := MGMT_DELTA_VALUES();
END IF;
v_updatetable.EXTEND;
v_updatetable(v_updatetable.LAST) :=
MGMT_DELTA_VALUE( v_attribute.name,
v_left_value,
v_right_value,
DIFFERENT_C,
STRING_TYPE_C );
RETURN TRUE;
ELSE
-- The old value of the attribute is an indicator that same should be returned.
IF (v_attribute.RIGHT_VALUE IS NOT NULL) OR (v_flags = RETURN_ALL)
THEN
IF v_updatetable IS NULL
THEN
v_updatetable := MGMT_DELTA_VALUES();
END IF;
v_updatetable.EXTEND;
-- Return both. We will indicate same, but may be different because of the
-- rules.
v_updatetable(v_updatetable.LAST) :=
MGMT_DELTA_VALUE( v_attribute.name,
v_left_value,
v_right_value,
SAME_C,
STRING_TYPE_C );
END IF;
END IF;
RETURN FALSE;
END TEST_AND_ADD_DIFF;
FUNCTION GET_UTC_TIMESTAMP_STRING( p_time TIMESTAMP )
RETURN VARCHAR2
IS
l_charTime VARCHAR2(40);
BEGIN
l_charTime := NULL;
IF p_time IS NOT NULL THEN
-- work around for a plsql bug in 9.0.1, bug # 2013606
-- l_charTime := to_char(SYS_EXTRACT_UTC(p_time), 'YYYY-MM-DD HH24:MI:SS.FF' );
execute immediate
'select to_char(SYS_EXTRACT_UTC(:p_time), ''YYYY-MM-DD HH24:MI:SS.FF'' ) from dual'
into l_charTime using p_time;
END IF;
RETURN l_charTime;
END GET_UTC_TIMESTAMP_STRING;
FUNCTION TEST_AND_ADD_DIFF( v_updatetable in out MGMT_DELTA_VALUES,
v_attribute in MGMT_DELTA_VALUE,
v_left_value in TIMESTAMP,
v_right_value in TIMESTAMP,
v_flags in SMALLINT )
RETURN BOOLEAN
IS
BEGIN
IF DO_TEST(v_attribute.LEFT_VALUE, v_left_value, v_right_value ) THEN
IF v_updatetable IS NULL
THEN
v_updatetable := MGMT_DELTA_VALUES();
END IF;
v_updatetable.EXTEND;
v_updatetable(v_updatetable.LAST) :=
MGMT_DELTA_VALUE( v_attribute.name,
GET_UTC_TIMESTAMP_STRING( v_left_value ),
GET_UTC_TIMESTAMP_STRING( v_right_value ),
DIFFERENT_C,
DATETIME_TYPE_C );
RETURN TRUE;
ELSE
-- The old value of the attribute is an indicator that same should be returned.
IF (v_attribute.RIGHT_VALUE IS NOT NULL) OR (v_flags = RETURN_ALL)
THEN
IF v_updatetable IS NULL
THEN
v_updatetable := MGMT_DELTA_VALUES();
END IF;
v_updatetable.EXTEND;
-- Return both. We will indicate same, but may be different because of the
-- rules.
v_updatetable(v_updatetable.LAST) :=
MGMT_DELTA_VALUE( v_attribute.name,
GET_UTC_TIMESTAMP_STRING(v_left_value ),
GET_UTC_TIMESTAMP_STRING( v_right_value ),
SAME_C,
DATETIME_TYPE_C );
END IF;
END IF;
RETURN FALSE;
END TEST_AND_ADD_DIFF;
FUNCTION TEST_AND_ADD_DIFF( v_updatetable in out MGMT_DELTA_VALUES,
v_attribute in MGMT_DELTA_VALUE,
v_left_value in NUMBER,
v_right_value in NUMBER,
v_flags in SMALLINT )
RETURN BOOLEAN
IS
BEGIN
IF DO_TEST(v_attribute.LEFT_VALUE, v_left_value, v_right_value ) THEN
IF v_updatetable IS NULL
THEN
v_updatetable := MGMT_DELTA_VALUES();
END IF;
v_updatetable.EXTEND;
v_updatetable(v_updatetable.LAST) :=
MGMT_DELTA_VALUE( v_attribute.name,
v_left_value,
v_right_value,
DIFFERENT_C,
NUMBER_TYPE_C );
RETURN TRUE;
ELSE
-- The old value of the attribute is an indicator that same should be returned.
IF (v_attribute.RIGHT_VALUE IS NOT NULL) OR (v_flags = RETURN_ALL)
THEN
IF v_updatetable IS NULL
THEN
v_updatetable := MGMT_DELTA_VALUES();
END IF;
v_updatetable.EXTEND;
-- Return both. We will indicate same, but may be different because of the
-- rules.
v_updatetable(v_updatetable.LAST) :=
MGMT_DELTA_VALUE( v_attribute.name,
v_left_value,
v_right_value,
SAME_C,
NUMBER_TYPE_C );
END IF;
END IF;
RETURN FALSE;
END TEST_AND_ADD_DIFF;
--------------------------------------------------------------------------------
PROCEDURE ADD_NON_NULL_DELTA_VALUE( v_deltavalues in out MGMT_DELTA_VALUES,
v_attribute_name in VARCHAR2,
v_right_value in VARCHAR2,
v_left_value in VARCHAR2,
v_attribute_type in CHAR,
p_side in VARCHAR2)
IS
BEGIN
IF v_deltavalues IS NULL
THEN
v_deltavalues := MGMT_DELTA_VALUES();
END IF;
v_deltavalues.EXTEND;
v_deltavalues(v_deltavalues.LAST) :=
MGMT_DELTA_VALUE( v_attribute_name,
v_left_value,
v_right_value,
p_side,
v_attribute_type );
END ADD_NON_NULL_DELTA_VALUE;
-- Register the start of a delta time slice. Used to associated a set of IUDs as part of
-- the same delta. The caller can supplied thier own delta guid or have a new delta guid
-- generated. This allows the code that calls the RECORD_XXX methods, such as triggers,
-- to look up the delta guid for the current transaction. Note that the transaction id, should be kept
-- up to date by calling the update_delta_XACT after any intermediate commits. The GUID returned will be
-- a guid identifying the delta and is the same as v_delta_guid.
-- v_delta_guid: A identifier of the current delta. If not supplied, the default will generate
-- one.
-- RETURNS: The delta guid.
--
FUNCTION START_DELTA( v_delta_guid RAW := SYS_GUID()) RETURN RAW
IS
BEGIN
INSERT INTO MGMT_DELTA_SNAP( DELTA_GUID, TRANSACTION_ID )
VALUES (v_delta_guid, DBMS_TRANSACTION.LOCAL_TRANSACTION_ID());
RETURN v_delta_guid;
END START_DELTA;
-- Register the start of a delta time slice. Used to associated a set of IUDs as part of
-- the same delta. The caller can supplied thier own delta guid or have a new delta guid
-- generated. This allows the code that calls the RECORD_XXX methods, such as triggers,
-- to look up the delta guid for the current transaction. Note that the transaction id, should be kept
-- up to date by calling the update_delta_XACT after any intermediate commits. The GUID returned will be
-- a guid identifying the delta and is the same as v_delta_guid.
-- l_snapguid: Snapshot_guid of the NEW or LEFT Snapshot.
-- l_timestamp: The timestamp of the snapshot.
-- l_target_name: Name of the left snapshot's target
-- r_snapguid: Snapshot_guid of the OLD or RIGHT snapshot.
-- r_timestamp: The timestamp of the snapshot.
-- r_target_name: Name of the right snapshot's target
-- target_type: Name of the targets' type
-- snapshot_type: Name of the snapshots' type
-- delta_type: The type of delta. (COMPARISON, HISTORY)
-- v_delta_guid: A identifier of the current delta. If not supplied, the default will generate
-- one.
-- RETURNS: The delta guid.
--
FUNCTION START_DELTA( l_snapguid RAW,
l_timestamp DATE,
l_target_name VARCHAR2,
r_snapguid RAW,
r_timestamp DATE,
r_target_name VARCHAR2,
target_type VARCHAR2,
snapshot_type VARCHAR2,
delta_type VARCHAR2,
v_delta_guid RAW := SYS_GUID()) RETURN RAW
IS
BEGIN
INSERT INTO MGMT_DELTA_SNAP( DELTA_GUID, TRANSACTION_ID, DELTA_TYPE,
TARGET_TYPE, SNAPSHOT_TYPE,
NEW_LEFT_SNAPSHOT_GUID, NEW_LEFT_TIMESTAMP,
NEW_LEFT_TARGET_NAME,
OLD_RIGHT_SNAPSHOT_GUID, OLD_RIGHT_TIMESTAMP,
OLD_RIGHT_TARGET_NAME )
VALUES (v_delta_guid, DBMS_TRANSACTION.LOCAL_TRANSACTION_ID(),
delta_type, target_type, snapshot_type, l_snapguid,
l_timestamp, l_target_name,
r_snapguid, r_timestamp, r_target_name );
RETURN v_delta_guid;
END START_DELTA;
------------------------------------------------------------------------
-- Update the delta identified by v_delta_guid. This will updated the deltas transaction id
-- to be the current tranaction id. Used when intermediate commits are performed.
-- v_delta_guid: The identifier of the delta for which to update the transaction id.
--
PROCEDURE UPDATE_DELTA_XACT( v_delta_guid RAW )
IS
BEGIN
UPDATE MGMT_DELTA_SNAP SET TRANSACTION_ID = DBMS_TRANSACTION.LOCAL_TRANSACTION_ID
WHERE DELTA_GUID = v_delta_guid;
END UPDATE_DELTA_XACT;
-- Get the delta_guid for the current transaction
-- If none are recorded, i.e. not in a delta, then the delta guid will be "NO_DELTA";
--
FUNCTION GET_CURRENT_DELTA_GUID RETURN RAW
IS
l_deltaguid RAW(16);
BEGIN
BEGIN
SELECT DELTA_GUID INTO l_deltaguid FROM MGMT_DELTA_SNAP
WHERE TRANSACTION_ID = DBMS_TRANSACTION.LOCAL_TRANSACTION_ID();
EXCEPTION
WHEN NO_DATA_FOUND THEN l_deltaguid := NULL;
END;
IF (l_deltaguid IS NULL)
THEN
l_deltaguid := NO_DELTA;
END IF;
RETURN l_deltaguid;
END GET_CURRENT_DELTA_GUID;
-----------------------------------------------------------------------
-- A convenience function that returns a string that is a concatenated list
-- of all the key's name-value pairs. This can be used to compare keys.
-- Example result "NAME1=VAL1 NAME2=VAL2"
--
FUNCTION GET_DELTA_KEY_DISPLAY_STRING( v_key_row_guid RAW ) RETURN VARCHAR2
IS
tmp VARCHAR2(4000);
tmp1 VARCHAR2( 4000);
l_name VARCHAR2(64);
l_value VARCHAR2(4000);
CURSOR c1 IS SELECT NAME, "VALUE"
FROM MGMT_DELTA_ID_VALUES
WHERE DELTA_IDS_GUID = v_key_row_guid;
BEGIN
tmp := NULL;
OPEN C1;
LOOP
FETCH c1 INTO l_name, l_value;
EXIT WHEN c1%NOTFOUND;
tmp1 := l_name || '=' ||l_value;
IF tmp IS NULL THEN
tmp := tmp1;
ELSE
tmp := tmp || ' ' || tmp1;
END IF;
END LOOP;
RETURN tmp;
EXCEPTION
WHEN VALUE_ERROR THEN RETURN tmp; -- Truncated.
END GET_DELTA_KEY_DISPLAY_STRING;
----------------------------------------------------------------------------------
-- A convenience function that returns a string that is a concatenated list of
-- the delte values' name-old_val-new_val triplets.
-- v_vals The delta_entry_guid for the delta operation..
-- v_operation INSERT_C, UPDATE_C, or DELETE_C. Based on this, the
-- old-val and new-vals are included or not.
-- Example result NAME(OLD_VAL=>NEW_VAL) for update
-- NAME(VAL) for INSERT or DELETE
-- Result is truncated is max size of VARCHAR2 is exceeded. (VALUE_ERROR)
FUNCTION GET_DELTA_VALUE_DISPLAY_STRING( v_delta_entryguid RAW,
v_operation VARCHAR2 ) RETURN VARCHAR2
IS
result VARCHAR2(4000);
comma VARCHAR2(3);
l_name VARCHAR2(64);
l_leftvalue VARCHAR2(4000);
l_rightvalue VARCHAR2(4000);
CURSOR c1 IS SELECT NAME, "VALUE", OLD_VALUE
FROM MGMT_DELTA_ENTRY_VALUES
WHERE DELTA_ENTRY_GUID = v_delta_entryguid;
BEGIN
result := '';
comma := '';
OPEN C1;
LOOP
FETCH c1 INTO l_name, l_leftvalue, l_rightvalue;
EXIT WHEN c1%NOTFOUND;
IF v_operation = UPDATE_C THEN
result := result || comma || l_name || '(' || l_rightvalue || '=>' || l_leftvalue || ')';
ELSE IF v_operation = INSERT_C THEN
result := result || comma || l_name || '(' || l_leftvalue || ')';
ELSE
result := result || comma || l_name || '(' || l_rightvalue || ')';
END IF; END IF;
comma := ' ';
END LOOP;
RETURN result;
EXCEPTION
WHEN VALUE_ERROR THEN RETURN result; -- Truncated.
END GET_DELTA_VALUE_DISPLAY_STRING;
----------------------------------------------------------------------------------
-- A convenience function that returns a string that is a concatenated list of
-- the delte values' name-old_val-new_val triplets.
-- v_vals The name-old_val-new_val triplets.
-- v_operation INSERT_C, UPDATE_C, or DELETE_C. Based on this, the
-- old-val and new-vals are included or not.
-- Example result NAME(OLD_VAL=>NEW_VAL) for update
-- NAME(VAL) for INSERT or DELETE.
--
FUNCTION GET_DELTA_VALUE_DISPLAY_STRING( v_vals MGMT_DELTA_VALUES,
v_operation VARCHAR2 ) RETURN VARCHAR2
IS
tmp VARCHAR2(4000);
tmp1 VARCHAR2( 1000);
i NUMBER;
BEGIN
tmp := NULL;
IF v_vals IS NULL THEN RETURN NULL; END IF;
i := v_vals.FIRST;
WHILE i IS NOT NULL
LOOP
IF v_operation = UPDATE_C THEN
tmp1 := v_vals(i).NAME || '(' || v_vals(i).RIGHT_VALUE || '=>' || v_vals(i).LEFT_VALUE || ')';
ELSE IF v_operation = INSERT_C THEN
tmp1 := v_vals(i).NAME || '(' || v_vals(i).LEFT_VALUE || ')';
ELSE
tmp1 := v_vals(i).NAME || '(' || v_vals(i).RIGHT_VALUE || ')';
END IF; END IF;
IF i = v_vals.FIRST THEN
tmp := tmp1;
ELSE
tmp := tmp || ' ' || tmp1;
END IF;
i := v_vals.NEXT(i);
END LOOP;
RETURN tmp;
END GET_DELTA_VALUE_DISPLAY_STRING;
---------------------------------------------------------------------------------
-- Function for getting key col list values.
FUNCTION GET_KEYCOL_LIST_VALUES( p_keyList VARCHAR2_TABLE,
p_curs INTEGER,
p_rec_tab dbms_sql.desc_tab )
RETURN MGMT_NAME_VALUES
IS
keyCols MGMT_NAME_VALUES;
charVal VARCHAR2(4000); -- Used to hold left side character column data.
numVal NUMBER; -- Used to hold left side number column data.
timeVal TIMESTAMP; -- Used to hold left side timestamp column data.
BEGIN
-- Look through and create the keylist structure with the key columns.
-- They are always the first in the list in the same order as select list.
keyCols := MGMT_NAME_VALUES();
IF p_keyList.COUNT > 0 THEN
FOR i IN p_keyList.FIRST..p_keyList.LAST
LOOP
keyCols.EXTEND;
CASE
WHEN p_rec_tab(i).col_type = DBMS_TYPES.TYPECODE_NUMBER THEN
DBMS_SQL.COLUMN_VALUE(p_curs, i, numVal );
keyCols(keyCols.LAST) := MGMT_NAME_VALUE( p_keyList(i),
numVal,
NUMBER_TYPE_C );
WHEN p_rec_tab(i).col_type = DBMS_TYPES.TYPECODE_DATE OR
p_rec_tab(i).col_type = DBMS_TYPES.TYPECODE_TIMESTAMP_TZ OR
p_rec_tab(i).col_type = DBMS_TYPES.TYPECODE_TIMESTAMP_LTZ OR
p_rec_tab(i).col_type = DBMS_TYPES.TYPECODE_TIMESTAMP THEN
DBMS_SQL.COLUMN_VALUE(p_curs, i, timeVal );
keyCols(keyCols.LAST) := MGMT_NAME_VALUE( p_keyList(i),
GET_UTC_TIMESTAMP_STRING( timeVal ),
DATETIME_TYPE_C );
ELSE
DBMS_SQL.COLUMN_VALUE(p_curs, i, charVal );
keyCols(keyCols.LAST) := MGMT_NAME_VALUE( p_keyList(i),
charVal,
STRING_TYPE_C );
END CASE;
END LOOP;
END IF;
RETURN keyCols;
END GET_KEYCOL_LIST_VALUES;
-----------------------------------------------------------------------------
-- Procedure for getting left/right only entries.
FUNCTION GET_ONE_SIDE_DELTA( p_collectionType VARCHAR2,
p_keyCols MGMT_NAME_VALUES,
p_attrList MGMT_DELTA_VALUES,
p_curs INTEGER,
p_rec_tab dbms_sql.desc_tab,
p_side VARCHAR2,
p_deltaSummary in out MGMT_DELTA_SUMMARY,
p_flags SMALLINT )
RETURN MGMT_DELTA_ENTRY_OBJ
IS
deltaVals MGMT_DELTA_VALUES;
firstk NUMBER;
fCharVal VARCHAR2(4000); -- Used to hold left side character column data.
fNumVal NUMBER; -- Used to hold left side number column data.
fTimeVal TIMESTAMP; -- Used to hold left side timestamp column data.
sCharVal VARCHAR2(4000); -- Used to hold left side character column data.
sNumVal NUMBER; -- Used to hold left side number column data.
sTimeVal TIMESTAMP; -- Used to hold left side timestamp column data.
rightOnly BOOLEAN;
BEGIN
rightOnly := p_side = RIGHT_ONLY_C;
-- Get Deltas, one side only.
-- for each row, loop through the rec_tabs and process each column pair,
-- one pair for each entry int he attrList.
deltaVals := MGMT_DELTA_VALUES();
FOR k IN 1..p_attrList.COUNT
LOOP
firstk := k + p_keyCols.COUNT;
CASE
WHEN p_rec_tab(firstk).col_type = DBMS_TYPES.TYPECODE_NUMBER THEN
DBMS_SQL.COLUMN_VALUE(p_curs, firstk, fNumVal );
IF rightOnly THEN
sNumVal := fNumVal;
fNumVal := null;
ELSE
sNumVal := null;
END IF;
MGMT_DELTA.ADD_NON_NULL_DELTA_VALUE( deltaVals,
p_attrList(k).NAME,
sNumVal,
fNumVal,
NUMBER_TYPE_C,
p_side);
WHEN p_rec_tab(firstk).col_type = DBMS_TYPES.TYPECODE_DATE OR
p_rec_tab(firstk).col_type = DBMS_TYPES.TYPECODE_TIMESTAMP OR
p_rec_tab(firstk).col_type = DBMS_TYPES.TYPECODE_TIMESTAMP_TZ OR
p_rec_tab(firstk).col_type = DBMS_TYPES.TYPECODE_TIMESTAMP_LTZ THEN
DBMS_SQL.COLUMN_VALUE(p_curs, firstk, fTimeVal );
IF rightOnly THEN
sTimeVal := fTimeVal;
fTimeVal := null;
ELSE
sTimeVal := null;
END IF;
MGMT_DELTA.ADD_NON_NULL_DELTA_VALUE( deltaVals,
p_attrList(k).NAME,
GET_UTC_TIMESTAMP_STRING(sTimeVal),
GET_UTC_TIMESTAMP_STRING(fTimeVal),
DATETIME_TYPE_C,
p_side);
ELSE
DBMS_SQL.COLUMN_VALUE(p_curs, firstk, fCharVal );
IF rightOnly THEN
sCharVal := fCharVal;
fCharVal := null;
ELSE
sCharVal := null;
END IF;
MGMT_DELTA.ADD_NON_NULL_DELTA_VALUE( deltaVals,
p_attrList(k).NAME,
sCharVal,
fCharVal,
STRING_TYPE_C,
p_side);
END CASE;
END LOOP;
IF rightOnly THEN
p_deltaSummary.RIGHT_OLD_COUNT := p_deltaSummary.RIGHT_OLD_COUNT + 1;
ELSE
p_deltaSummary.LEFT_NEW_COUNT := p_deltaSummary.LEFT_NEW_COUNT + 1;
END IF;
-- Done with the columns for this row, not add an entry to the
-- result if there were any deltas and we are returning them.
IF p_flags <> RETURN_NONE THEN
RETURN MGMT_DELTA_ENTRY_OBJ( p_side,
p_collectionType,
p_keyCols,
deltaVals );
END IF;
RETURN NULL;
END GET_ONE_SIDE_DELTA;
-----------------------------------------------------------------------------
-- Procedure for getting diffs or sames.
FUNCTION GET_BOTH_SIDE_DELTA( p_collectionType VARCHAR2,
p_keyCols MGMT_NAME_VALUES,
p_attrList MGMT_DELTA_VALUES,
p_fCurs INTEGER,
p_sCurs INTEGER,
p_frec_tab dbms_sql.desc_tab,
p_srec_tab dbms_sql.desc_tab,
p_deltaSummary in out MGMT_DELTA_SUMMARY,
p_flags SMALLINT )
RETURN MGMT_DELTA_ENTRY_OBJ
IS
deltaVals MGMT_DELTA_VALUES;
firstk NUMBER;
fCharVal VARCHAR2(4000); -- Used to hold left side character column data.
fNumVal NUMBER; -- Used to hold left side number column data.
fTimeVal TIMESTAMP; -- Used to hold left side timestamp column data.
sCharVal VARCHAR2(4000); -- Used to hold left side character column data.
sNumVal NUMBER; -- Used to hold left side number column data.
sTimeVal TIMESTAMP; -- Used to hold left side timestamp column data.
diffFound BOOLEAN;
testResult BOOLEAN;
operation VARCHAR2(20);
BEGIN
-- for each row, loop through the rec_tabs and process each column pair,
-- one pair for each entry in the attrList.
deltaVals := MGMT_DELTA_VALUES();
diffFound := false;
FOR k IN 1..p_attrList.COUNT
LOOP
firstk := k + p_keyCols.COUNT;
CASE
WHEN p_frec_tab(firstk).col_type = DBMS_TYPES.TYPECODE_NUMBER THEN
DBMS_SQL.COLUMN_VALUE(p_fCurs, firstk, fNumVal );
DBMS_SQL.COLUMN_VALUE(p_sCurs, firstk, sNumVal );
testResult := MGMT_DELTA.TEST_AND_ADD_DIFF( deltaVals,
p_attrList(k),
fNumVal,
sNumVal,
p_flags );
WHEN p_frec_tab(firstk).col_type = DBMS_TYPES.TYPECODE_DATE OR
p_frec_tab(firstk).col_type = DBMS_TYPES.TYPECODE_TIMESTAMP OR
p_frec_tab(firstk).col_type = DBMS_TYPES.TYPECODE_TIMESTAMP_TZ OR
p_frec_tab(firstk).col_type = DBMS_TYPES.TYPECODE_TIMESTAMP_LTZ THEN
DBMS_SQL.COLUMN_VALUE(p_fCurs, firstk, fTimeVal );
DBMS_SQL.COLUMN_VALUE(p_sCurs, firstk, sTimeVal );
testResult := MGMT_DELTA.TEST_AND_ADD_DIFF( deltaVals,
p_attrList(k),
fTimeVal,
sTimeVal,
p_flags );
ELSE
DBMS_SQL.COLUMN_VALUE(p_fCurs, firstk, fCharVal );
DBMS_SQL.COLUMN_VALUE(p_sCurs, firstk, sCharVal );
testResult := MGMT_DELTA.TEST_AND_ADD_DIFF( deltaVals,
p_attrList(k),
fCharVal,
sCharVal,
p_flags );
END CASE;
diffFound := diffFound OR testResult;
END LOOP;
-- Done with the columns for this row, not add an entry to the
-- result if there were any deltas.
IF deltaVals IS NOT NULL
THEN
IF diffFound THEN
operation := DIFFERENT_C;
p_deltaSummary.DIFFERENT_COUNT := p_deltaSummary.DIFFERENT_COUNT + 1;
ELSE
operation := SAME_C;
END IF;
-- If we are returning something...then place in entry list.
IF (p_flags <> RETURN_NONE AND diffFound) OR
(p_flags = RETURN_ALL)
THEN
RETURN MGMT_DELTA_ENTRY_OBJ( operation,
p_collectionType,
p_keyCols,
deltaVals );
END IF;
END IF;
RETURN NULL;
END GET_BOTH_SIDE_DELTA;
---------------------------------
FUNCTION GET_KEY_COLS( p_curs INTEGER,
p_keyList VARCHAR2_TABLE,
p_rec_tab dbms_sql.desc_tab)
RETURN MGMT_NAME_VALUES
IS
keyCols MGMT_NAME_VALUES;
BEGIN
keyCols := NULL;
IF DBMS_SQL.FETCH_ROWS(p_curs) > 0 THEN
keyCols := GET_KEYCOL_LIST_VALUES( p_keyList, p_curs, p_rec_tab );
END IF;
RETURN keyCols;
END GET_KEY_COLS;
-------------------------------------------
FUNCTION COMPARE_KEYS( fKeyCols MGMT_NAME_VALUES,
sKeyCols MGMT_NAME_VALUES )
RETURN NUMBER
IS
result NUMBER;
BEGIN
IF fKeyCols IS NULL OR fKeyCols.COUNT <= 0 THEN RETURN 0; END IF;
FOR i IN fKeyCols.FIRST..fKeyCols.LAST
LOOP
-- If both are null, then continue with next keys in loop. We will consider nulls equal.
-- If one is null and the other is not, then the null is considered greater than the other.
IF fKeyCols(i).VALUE IS NOT NULL
THEN
-- First of the two is not null.
IF sKeyCols(i).VALUE IS NULL OR fKeyCols(i).VALUE < sKeyCols(i).VALUE THEN
-- either second key is null or first key is less than the second key
RETURN -1;
ELSE IF fKeyCols(i).VALUE > sKeyCols(i).VALUE THEN
-- The second isn't null and is less than the first key
RETURN 1;
END IF; END IF;
ELSE IF sKeyCols(i).VALUE IS NOT NULL
THEN
-- second key is not null and the first key is null
RETURN 1;
END IF; END IF;
END LOOP; -- No difference found.
RETURN 0;
END COMPARE_KEYS;
------------------------------------------------------------------------------------
-- This function opens a cursor using the dbms_sql package. It uses this package so
-- it can get the desc_tab that describes the columns and their datatypes.
-- If we decide to keep the datatype information in the repository, then we can
-- modify this so it doesn't need to use the dbms_sql.
-- p_queryText Sql query to for which to open a cursor on.
-- p_idList A list of MGMT_DELTA_VALUE objects that describe the id columns.
-- p_bindRight True if we want to use the rightside id values for bind. Otherwise
-- use the leftside id values.
-- p_rec_tab A structure describing the results.
-- Returns a cursor id.
FUNCTION GET_CURSOR( p_queryText VARCHAR2,
p_idList MGMT_DELTA_VALUES,
p_bindRight BOOLEAN,
p_rec_tab out dbms_sql.desc_tab )
RETURN INTEGER
IS
curs INTEGER; -- Local cursor id
numrows INTEGER; -- Used to hold the results of the EXECUTE call.
col_cnt NUMBER; -- Holds the number of columns return from describe_columns.
numVal NUMBER; -- Used in the define_column call for number columns.
charVal VARCHAR2(4000); -- Used in the define_column call for char columns.
timeVal TIMESTAMP;-- Used in the define_column call for date/time columns.
rawVal RAW(4000); -- Used in the define_column call for RAW columns.
BEGIN
-- Open a dynamic cursor;
curs := DBMS_SQL.OPEN_CURSOR;
-- Parse the sql that was passed in.
DBMS_SQL.PARSE( curs, p_queryText, DBMS_SQL.NATIVE );
-- Bind the id columns.
FOR i IN p_idList.FIRST..p_idList.LAST
LOOP
IF p_bindRight
THEN
charVal := p_idList(i).right_value;
ELSE
charVal := p_idList(i).left_value;
END IF;
IF p_idList(i).type = NUMBER_TYPE_C THEN
numVal := charVal;
DBMS_SQL.BIND_VARIABLE(curs, ':b'||i, numVal);
ELSE IF p_idList(i).type = DATETIME_TYPE_C THEN
timeVal := charVal;
DBMS_SQL.BIND_VARIABLE(curs, ':b'||i, timeVal);
ELSE IF p_idList(i).type = RAW_TYPE_C THEN
rawVal := charVal;
DBMS_SQL.BIND_VARIABLE_RAW(curs, ':b'||i, rawVal);
ELSE
DBMS_SQL.BIND_VARIABLE(curs, ':b'||i, charVal);
END IF;
END IF;
END IF;
END LOOP;
-- Execute the sql statement
numrows := DBMS_SQL.EXECUTE(curs);
-- We want to examine the metadata to get datatypes.
DBMS_SQL.DESCRIBE_COLUMNS( curs, col_cnt, p_rec_tab );
-- Loop throught the record that describes the columns and call the
-- define column procedure.
FOR i in p_rec_tab.first..p_rec_tab.last
LOOP
CASE
WHEN p_rec_tab(i).col_type = DBMS_TYPES.TYPECODE_NUMBER THEN
DBMS_SQL.DEFINE_COLUMN( curs, i, numVal );
WHEN p_rec_tab(i).col_type = DBMS_TYPES.TYPECODE_DATE OR
p_rec_tab(i).col_type = DBMS_TYPES.TYPECODE_TIMESTAMP OR
p_rec_tab(i).col_type = DBMS_TYPES.TYPECODE_TIMESTAMP_TZ OR
p_rec_tab(i).col_type = DBMS_TYPES.TYPECODE_TIMESTAMP_LTZ THEN
DBMS_SQL.DEFINE_COLUMN( curs, i, timeVal );
ELSE
DBMS_SQL.DEFINE_COLUMN( curs, i, charVal, p_rec_tab(i).col_max_len );
END CASE;
END LOOP;
RETURN curs;
END GET_CURSOR;
-- Given a table/view name OR query-constant
-- This returns the table/view name ENQUOTED OR the query associated with constant
-- stored in pkg-def
FUNCTION GET_TC2_STRING( p_table_view_inner_query VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
IF substr(p_table_view_inner_query, 1, QUERY_INDICATOR_LENGTH ) != QUERY_PASSED_NOT_SIMPLE_NAME THEN
return DBMS_ASSERT.ENQUOTE_NAME(p_table_view_inner_query);
-- do not need to handle VSCI constant: that code is never hit in java: we never compare 2 selected vendor software components
--ELSIF substr( p_table_view_inner_query, 1, length(COMPARE_QUERY_CONSTANT_VSCI)) = COMPARE_QUERY_CONSTANT_VSCI THEN
-- return ' ( ' || COMPARE_QUERY_VSCI || DBMS_ASSERT.ENQUOTE_LITERAL (substr( p_table_view_inner_query, length(COMPARE_QUERY_CONSTANT_VSCI) + 1 )) || ' ) ';
ELSIF p_table_view_inner_query = COMPARE_QUERY_CONSTANT_DFOC THEN
return COMPARE_QUERY_DFOC;
ELSIF p_table_view_inner_query = COMPARE_QUERY_CONSTANT_OPSIC THEN
return COMPARE_QUERY_OPSIC;
ELSIF p_table_view_inner_query = COMPARE_QUERY_CONSTANT_OIPIC THEN
return COMPARE_QUERY_OIPIC;
END IF;
-- 2 returns with same so that history flow is terminates at first condition
-- this return is unlikely but if happens will give better errors (than returning null)
return DBMS_ASSERT.ENQUOTE_NAME(p_table_view_inner_query);
END GET_TC2_STRING;
---------------------------------------------------------------------------
-- This function creates a query that can be used to get a cursor that contains
-- rows with duplicate keys. The result set will be sorted on all the columns
-- to ensure a consistent sort order
-- p_queryTest The original text of the query
-- p_attrList The lsit of attributes
-- p_keyCols The list of keys
FUNCTION GET_SUBLIST_QUERY(p_queryText VARCHAR2,
p_attrList MGMT_DELTA_VALUES,
p_keyCols MGMT_NAME_VALUES )
RETURN VARCHAR2
IS
newQuery VARCHAR2(1000) := 'select * from (' || p_queryText || ')';
orderByString VARCHAR2(10) := ' ORDER BY ';
andString VARCHAR2(10) := '';
commaString VARCHAR2(2) := ' ';
BEGIN
IF p_keyCols IS NOT NULL AND p_keyCols.COUNT > 0
THEN
newQuery := newQuery || ' WHERE ';
FOR i IN p_keyCols.FIRST..p_keyCols.LAST
LOOP
newQuery := newQuery || andString || DBMS_ASSERT.ENQUOTE_NAME(p_keyCols(i).name) ||
' = ' || ':x' || i;
andString := ' and ';
END LOOP;
newQuery := newQuery || orderByString;
FOR i IN p_keyCols.FIRST..p_keyCols.LAST
LOOP
newQuery := newQuery || commaString || DBMS_ASSERT.ENQUOTE_NAME(p_keyCols(i).name) ;
commaString := ', ';
END LOOP;
orderByString := '';
END IF;
IF p_attrList IS NOT NULL AND P_attrList.COUNT > 0
THEN
newQuery := newQuery || orderByString;
FOR i IN p_attrList.FIRST..p_attrList.LAST
LOOP
newQuery := newQuery || commaString || DBMS_ASSERT.ENQUOTE_NAME(p_attrList(i).NAME) ;
commaString := ', ';
END LOOP;
END IF;
RETURN newQuery;
END GET_SUBLIST_QUERY;
------------------------------------------------------------------------------------
-- This function opens a cursor using the dbms_sql package. It uses this package so
-- it can get the desc_tab that describes the columns and their datatypes.
-- If we decide to keep the datatype information in the repository, then we can
-- modify this so it doesn't need to use the dbms_sql.
-- p_queryText Sql query to for which to open a cursor on.
-- p_idList A list of MGMT_DELTA_VALUE objects that describe the id columns.
-- p_extraBindList A list of values for additional bind variables
-- p_bindRight True if we want to use the rightside id values for bind. Otherwise
-- use the leftside id values.
-- p_rec_tab A structure describing the results.
-- Returns a cursor id.
FUNCTION GET_CURSOR_EXTRA_BINDS( p_queryText VARCHAR2,
p_idList MGMT_DELTA_VALUES,
p_extraBindList MGMT_NAME_VALUES,
p_bindRight BOOLEAN,
p_rec_tab out dbms_sql.desc_tab )
RETURN INTEGER
IS
curs INTEGER; -- Local cursor id
numrows INTEGER; -- Used to hold the results of the EXECUTE call.
col_cnt NUMBER; -- Holds the number of columns return from describe_columns.
numVal NUMBER; -- Used in the define_column call for number columns.
charVal VARCHAR2(4000); -- Used in the define_column call for char columns.
timeVal TIMESTAMP;-- Used in the define_column call for date/time columns.
rawVal RAW(4000); -- Used in the define_column call for RAW columns.
BEGIN
-- Open a dynamic cursor;
curs := DBMS_SQL.OPEN_CURSOR;
-- Parse the sql that was passed in.
DBMS_SQL.PARSE( curs, p_queryText, DBMS_SQL.NATIVE );
-- Bind the id columns.
FOR i IN p_idList.FIRST..p_idList.LAST
LOOP
IF p_bindRight
THEN
charVal := p_idList(i).right_value;
ELSE
charVal := p_idList(i).left_value;
END IF;
IF p_idList(i).type = NUMBER_TYPE_C THEN
numVal := charVal;
DBMS_SQL.BIND_VARIABLE(curs, ':b'||i, numVal);
ELSE IF p_idList(i).type = DATETIME_TYPE_C THEN
timeVal := charVal;
DBMS_SQL.BIND_VARIABLE(curs, ':b'||i, timeVal);
ELSE IF p_idList(i).type = RAW_TYPE_C THEN
rawVal := charVal;
DBMS_SQL.BIND_VARIABLE_RAW(curs, ':b'||i, rawVal);
ELSE
DBMS_SQL.BIND_VARIABLE(curs, ':b'||i, charVal);
END IF;
END IF;
END IF;
END LOOP;
--Now we do EXACTLY what we just did above. The catch is that we are binding
--the "additional" values. We also don't need to worry about the left v.
--right stuff
FOR i IN p_extraBindList.FIRST..p_extraBindList.LAST
LOOP
charVal := p_extraBindList(i).value;
IF p_extraBindList(i).type = NUMBER_TYPE_C THEN
numVal := charVal;
DBMS_SQL.BIND_VARIABLE(curs, ':x'||i, numVal);
ELSE IF p_extraBindList(i).type = DATETIME_TYPE_C THEN
timeVal := charVal;
DBMS_SQL.BIND_VARIABLE(curs, ':x'||i, timeVal);
ELSE IF p_extraBindList(i).type = RAW_TYPE_C THEN
rawVal := charVal;
DBMS_SQL.BIND_VARIABLE_RAW(curs, ':x'||i, rawVal);
ELSE
DBMS_SQL.BIND_VARIABLE(curs, ':x'||i, charVal);
END IF;
END IF;
END IF;
END LOOP;
-- Execute the sql statement
numrows := DBMS_SQL.EXECUTE(curs);
-- We want to examine the metadata to get datatypes.
DBMS_SQL.DESCRIBE_COLUMNS( curs, col_cnt, p_rec_tab );
-- Loop throught the record that describes the columns and call the
-- define column procedure.
FOR i in p_rec_tab.first..p_rec_tab.last
LOOP
CASE
WHEN p_rec_tab(i).col_type = DBMS_TYPES.TYPECODE_NUMBER THEN
DBMS_SQL.DEFINE_COLUMN( curs, i, numVal );
WHEN p_rec_tab(i).col_type = DBMS_TYPES.TYPECODE_DATE OR
p_rec_tab(i).col_type = DBMS_TYPES.TYPECODE_TIMESTAMP OR
p_rec_tab(i).col_type = DBMS_TYPES.TYPECODE_TIMESTAMP_TZ OR
p_rec_tab(i).col_type = DBMS_TYPES.TYPECODE_TIMESTAMP_LTZ THEN
DBMS_SQL.DEFINE_COLUMN( curs, i, timeVal );
ELSE
DBMS_SQL.DEFINE_COLUMN( curs, i, charVal, p_rec_tab(i).col_max_len );
END CASE;
END LOOP;
RETURN curs;
END GET_CURSOR_EXTRA_BINDS;
--------------------------------------------------------------------------------
-- This procedure takes first and second (left and right, new and old) queries
-- and various pieces of metadata to perform the query
-- in and to add any results to the p_deltaEntries parameter.
-- p_firstQueryText A query that returns a list of key columns, followed by a list of
-- column pairs. Each key column corresponds to a column in the key list and each
-- column pair corresponds to a column in the attrList. They must be in the same
-- order.
-- p_secondQueryText A query that returns a list of key columns, followed by a list of
-- column pairs. Each key column corresponds to a column in the key list and each
-- column pair corresponds to a column in the attrList. They must be in the same
-- order.
-- p_keyList A list of the column names of the key matching columns.
-- p_attrList A list of the column names of the column pairs being compared.
-- p_idList A list of column name, id pairs used to bind to the query.
-- Each is bound to a variable whose name is the index of the pair and
-- a l or r dependending if it is the left value or right (old) value.
-- p_keyValList A list of the key values. We need this list in order to identify
-- subsets of each cursor that have duplicate keys
-- p_flags Flags are RETURN_NONE, RETURN_DIFFS, RETURN_ALL.
-- p_deltaEntries A list of results so far accumulated. This procedure may add to that
-- list.
-- p_deltaSummary A structure in which to record the number of rows, each for
-- sames and different.
PROCEDURE GET_DELTAS_IGNORE_DUPLICATES( p_firstQueryText VARCHAR2,
p_secondQueryText VARCHAR2,
p_collectionType VARCHAR2,
p_keyList VARCHAR2_TABLE,
p_attrList MGMT_DELTA_VALUES,
p_idList MGMT_DELTA_VALUES,
p_keyValList MGMT_NAME_VALUES,
p_flags SMALLINT,
p_deltaRecorder in out MGMT_DELTA_RECORDER,
p_deltaSummary in out MGMT_DELTA_SUMMARY )
IS
fCurs INTEGER; -- Cursor Id for first query
sCurs INTEGER; -- Cursor Id for second query
fNumrows INTEGER; -- Numrows used to get return value from execute call.
sNumrows INTEGER; -- Numrows used to get return value from execute call.
col_cnt INTEGER; -- The column count from describe_columns
frec_tab dbms_sql.desc_tab; -- The table that has describe_column information
srec_tab dbms_sql.desc_tab; -- The table that has describe_column information
fCharVal VARCHAR2(4000); -- Used to hold left side character column data.
fNumVal NUMBER; -- Used to hold left side number column data.
fTimeVal TIMESTAMP; -- Used to hold left side timestamp column data.
sCharVal VARCHAR2(4000); -- Used to hold left side character column data.
sNumVal NUMBER; -- Used to hold left side number column data.
sTimeVal TIMESTAMP; -- Used to hold left side timestamp column data.
fDone BOOLEAN; -- Used to indicate that cursor is exhausted.
sDone BOOLEAN;
fKeyCols MGMT_NAME_VALUES; -- To hold key for each entry.
sKeyCols MGMT_NAME_VALUES; -- To hold key for each entry.
deltaEntry MGMT_DELTA_ENTRY_OBJ;
BEGIN
-- Get first and second query Cursor.
fCurs := GET_CURSOR_EXTRA_BINDS( p_firstQueryText,
p_idList,
p_keyValList,
FALSE,
frec_tab );
sCurs := GET_CURSOR_EXTRA_BINDS( p_firstQueryText,
p_idList,
p_keyValList,
TRUE,
srec_tab );
-- LOOP until both left and right cursors are exhausted.
fKeyCols := GET_KEY_COLS( fCurs, p_keyList, frec_tab );
sKeyCols := GET_KEY_COLS( sCurs, p_keyList, srec_tab );
LOOP
fDone := fKeyCols IS NULL;
sDone := sKeyCols IS NULL;
deltaEntry := NULL;
IF fDone AND sDone THEN EXIT; END IF;
-- comVal = 0 then same. <0 then f < s, >0 then r > s. If fDone or sDone
-- then we don't care but will make it zero so left and right test don't
-- get fooled.
-- If left is exhausted and not right, or left > right then
IF fDone THEN
-- Add a right only and advance the right cursor.
-- FIrst get the keyCols
deltaEntry :=
GET_ONE_SIDE_DELTA( p_collectionType,
sKeyCols,
p_attrList,
sCurs,
srec_tab,
RIGHT_ONLY_C,
p_deltaSummary,
p_flags );
-- get next row.
sKeyCols := GET_KEY_COLS( sCurs, p_keyList, srec_tab );
-- Else if right is exhausted and not left, or right > left then
ELSE IF sDone THEN
-- Add a left only and advance the left cursor.
-- FIrst get the keyCols
deltaEntry :=
GET_ONE_SIDE_DELTA( p_collectionType,
fKeyCols,
p_attrList,
fCurs,
frec_tab,
LEFT_ONLY_C,
p_deltaSummary,
p_flags );
-- get next row.
fKeyCols := GET_KEY_COLS( fCurs, p_keyList, frec_tab );
-- Else if left = right, then compare the left and right diff columns.
ELSE
deltaEntry :=
GET_BOTH_SIDE_DELTA( p_collectionType,
fKeyCols,
p_attrList,
fCurs,
sCurs,
frec_tab,
srec_tab,
p_deltaSummary,
p_flags );
-- get next rowx.
fKeyCols := GET_KEY_COLS( fCurs, p_keyList, frec_tab );
sKeyCols := GET_KEY_COLS( sCurs, p_keyList, srec_tab );
END IF; END IF;
IF deltaEntry IS NOT NULL
THEN
p_deltaRecorder.RECORD_DELTA( deltaEntry );
END IF;
END LOOP;
-- Close both cursors.
dbms_sql.close_cursor(fCurs);
dbms_sql.close_cursor(sCurs);
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(fCurs);
dbms_sql.close_cursor(sCurs);
RAISE;
END GET_DELTAS_IGNORE_DUPLICATES;
------------------------------------------------------------------------------------
-- This procedure takes first and second (left and right, new and old) queries
-- and various pieces of metadata to perform the query
-- in and to add any results to the p_deltaEntries parameter.
-- p_firstQueryText A query that returns a list of key columns, followed by a list of
-- column pairs. Each key column corresponds to a column in the key list and each
-- column pair corresponds to a column in the attrList. They must be in the same
-- order.
-- p_secondQueryText A query that returns a list of key columns, followed by a list of
-- column pairs. Each key column corresponds to a column in the key list and each
-- column pair corresponds to a column in the attrList. They must be in the same
-- order.
-- p_keyList A list of the column names of the key matching columns.
-- p_attrList A list of the column names of the column pairs being compared.
-- p_idList A list of column name, id pairs used to bind to the query.
-- Each is bound to a variable whose name is the index of the pair and
-- a l or r dependending if it is the left value or right (old) value.
-- p_flags Flags are RETURN_NONE, RETURN_DIFFS, RETURN_ALL.
-- p_deltaEntries A list of results so far accumulated. This procedure may add to that
-- list.
-- p_deltaSummary A structure in which to record the number of rows, each for
-- sames and different.
PROCEDURE GET_DELTAS( p_firstQueryText VARCHAR2,
p_secondQueryText VARCHAR2,
p_collectionType VARCHAR2,
p_keyList VARCHAR2_TABLE,
p_attrList MGMT_DELTA_VALUES,
p_idList MGMT_DELTA_VALUES,
p_flags SMALLINT,
p_deltaRecorder in out MGMT_DELTA_RECORDER,
p_deltaSummary in out MGMT_DELTA_SUMMARY )
IS
fCurs INTEGER; -- Cursor Id for first query
sCurs INTEGER; -- Cursor Id for second query
fNumrows INTEGER; -- Numrows used to get return value from execute call.
sNumrows INTEGER; -- Numrows used to get return value from execute call.
col_cnt INTEGER; -- The column count from describe_columns
frec_tab dbms_sql.desc_tab; -- The table that has describe_column information
srec_tab dbms_sql.desc_tab; -- The table that has describe_column information
fCharVal VARCHAR2(4000); -- Used to hold left side character column data.
fNumVal NUMBER; -- Used to hold left side number column data.
fTimeVal TIMESTAMP; -- Used to hold left side timestamp column data.
sCharVal VARCHAR2(4000); -- Used to hold left side character column data.
sNumVal NUMBER; -- Used to hold left side number column data.
sTimeVal TIMESTAMP; -- Used to hold left side timestamp column data.
fDone BOOLEAN; -- Used to indicate that cursor is exhausted.
sDone BOOLEAN;
fKeyCols MGMT_NAME_VALUES; -- To hold key for each entry.
sKeyCols MGMT_NAME_VALUES; -- To hold key for each entry.
comVal NUMBER;
deltaEntry MGMT_DELTA_ENTRY_OBJ;
tempDeltaEntry MGMT_DELTA_ENTRY_OBJ;
tempFKeyCols MGMT_NAME_VALUES;
tempSKeyCols MGMT_NAME_VALUES;
tempFDone BOOLEAN;
tempSDone BOOLEAN;
newFirstQueryText VARCHAR2(4000);
newSecondQueryText VARCHAR2(4000);
BEGIN
-- Get first and second query Cursor.
fCurs := GET_CURSOR( p_firstQueryText,
p_idList,
FALSE,
frec_tab );
sCurs := GET_CURSOR( p_firstQueryText,
p_idList,
TRUE,
srec_tab );
-- LOOP until both left and right cursors are exhausted.
fKeyCols := GET_KEY_COLS( fCurs, p_keyList, frec_tab );
sKeyCols := GET_KEY_COLS( sCurs, p_keyList, srec_tab );
LOOP
fDone := fKeyCols IS NULL;
sDone := sKeyCols IS NULL;
deltaEntry := NULL;
IF fDone AND sDone THEN EXIT; END IF;
-- comVal = 0 then same. <0 then f < s, >0 then r > s. If fDone or sDone
-- then we don't care but will make it zero so left and right test don't
-- get fooled.
comVal := 0;
IF NOT (fDone OR sDone) THEN
comVal := COMPARE_KEYS( fKeyCols, sKeyCols );
END IF;
-- If left is exhausted and not right, or left > right then
IF fDone OR comVal > 0 THEN
-- Add a right only and advance the right cursor.
-- FIrst get the keyCols
deltaEntry :=
GET_ONE_SIDE_DELTA( p_collectionType,
sKeyCols,
p_attrList,
sCurs,
srec_tab,
RIGHT_ONLY_C,
p_deltaSummary,
p_flags );
-- get next row.
sKeyCols := GET_KEY_COLS( sCurs, p_keyList, srec_tab );
-- Else if right is exhausted and not left, or right > left then
ELSE IF sDone OR comVal < 0 THEN
-- Add a left only and advance the left cursor.
-- FIrst get the keyCols
deltaEntry :=
GET_ONE_SIDE_DELTA( p_collectionType,
fKeyCols,
p_attrList,
fCurs,
frec_tab,
LEFT_ONLY_C,
p_deltaSummary,
p_flags );
-- get next row.
fKeyCols := GET_KEY_COLS( fCurs, p_keyList, frec_tab );
-- Else if left = right, then compare the left and right diff columns.
ELSE
tempDeltaEntry :=
GET_BOTH_SIDE_DELTA( p_collectionType,
fKeyCols,
p_attrList,
fCurs,
sCurs,
frec_tab,
srec_tab,
p_deltaSummary,
p_flags );
-- get next rowx.
tempFKeyCols := GET_KEY_COLS( fCurs, p_keyList, frec_tab );
tempSKeyCols := GET_KEY_COLS( sCurs, p_keyList, srec_tab );
--if the temporary delta entry is a diff, we don't necessarily want to
--record it. What we need to do is to check if the next set of keys is
--identical. The keys from the next row in each cursor need to be equal
--to each other and to the keys from this row.
tempFDone := tempFKeyCols IS NULL;
tempSDone := tempSKeyCols IS NULL;
IF (NOT (tempFDone OR tempSDone))
AND (COMPARE_KEYS(tempFKeyCols, tempSKeyCols) = 0)
AND (COMPARE_KEYS(tempFKeyCols, fKeyCols) = 0) THEN
--if we are here, it means that the next row has the same set of
--keys as the current row. So we need to get the deltas for the
--sub-list.
newFirstQueryText := GET_SUBLIST_QUERY(p_firstQueryText,
p_attrList,
tempFKeyCols);
newSecondQueryText := GET_SUBLIST_QUERY(p_secondQueryText,
p_attrList,
tempFKeyCols);
--in theory, this should be all we need to do
GET_DELTAS_IGNORE_DUPLICATES( newFirstQueryText,
newSecondQueryText,
p_collectionType,
p_keyList,
p_attrList,
p_idList,
tempFKeyCols,
p_flags,
p_deltaRecorder,
p_deltaSummary);
--now we just need ot advance the real cursors until we get past this
--set of keys. We have to do both INDEPENDENTLY since there may be
--actual insertions or deletions
fKeyCols := GET_KEY_COLS(fCurs, p_keyList, frec_tab);
sKeyCols := GET_KEY_COLS(sCurs, p_keyList, srec_tab);
fDone := fKeyCols IS NULL;
sDone := sKeyCols IS NULL;
WHILE(NOT fDone) AND (COMPARE_KEYS(tempFKeyCols, fKeyCols) = 0)
LOOP
fKeyCols := GET_KEY_COLS(fCurs, p_keyList, frec_tab);
fDone := fKeyCols IS NULL;
END LOOP;
WHILE (NOT sDOne) AND (COMPARE_KEYS(tempSKeyCols, sKeyCols) = 0)
LOOP
sKeyCols := GET_KEY_COLS(sCurs, p_keyList, srec_tab);
sDone := sKeyCols IS NULL;
END LOOP;
ELSE
fKeyCols := tempFKeyCols;
sKeyCols := tempSKeyCols;
deltaEntry := tempDeltaEntry;
END IF;
END IF; END IF;
IF deltaEntry IS NOT NULL
THEN
p_deltaRecorder.RECORD_DELTA( deltaEntry );
END IF;
END LOOP;
-- Close both cursors.
dbms_sql.close_cursor(fCurs);
dbms_sql.close_cursor(sCurs);
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(fCurs);
dbms_sql.close_cursor(sCurs);
RAISE;
END GET_DELTAS;
--------------------------------------------------------------------------------
-- The following methods are used to create a sql query for doing comparisons.
-- The following methods are used to build the string that will be substituted
-- into the query templates
-- This returns the select list for
-- the comparison which includes the matching cols and the diff cols.
FUNCTION GET_TC1_STRING( p_keyList VARCHAR2_TABLE, p_attrList MGMT_DELTA_VALUES )
RETURN VARCHAR2
IS
selectList VARCHAR2(1000) := '';
comma VARCHAR2(10) := '';
BEGIN
IF p_keyList IS NOT NULL AND p_keyList.COUNT > 0
THEN
FOR i IN p_keyList.FIRST..p_keyList.LAST
LOOP
selectList := selectList || comma || p_keyList(i);
comma := ', ';
END LOOP;
END IF;
IF p_attrList IS NOT NULL AND p_attrList.COUNT > 0
THEN
FOR i IN p_attrList.FIRST..p_attrList.LAST
LOOP
selectList := selectList || comma || p_attrList(i).NAME ;
comma := ', ';
END LOOP;
END IF;
RETURN selectList;
END GET_TC1_STRING;
-- This function compares each column in the idlist with its bind var.
FUNCTION GET_TC3_STRING( p_idList MGMT_DELTA_VALUES )
RETURN VARCHAR2
IS
idPred VARCHAR2(1000) := '';
andtxt VARCHAR2(10) := ' ';
BEGIN
FOR i IN p_idList.FIRST..p_idList.LAST
LOOP
-- Only append the id if the status field is not null
IF p_idList(i).STATUS IS NULL THEN
idPred := idPred || andtxt || p_idList(i).NAME || ' = :b' || i;
andtxt := ' and ';
END IF;
END LOOP;
RETURN idPred;
END GET_TC3_STRING;
-- This function is like TC1 except that it only returns the select list for
-- the first (not the second attrs as in TC1). This is used for the existence
-- query creation.
FUNCTION GET_TC4_STRING( p_keyList VARCHAR2_TABLE )
RETURN VARCHAR2
IS
selectList VARCHAR2(1000) := '';
-- We want the sorting to be character sort to be consistent with the
-- way comparisons are done when walking the two result sets, so we
-- explicitly to a to_char...
comma VARCHAR2(10) := 'to_char(';
BEGIN
IF p_keyList IS NOT NULL AND p_keyList.COUNT > 0
THEN
selectList := ' order by ';
FOR i IN p_keyList.FIRST..p_keyList.LAST
LOOP
selectList := selectList || comma || p_keyList(i) || ')';
comma := ', to_char(';
END LOOP;
END IF;
RETURN selectList;
END GET_TC4_STRING;
-- GET_QUERY
-- Createa a query given the base query or table name. See EACH_QUERY_TEMPLATE
-- for the format of the query. It essentially returns the key and diff columns
-- id'd by the id columns, order by the key columns. (Character sort order).
FUNCTION GET_QUERY( p_queryText VARCHAR2,
p_keyList VARCHAR2_TABLE,
p_attrList MGMT_DELTA_VALUES,
p_idList MGMT_DELTA_VALUES )
RETURN VARCHAR2
IS
-- init the query using the exists query template;
query VARCHAR2(4000) := EACH_QUERY_TEMPLATE;
tempstr VARCHAR2(1000);
BEGIN
-- Now start substituting the appropriate values
-- First add the select list.
tempstr := GET_TC1_STRING( p_keyList, p_attrList );
query := REPLACE( query, TC1, tempstr );
-- Now plug in the query, table, or view.
tempstr := GET_TC2_STRING(p_queryText);
query := REPLACE( query, TC2, tempstr);
-- Not add the id column match against the binds.
tempstr := GET_TC3_STRING( p_idList );
query := REPLACE( query, TC3, tempstr );
-- And finally, the order-by match column list.
tempstr := GET_TC4_STRING( p_keyList);
query := REPLACE( query, TC4, tempstr );
RETURN query;
END GET_QUERY;
-------------------------------------------------------------------------------------
-- DO_DIFF
--
-- Execute all the queries represented by the members of the MGMT_DELTA_QUERIES
-- collectio passed into this function.
PROCEDURE DO_DIFF( p_deltaQueries MGMT_DELTA_QUERIES,
p_deltaRecorder in out MGMT_DELTA_RECORDER )
IS
firstQueryText VARCHAR2(8000);
secondQueryText VARCHAR2(8000);
summaryObj MGMT_DELTA_SUMMARY;
BEGIN
FOR i IN p_deltaQueries.FIRST..p_deltaQueries.LAST
LOOP
-- Set up the queries for each side.
firstQueryText := GET_QUERY( p_deltaQueries(i).FIRST_QUERY_TEXT,
p_deltaQueries(i).KEY_LIST,
p_deltaQueries(i).ATTRIBUTE_LIST,
p_deltaQueries(i).ID_LIST );
-- If the second query is null, then it should be the same as the first.
IF p_deltaQueries(i).SECOND_QUERY_TEXT IS NULL THEN
secondQueryText := firstQueryText;
ELSE
secondQueryText := GET_QUERY( p_deltaQueries(i).SECOND_QUERY_TEXT,
p_deltaQueries(i).KEY_LIST,
p_deltaQueries(i).ATTRIBUTE_LIST,
p_deltaQueries(i).ID_LIST );
END IF;
-- Set up a new summary object for this sub-comparison
summaryObj := MGMT_DELTA_SUMMARY( p_deltaQueries(i).COLLECTION_TYPE,
0,
0,
0,
VARCHAR2_TABLE());
BEGIN
-- Do the comparison.
GET_DELTAS( firstQueryText,
secondQueryText,
p_deltaQueries(i).COLLECTION_TYPE,
p_deltaQueries(i).KEY_LIST,
p_deltaQueries(i).ATTRIBUTE_LIST,
p_deltaQueries(i).ID_LIST,
p_deltaQueries(i).FLAGS,
p_deltaRecorder,
summaryObj );
EXCEPTION
WHEN OTHERS THEN
-- err_num := SQLCODE;
summaryObj.ERRORS.EXTEND;
summaryObj.ERRORS(summaryObj.ERRORS.LAST) := SUBSTR(SQLERRM, 1, 256);
summaryObj.ERRORS.EXTEND;
summaryObj.ERRORS(summaryObj.ERRORS.LAST) := SUBSTR(firstQueryText, 1, 256);
IF p_deltaQueries(i).SECOND_QUERY_TEXT IS NOT NULL THEN
summaryObj.ERRORS(summaryObj.ERRORS.LAST) := SUBSTR(secondQueryText, 1, 256);
END IF;
END;
p_deltaRecorder.RECORD_SUMMARY( summaryObj );
END LOOP;
END DO_DIFF;
---------------------------------------------------------------------------------
-- SAVE_COMPARISON_DELTA
--
-- Save the delta information in the repository given the comparison id. Note
-- thar this does not update the summary information.
--
-- PARAMETERS
--
-- p_comparisonGuid The comparison id under which to save this delta.
-- p_collectionType The logical table name to which this diff refers.
-- p_status SAME, DIFFERENT, LEFT_ONLY, RIGHT_ONLY
-- p_keyList The key information
-- p_diffList The non-key columns with differences.
--
---------------------------------------------------------------------------------
-- SAVE_COMPARISON_RESULTS
--
-- Do a comparison based on the metadata described in the list of logical table
-- information. This version of do_diff_queries is meant for saved comparisons.
-- The saved comparison's guid is returned.
--
-- PARAMETERS
--
-- p_deltaRecorder Holds the comparison results.
-- p_comparisonType The type of the comparison. For config fw, this is the
-- snapshot_type.
-- p_compTargetType The type of the target of the comparison.
-- p_compTargetProperties A list of comparison target properties, which are
-- name, left_value, right_value triplets.
-- session_id - identifies the session in which this comparison is saved.
-- RETURNS A guid pointing to the comparison result in the
-- MGMT_DELTA_SAVED_COMPARISONS table.
--
FUNCTION START_SAVE_COMPARISON_RESULTS( p_comparisonType VARCHAR2,
p_compTargetType VARCHAR2,
p_compTargetProperties COMPARISON_PROPERTIES,
p_session_id RAW DEFAULT SYS_GUID())
RETURN RAW
IS
l_tempGuid RAW(16);
l_tempKeyGuid RAW(16);
l_deltaValues MGMT_DELTA_VALUES;
BEGIN
l_tempGuid := sys_guid();
-- First write a save comparison entry
INSERT INTO mgmt_delta_saved_comparison( DELTA_COMP_GUID,
COMPARISON_TYPE,
TARGET_TYPE,
SESSION_ID)
VALUES ( l_tempGuid,
p_comparisonType,
p_compTargetType,
p_session_id);
-- Write out the comparison properties
IF p_compTargetProperties IS NOT NULL AND
p_compTargetProperties.COUNT > 0
THEN
FOR h IN p_compTargetProperties.FIRST..p_compTargetProperties.LAST
LOOP
INSERT INTO MGMT_DELTA_COMP_PROPERTIES( DELTA_COMP_GUID,
NAME,
LEFT_VALUE,
RIGHT_VALUE )
VALUES( l_tempGuid,
p_compTargetProperties(h).NAME,
p_compTargetProperties(h).LEFT_VALUE,
p_compTargetProperties(h).RIGHT_VALUE );
END LOOP;
END IF;
RETURN l_tempGuid;
END START_SAVE_COMPARISON_RESULTS;
-------------------------------------------------------------------------------------
-- DO_DIFF_QUERIES
--
-- Do a comparison based on the metadata described in the list of logical table
-- information. Return two cursors, one which is a list of all of the differences
-- requested in the metadata, the other is a summary of the findings.
--
-- PARAMETERS
--
-- p_deltaQueries A list of MGMT_DELTA_QUERY objects, each of which describes
-- a logical table, the details of which are used to compare
-- two snapshots.
-- p_deRef A ref cursor to the MGMT_DELTA_ENTRY_OBJ records.
-- p_dsRef A ref cursor to the MGMT_DELTA_SUMMARY records.
-- session_id - identifies the session in which this comparison is saved.
--
PROCEDURE DO_DIFF_QUERIES( p_deltaQueries MGMT_DELTA_QUERIES,
p_deRef out DELTA_ENTRY_REF,
p_dsRef out DELTA_SUMMARY_REF,
p_session_id RAW DEFAULT SYS_GUID())
IS
deltaRecorder MGMT_DELTA_ENTRY_RECORDER;
l_compGuid RAW(16);
BEGIN
l_compGuid := START_SAVE_COMPARISON_RESULTS( NULL,
NULL,
NULL,
p_session_id);
deltaRecorder := MGMT_DELTA_ENTRY_RECORDER( sysdate, l_compGuid );
DO_DIFF( p_deltaQueries, deltaRecorder );
RETRIEVE_SAVED_COMPARISON( l_compGuid,
p_deRef,
p_dsRef );
END DO_DIFF_QUERIES;
-------------------------------------------------------------------------------------
-- DO_DIFF_QUERIES_AND_SAVE
--
-- Do a comparison based on the metadata described in the list of logical table
-- information. This version of do_diff_queries is meant for saved comparisons.
-- The saved comparison's guid is returned.
--
-- PARAMETERS
--
-- p_deltaQueries A list of MGMT_DELTA_QUERY objects, each of which describes
-- a logical table, the details of which are used to compare
-- two snapshots.
-- p_comparisonType The type of the comparison. For config fw, this is the
-- snapshot_type.
-- p_compTargetType The type of the target of the comparison.
-- p_compTargetProperties A list of properties that describe the two targets of
-- the comparison.
-- session_id - identifies the session in which this comparison is saved.
-- RETURNS A guid pointing to the comparison result in the
-- MGMT_DELTA_SAVED_COMPARISONS table.
--
FUNCTION DO_DIFF_QUERIES_AND_SAVE( p_deltaQueries MGMT_DELTA_QUERIES,
p_comparisonType VARCHAR2,
p_compTargetType VARCHAR2,
p_compTargetProperties COMPARISON_PROPERTIES,
p_session_id RAW DEFAULT SYS_GUID())
RETURN RAW
IS
deltaRecorder MGMT_DELTA_ENTRY_RECORDER;
l_compGuid RAW(16);
BEGIN
l_compGuid := START_SAVE_COMPARISON_RESULTS( p_comparisonType,
p_compTargetType,
p_compTargetProperties,
p_session_id);
deltaRecorder := MGMT_DELTA_ENTRY_RECORDER( sysdate, l_compGuid );
DO_DIFF( p_deltaQueries, deltaRecorder );
return l_compGuid;
END DO_DIFF_QUERIES_AND_SAVE;
--------------------------------------------------------------------------------
FUNCTION READ_DELTAS( p_status VARCHAR2,
p_collection_type VARCHAR2,
p_key_guid RAW)
RETURN MGMT_DELTA_ENTRY_OBJ
IS
key_values MGMT_NAME_VALUES;
CURSOR c2 IS
SELECT name, value, type
FROM mgmt_delta_comp_key_cols
WHERE key_guid = p_key_guid;
key_rec c2%ROWTYPE;
delta_values MGMT_DELTA_VALUES;
CURSOR c3 IS
SELECT name, left_value, right_value, status, type
FROM mgmt_delta_comp_delta_details
WHERE key_guid = p_key_guid
order by name;
detail_rec c3%ROWTYPE;
BEGIN
-- Get the key values for this delta entry.
key_values := MGMT_NAME_VALUES();
FOR key_rec IN c2
LOOP
key_values.EXTEND;
key_values(key_values.LAST) := MGMT_NAME_VALUE( key_rec.name,
key_rec.value,
key_rec.type );
END LOOP;
-- Now get the delta value details.
delta_values := MGMT_DELTA_VALUES();
FOR detail_rec IN C3
LOOP
delta_values.EXTEND;
delta_values(delta_values.LAST) := MGMT_DELTA_VALUE( detail_rec.name,
detail_rec.left_value,
detail_rec.right_value,
detail_rec.status,
detail_rec.type );
END LOOP;
RETURN MGMT_DELTA_ENTRY_OBJ( p_status,
p_collection_type,
key_values,
delta_values );
END READ_DELTAS;
--------------------------------------------------------------------------------
-- RETRIEVE_SAVED_COMPARISON
-- p_diffGuid The id of the saved comparison.
-- p_deRef A ref cursor to the MGMT_DELTA_ENTRY_OBJ records.
-- p_dsRef A ref cursor to the MGMT_DELTA_SUMMARY records.
-- p_collectionTypes An optional list of comparison type to restrict the returned
-- results to.
PROCEDURE RETRIEVE_SAVED_COMPARISON( p_diffGuid RAW,
p_deRef out DELTA_ENTRY_REF,
p_dsRef out DELTA_SUMMARY_REF,
p_collectionTypes VARCHAR2_TABLE := NULL )
IS
l_deltaEntries MGMT_DELTA_ENTRIES;
CURSOR c1 IS
SELECT status, collection_type, key_guid
FROM
mgmt_delta_comparison_deltas d,
TABLE( p_collectionTypes ) t
WHERE d.collection_type = t.column_value
AND delta_comp_guid = p_diffGuid;
CURSOR c1All IS
SELECT status, collection_type, key_guid
FROM
mgmt_delta_comparison_deltas
WHERE delta_comp_guid = p_diffGuid;
delta_rec c1%ROWTYPE;
l_deltaEntry MGMT_DELTA_ENTRY_OBJ;
BEGIN
l_deltaEntries := MGMT_DELTA_ENTRIES();
IF p_collectionTypes IS NULL THEN
FOR delta_rec IN c1All
LOOP
l_deltaEntry := READ_DELTAS( delta_rec.status,
delta_rec.collection_type,
delta_rec.key_guid );
-- Now make a deltaEntry and place it in the list.
l_deltaEntries.EXTEND;
l_deltaEntries(l_deltaEntries.LAST) := l_deltaEntry;
END LOOP;
OPEN p_dsRef FOR SELECT COLLECTION_TYPE, DIFFERENT_COUNT, LEFT_COUNT, RIGHT_COUNT, ERRORS
FROM MGMT_DELTA_COMP_SUMMARIES WHERE DELTA_COMP_GUID = p_diffGuid;
ELSE
FOR delta_rec IN c1
LOOP
l_deltaEntry := READ_DELTAS( delta_rec.status,
delta_rec.collection_type,
delta_rec.key_guid );
-- Now make a deltaEntry and place it in the list.
l_deltaEntries.EXTEND;
l_deltaEntries(l_deltaEntries.LAST) := l_deltaEntry;
END LOOP;
OPEN p_dsRef FOR SELECT COLLECTION_TYPE, DIFFERENT_COUNT, LEFT_COUNT, RIGHT_COUNT, ERRORS
FROM MGMT_DELTA_COMP_SUMMARIES s, TABLE(p_collectionTypes) t
WHERE s.COLLECTION_TYPE = t.column_value
AND DELTA_COMP_GUID = p_diffGuid;
END IF;
OPEN p_deRef FOR SELECT * FROM TABLE(CAST(l_deltaEntries AS MGMT_DELTA_ENTRIES));
END RETRIEVE_SAVED_COMPARISON;
--------------------------------------------------------------------------------
-- DO_HISTORY_DIFFS
--
-- This procedure does a comparison for the purpose of saving the results in
-- the history tables. It is essentially the same as do_diff_queries except
-- where the results go.
--
-- PARAMETERS
--
-- p_deltaQueries A list of MGMT_DELTA_QUERY objects, each of which describes
-- a logical table, the details of which are used to compare
-- two snapshots, one new current, one the previous current.
-- The results of the comparison are recorded as history entries
-- in the various MGMT_DELTA history tables.
-- p_snaptime This is the date-time of when the new current was actual
-- captured. It is used to record the time that the changes
-- were detected.
--
PROCEDURE DO_HISTORY_DIFFS( p_deltaQueries MGMT_DELTA_QUERIES,
p_snaptime DATE )
IS
deltaRecorder MGMT_HISTORY_ENTRY_RECORDER;
BEGIN
deltaRecorder := MGMT_HISTORY_ENTRY_RECORDER( p_snaptime );
DO_DIFF( p_deltaQueries, deltaRecorder );
END DO_HISTORY_DIFFS;
------------------------------------------------------------------------------------
-- PURGE_HISTORY_RECORDS
-- BEGIN Purge policies callback procedures
PROCEDURE ECM_HISTORY_PURGE(pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS) IS
TYPE p_rowid_list_type IS
TABLE OF UROWID
INDEX BY BINARY_INTEGER;
l_purge_rowids p_rowid_list_type;
l_rows_purged NUMBER := 0;
-- Snapshot delta entries
CURSOR purge_delta_snaps (c_purge_till_time DATE) IS
SELECT snap.ROWID
FROM MGMT_DELTA_SNAP snap
WHERE snap.NEW_LEFT_TIMESTAMP < c_purge_till_time;
CURSOR purge_delta_entries (c_purge_till_time DATE) IS
SELECT ROWID
FROM MGMT_DELTA_ENTRY e
WHERE DELTA_TIME < c_purge_till_time
AND NOT EXISTS ( SELECT * FROM MGMT_DELTA_SNAP s
WHERE e.DELTA_GUID = s.DELTA_GUID);
-- Unused delta ids.
CURSOR purge_delta_ids (c_purge_till_time DATE) IS
SELECT ROWID
FROM MGMT_DELTA_IDS id
WHERE (id.LAST_UPDATED_TIME < c_purge_till_time) AND
NOT EXISTS ( SELECT * FROM MGMT_DELTA_ENTRY e
WHERE id.ROW_GUID = e.ROW_GUID);
BEGIN
BEGIN
OPEN purge_delta_snaps(pcb_params.purge_upto_time);
LOOP
-- Bulk collect row ids
FETCH purge_delta_snaps BULK COLLECT INTO l_purge_rowids LIMIT 500;
-- Exit when no more row ids fetched
EXIT WHEN l_purge_rowids.COUNT <= 0;
-- BULK delete
FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST
DELETE FROM MGMT_DELTA_SNAP WHERE ROWID = l_purge_rowids(i);
COMMIT;
l_purge_rowids.DELETE;
END LOOP;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
-- If any error occurs before the snap or ids are purged, they will get purged
-- on a later date.
-- Close the cursor if open
IF (purge_delta_snaps%ISOPEN) THEN
CLOSE purge_delta_snaps;
END IF;
BEGIN
OPEN purge_delta_entries(pcb_params.purge_upto_time);
LOOP
-- Bulk collect row ids
FETCH purge_delta_entries BULK COLLECT INTO l_purge_rowids LIMIT 500;
-- Exit when no more row ids fetched
EXIT WHEN l_purge_rowids.COUNT <= 0;
-- BULK delete
FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST
DELETE FROM MGMT_DELTA_ENTRY WHERE ROWID = l_purge_rowids(i);
COMMIT;
l_rows_purged := l_rows_purged + l_purge_rowids.COUNT;
l_purge_rowids.DELETE;
END LOOP;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
-- Close the cursor if open
IF (purge_delta_entries%ISOPEN) THEN
CLOSE purge_delta_entries;
END IF;
BEGIN
OPEN purge_delta_ids(pcb_params.purge_upto_time);
LOOP
-- Bulk collect row ids
FETCH purge_delta_ids BULK COLLECT INTO l_purge_rowids LIMIT 500;
-- Exit when no more row ids fetched
EXIT WHEN l_purge_rowids.COUNT <= 0;
-- BULK delete
FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST
DELETE FROM MGMT_DELTA_IDS WHERE ROWID = l_purge_rowids(i);
COMMIT;
l_purge_rowids.DELETE;
END LOOP;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
-- Close the cursor if open
IF (purge_delta_ids%ISOPEN) THEN
CLOSE purge_delta_ids;
END IF;
pcb_params.callback_result := 0;
pcb_params.rows_processed := l_rows_purged;
pcb_params.error_code := 0;
pcb_params.error_msg := NULL;
END ECM_HISTORY_PURGE;
------------------------------------------------------------------------------
--
-- 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.
--
-- RETURNS The number of history entries purged.
--
FUNCTION PURGE_HISTORY( p_numberOfDays NUMBER := 0,
p_snapshotType VARCHAR2 := NULL,
p_targetType VARCHAR2 := NULL,
p_targetName VARCHAR2 := NULL )
RETURN NUMBER
IS
beforeDate DATE;
rowcount NUMBER := 0;
l_num_rows NUMBER := 0;
l_num_rows1 NUMBER := 0;
sqlText VARCHAR(256);
l_row_guids T_ROW_GUID_TABLE;
BEGIN
-- Calculate the correct date to remove before.
beforeDate := SYSDATE - p_numberOfDays;
-- Delete from the delta_snap table older than the date.
sqlText := 'DELETE FROM MGMT_DELTA_SNAP WHERE DELTA_TIMESTAMP < :bd';
IF p_snapshotType IS NOT NULL
THEN
sqlText := sqlText || ' AND SNAPSHOT_TYPE = ' ||
em_check.enquote_literal(replace(p_snapshotType, '''', ''''''));
END IF;
IF p_targetType IS NOT NULL
THEN
sqlText := sqlText || ' AND TARGET_TYPE = ' ||
em_check.enquote_literal(replace(p_targetType, '''', ''''''));
END IF;
IF p_targetName IS NOT NULL
THEN
sqlText := sqlText || ' AND NEW_LEFT_TARGET_NAME = ' ||
em_check.enquote_literal(replace(p_targetName, '''', ''''''));
END IF;
sqlText := sqlText || ' AND ROWNUM <= ' || MGMT_GLOBAL.MAX_COMMIT;
-- Delete rows from the table in chunks of size MAX_COMMIT
LOOP
EXECUTE IMMEDIATE sqlText USING beforeDate;
l_num_rows := SQL%ROWCOUNT;
COMMIT;
EXIT WHEN l_num_rows < MGMT_GLOBAL.MAX_COMMIT;
END LOOP;
-- Remove all the delta entries prior to the date only if the filters are all
-- null in chunks of size MAX_COMMIT.
LOOP
IF p_snapshotType IS NULL
AND p_targetType IS NULL
AND p_targetName IS NULL
THEN
DELETE FROM MGMT_DELTA_ENTRY
WHERE DELTA_TIME < beforeDate AND ROWNUM <= MGMT_GLOBAL.MAX_COMMIT
RETURNING ROW_GUID BULK COLLECT INTO l_row_guids;
l_num_rows := SQL%ROWCOUNT;
ELSE
-- There are filters so don't remove the entries that have NO_DELTA as their
-- delta guid. Remove all other whose delta_guid does not have a corresponding
-- entry in the DELTA_SNAP table. Those were removed based on the filters.
DELETE FROM MGMT_DELTA_ENTRY e
WHERE DELTA_TIME < beforeDate
AND DELTA_GUID <> NO_DELTA
AND NOT EXISTS (SELECT * FROM MGMT_DELTA_SNAP s
WHERE e.DELTA_GUID = s.DELTA_GUID
AND ROWNUM <= 1)
AND rownum <= MGMT_GLOBAL.MAX_COMMIT
RETURNING ROW_GUID BULK COLLECT INTO l_row_guids;
l_num_rows := SQL%ROWCOUNT;
END IF;
FORALL i IN l_row_guids.FIRST..l_row_guids.LAST
DELETE FROM MGMT_DELTA_IDS id WHERE id.ROW_GUID = l_row_guids(i)
AND NOT EXISTS (select * from mgmt_delta_entry e
where e.row_guid = id.row_guid and rownum <=1) ;
l_num_rows1 := SQL%ROWCOUNT;
rowcount := rowcount + l_num_rows;
rowcount := rowcount + l_num_rows1;
COMMIT;
EXIT WHEN l_num_rows < MGMT_GLOBAL.MAX_COMMIT;
END LOOP;
RETURN ROWCOUNT;
END PURGE_HISTORY;
END MGMT_DELTA;
/
show errors;