Rem Rem $Header: ecm_delta_pkgdef.sql 08-mar-2005.12:11:12 agor Exp $ Rem Rem ecm_delta_pkgdef.sql Rem Rem Copyright (c) 2002, 2005, Oracle. All rights reserved. Rem Rem NAME Rem ecm_delta_pkgdef.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem agor 03/08/05 - take care of keys which can be null Rem smalathe 09/17/04 - Bug#3879246: Add RAW_TYPE_C constant Rem awarkar 11/15/03 - Bug Fix For Issue#3255739 Rem shuberma 09/18/03 - Adding purge policy callback Rem shuberma 07/08/03 - Adding get_delta_key_display_string Rem shuberma 06/12/03 - Deprecate do_diff_queries Rem shuberma 06/11/03 - Removing save_comparison_results Rem shuberma 04/17/03 - Adding a new arg to the retrieve saved comparison procedure Rem shuberma 03/20/03 - Adding optional arguments to PURGE_HISTORY Rem shuberma 01/27/03 - Add method for getting entire saved comparison Rem shuberma 01/14/03 - Changing start_delta signature Rem shuberma 12/10/02 - Adding new save comparison method Rem shuberma 11/20/02 - add new procedure for history Rem shuberma 11/01/02 - Performance with ref cursors. Rem shuberma 10/25/02 - Adding flag constants Rem shuberma 10/17/02 - Adding PURGE_HISTORY procedure Rem shuberma 09/12/02 - Change type passed to api Rem shuberma 09/05/02 - Adding method for doing comparison and saving Rem shuberma 08/22/02 - Changing signature of DO_DIFF_QUERIES Rem shuberma 08/23/02 - Changes to allow for saving comparisons Rem shuberma 06/21/02 - Adding recordID Rem shuberma 06/05/02 - Use DeltaValues instead of NameValues for passing attributes.. Rem shuberma 05/22/02 - Adding procedure to take a list of ComparisonQueries.. Rem shuberma 05/17/02 - Adding new type specific methods.. Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Created Rem CREATE OR REPLACE PACKAGE MGMT_DELTA IS TYPE DELTA_ENTRY_REF IS REF CURSOR; TYPE DELTA_SUMMARY_REF IS REF CURSOR; TYPE COMPARISON_PROPERTY IS RECORD ( NAME VARCHAR2(64), LEFT_VALUE VARCHAR2( 4000), RIGHT_VALUE VARCHAR2(4000) ); TYPE COMPARISON_PROPERTIES IS TABLE OF COMPARISON_PROPERTY; NO_DELTA CONSTANT RAW(16) := '0ABCDE'; RETURN_NONE CONSTANT SMALLINT := 0; RETURN_DIFFS CONSTANT SMALLINT := 1; RETURN_ALL CONSTANT SMALLINT := 3; RIGHT_ONLY_C CONSTANT VARCHAR2(10) := 'RIGHT_ONLY'; LEFT_ONLY_C CONSTANT VARCHAR2(10) := 'LEFT_ONLY'; SAME_C CONSTANT VARCHAR2(10) := 'SAME'; DIFFERENT_C CONSTANT VARCHAR2(10) := 'DIFFERENT'; INSERT_C CONSTANT VARCHAR2(10) := 'INSERT'; UPDATE_C CONSTANT VARCHAR2(10) := 'UPDATE'; DELETE_C CONSTANT VARCHAR2(10) := 'DELETE'; -- Constants used to record type. STRING_TYPE_C CONSTANT CHAR(1) := 'S'; NUMBER_TYPE_C CONSTANT CHAR(1) := 'N'; DATETIME_TYPE_C CONSTANT CHAR(1) := 'D'; RAW_TYPE_C CONSTANT CHAR(1) := 'R'; -------------------------------------------------------------------------- -- 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', 'UPDATE' or 'DELETE' 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 ); -- 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 ); -- 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 ); -- 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 ); -- 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 defauld will generate -- one. -- RETURNS: The delta guid. -- FUNCTION START_DELTA( v_delta_guid RAW := SYS_GUID()) RETURN RAW; -- 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; -- Update the delta identified by v_delta_guid. This will updated the delta's 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 ); -- 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; -- 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. -- v_attribute_type A single character representing the attribute's type -- STRING_TYPE_C, NUMBER_TYPE_C, or DATETIME_TYPE_C -- 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 ); -- This procedure is a convenience function that c -- only adds an entry to the MGMT_DELTA_VALUES list if one of the two values is not null. -- v_deltavalues 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. -- v_attribute_type A single character representing the attribute's type -- STRING_TYPE_C, NUMBER_TYPE_C, or DATETIME_TYPE_C -- 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); -- -- FUNCTION GET_DELTA_KEY_DISPLAY_STRING( v_key_row_guid RAW ) RETURN VARCHAR2; -- 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', 'UPDATE', or 'DELETE'. 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. -- -- Truncated result when VARCHAR2 max length is exceeded. FUNCTION GET_DELTA_VALUE_DISPLAY_STRING( v_delta_entryguid RAW, v_operation VARCHAR2 ) RETURN VARCHAR2; ------------------------------------------------------------------------------------- -- DO_DIFF_QUERIES -- **************************8 -- deprecated!!!!!!! dO NOT CALL!!!!! -- -- 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()); ------------------------------------------------------------------------------------- -- 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; -------------------------------------------------------------------------------- -- 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 ); -------------------------------------------------------------------------------- -- 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 ); ------------------------------------------------------------------------------------ -- PURGE_HISTORY_RECORDS -- -- BEGIN Purge policies callbacks PROCEDURE ECM_HISTORY_PURGE(pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS); -- The following is not being removed but is deprecated. Use EM Purge policies instead. -- -- PURPOSE -- Cleans up all history records older than the number of days passed in. -- Returns the number of history entries purged. The first arguement indicates -- how far back to purge from. The other arguments qualify which history to -- purge. Default values of NULL indicate anything matches. So, if snapshot -- type is null, but other fields aren't, then purge all snapshots whose -- target type and target name is given. -- -- PARAMETERS -- -- p_numberOfDays The number of days, prior to which the history should be -- purged. -- p_snapshotType The name of the snapshot type whose history is being cleaned. -- p_collectionType The name of the collection type. Typically, this is the -- name of the table from which the data comes. -- p_targetType The type of the target, further qualifying the snapshot type. -- p_targetName The name for target whose history we are cleaning. -- -- 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; END MGMT_DELTA; / show errors;