Rem drv: Rem Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/changemgr/cm_pkgbody.sql /st_emdbsa_11.2/2 2009/02/02 12:13:29 abodge Exp $ Rem Rem cm_pkgbody.sql Rem Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem cm_pkgbody - CM Backend PL/SQL Rem Rem DESCRIPTION Rem CM Backend PL/SQL (along with some UI APIs) Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem abodge 01/14/09 - Move CM Synch to DB Control Rem mahessub 07/03/08 - refix bug 7010896 in a way that security tool can understand Rem mahessub 05/05/08 - fix bug 7010896 Rem abodge 12/04/08 - Remove IR entries for Synch version when dropping Rem version Rem mahessub 12/04/08 - Bug 7458036; More changes to get sql injection analyzer to notice security checks Rem abodge 10/29/08 - Bug 7517202, not deleting script version Rem abodge 09/26/08 - Add arg to add_obj_to_synch Rem mahessub 09/12/08 - bug 7390832; change DBMS_ASSERT to EM_CHECK. Rem mahessub 08/08/08 - support rollup of warnings through MAX_IR_SEVERITY column Rem mahessub 07/03/08 - merge refix of bug 7010896 in a way that security tool can understand Rem mahessub 05/27/08 - fix bug 6955926 Rem abodge 04/10/08 - Remove insecure implementations Rem abodge 04/04/08 - Remove insecure entry points Rem mahessub 12/14/07 - delete interactive jobs when deleting synch Rem abodge 12/12/07 - Handle dba dependencies in baselines Rem mahessub 07/11/07 - fix bug 6162355, character string buffer too small Rem mahessub 05/17/07 - handle job deletion in synch rollup using outer join Rem mahessub 04/16/07 - fixed synch rollup query for non-superuser Rem mahessub 02/23/07 - order by clause for baseline, comparison, synch rollups Rem mahessub 01/08/07 - Add synch impact report table operation Rem mahessub 12/14/06 - support for interactive synch mode Rem abodge 10/09/06 - Add synch obj table operations Rem abodge 10/09/06 - Add synch obj table operations Rem mahessub 09/13/06 - abodge and my changes for synch Rem abodge 06/09/06 - Add obj grant obj type col Rem abodge 05/18/06 - Add function to insert Synch script lines Rem abodge 03/27/06 - Handle baseline dependencies Rem abodge 02/27/06 - Add Synch entry points Rem abodge 05/13/08 - Migrate 11GC functionality Rem abodge 07/19/05 - Add col to baseline deps table Rem xshen 06/29/05 - support version history Rem chyu 06/28/05 - New repmgr header impl Rem abodge 06/16/05 - Continue UDT Rem abodge 06/10/05 - Add UDT support Rem abodge 05/31/05 - Continue pub. syn. Rem abodge 05/25/05 - Add pub. synonym option Rem xshen 05/24/05 - add un-ignore annotation Rem lhan 05/18/05 - fix security violation bug 4359332 Rem xshen 05/17/05 - grabtrans 'lhan_cm_annotate' Rem lhan 05/05/05 - add annotation support Rem abodge 04/25/05 - Fix bug 4328465 - typo Rem abodge 04/22/05 - Touch up dyn SQL Rem abodge 04/21/05 - Use dyn. SQL for get_baseline_version_schemaobj Rem abodge 04/21/05 - Continue pattern work Rem abodge 04/19/05 - Fix error in new proc. Rem abodge 04/18/05 - Add name patterns Rem abodge 03/23/05 - Store DB version with baseline versions Rem abodge 03/18/05 - Add init param calls Rem abodge 03/04/05 - Check for active comp before deleting BL version Rem abodge 03/04/05 - Check for bad versions in prev-version lookup Rem abodge 03/04/05 - Do not rem comp/BL version while proc new Rem version Rem xshen 02/18/05 - get target guid in rollup cursor Rem lhan 02/17/05 - fix bug 4192287 Rem xshen 01/27/05 - fix bug 4144813 Rem lhan 01/17/05 - fix remove baseline (version) after sync relative version Rem lhan 11/24/04 - fix bug 4028698 Rem lhan 11/18/04 - fix bug 4014471 Rem xshen 11/15/04 - search semantics Rem lhan 11/02/04 - fix bug 3987070 (delete version) Rem abodge 10/06/04 - Revise baseline queries for MTIME objs Rem abodge 09/29/04 - Add package body type Rem xshen 09/23/04 - adding description column Rem abodge 09/20/04 - Add IGNORE flags to scope spec Rem abodge 09/15/04 - Update definitions, mostly Rem xshen 09/15/04 - grabtrans 'abodge_comp_exclude_mode' Rem xshen 09/15/04 - get new timezone region into rollup result set Rem lhan 09/14/04 - lhan_cm_0914 Rem lhan 09/14/04 - move to cm_pkgbody.sql Rem xshen 09/13/04 - reorder cursor columns Rem xshen 09/07/04 - consolidate Rem lhan 09/07/04 - Rem xshen 09/02/04 - tune sql Rem xshen 08/30/04 - add security check for rollup, delete calls etc Rem abodge 08/30/04 - Return hash key for schemaobj query Rem abodge 08/24/04 - Exclude mat views from schemaobjs Rem abodge 08/04/04 - Add grants_hash to comparison Rem xshen 07/28/04 - add set/delete job/exec to comparison Rem abodge 07/26/04 - Add more grant types Rem xshen 07/12/04 - change CLOB to BLOB for differences Rem xshen 07/08/04 - add_obj_to_comparison function Rem abodge 06/25/04 - Fix remove_obj_from_comp for NS obj Rem lhan 06/23/04 - Rem abodge 06/22/04 - NS obj compare work Rem xshen 06/18/04 - Change DDL column to BLOB Rem lhan 06/18/04 - replace CLOB with BLOB for compression Rem abodge 06/17/04 - Nonschema obj compare Rem lhan 06/16/04 - Rem lhan 06/15/04 - Rem xshen 06/14/04 - Add top level comparison roll up call Rem ... ... Rem abodge 04/16/04 - Created Rem -- MGMT_CM package body -- Includes schema map support CREATE OR REPLACE PACKAGE BODY MGMT_CM as -- Maximum version number, also indicates current version -- when used as value of last_version column. MAX_VERSION_NUMBER CONSTANT INTEGER := 99999; -- Overloaded for JDBC: booleans set from JDBC are converted to bits. function create_scope_spec( p_typemask in number, p_include_grants in number default 0, p_include_all_schemas in number default 0, p_comparison_flags in number default 0, p_include_init_params in number default 0, p_include_public_syn in number default 0) return RAW is grants BOOLEAN := false; all_schema BOOLEAN := false; init_params BOOLEAN := false; pub_syn BOOLEAN := false; retvalue RAW(16); begin IF p_include_grants = 1 THEN grants := true; END IF; IF p_include_all_schemas = 1 THEN all_schema := true; END IF; IF p_include_init_params = 1 THEN init_params := true; END IF; IF p_include_public_syn = 1 THEN pub_syn := true; END IF; retvalue := create_scope_spec(p_typemask, grants, all_schema, p_comparison_flags, init_params, pub_syn); return retvalue; end create_scope_spec; -- Create a scope spec function create_scope_spec( p_typemask in number, p_include_grants in boolean default false, p_include_all_schemas in boolean default false, p_comparison_flags in number default 0, p_include_init_params in boolean default false, p_include_public_syn in boolean default false) return raw is l_ss_guid RAW(16) := sys_guid(); l_flags INTEGER := p_comparison_flags; begin IF p_include_grants THEN l_flags := l_flags + MGMT_CM.INCLUDE_GRANTS; END IF; IF p_include_all_schemas THEN l_flags := l_flags + MGMT_CM.INCLUDE_ALL_SCHEMAS; END IF; IF p_include_init_params THEN l_flags := l_flags + MGMT_CM.INCLUDE_INIT_PARAMS; END IF; IF p_include_public_syn THEN l_flags := l_flags + MGMT_CM.INCLUDE_PUBLIC_SYNONYMS; END IF; INSERT INTO mgmt_cm_scopespecs (ss_guid, object_types, flags) VALUES (l_ss_guid, p_typemask, l_flags); return l_ss_guid; end create_scope_spec; -- Add a schema to a scope spec procedure add_scope_schema( p_ss_id in raw, p_schema_name in varchar2) is begin INSERT INTO mgmt_cm_scopespec_names (ss_guid, match_types, name) VALUES( p_ss_id, MGMT_CM.SCHEMA_MATCH, p_schema_name ); end add_scope_schema; procedure add_scope_name_pattern( p_ss_id in raw, p_pattern in varchar2) is begin INSERT INTO mgmt_cm_scopespec_names (ss_guid, match_types, name) VALUES( p_ss_id, MGMT_CM.NAME_PATTERN_MATCH, p_pattern ); end add_scope_name_pattern; -- Add a name to a scope spec procedure add_scope_name( p_ss_id in raw, p_name in varchar2, p_match_type in number default OBJECT_MATCH_ANY_TYPE, p_obj_schema in varchar2 default null) is begin INSERT INTO mgmt_cm_scopespec_names (ss_guid, match_types, schema, name) VALUES( p_ss_id, p_match_type, p_obj_schema, p_name ); end add_scope_name; -- Get scope spec typemask -- p_ss_id: ID of scope spec function get_scope_spec_typemask( p_ss_id in raw) return number is l_typemask NUMBER; begin SELECT object_types INTO l_typemask FROM mgmt_cm_scopespecs WHERE ss_guid = p_ss_id; RETURN l_typemask; END get_scope_spec_typemask; -- Get scope spec flags -- p_ss_id: ID of scope spec function get_scope_spec_flags( p_ss_id in raw) return number is l_flags NUMBER; begin SELECT flags INTO l_flags FROM mgmt_cm_scopespecs WHERE ss_guid = p_ss_id; RETURN l_flags; END get_scope_spec_flags; -- Get scope spec names procedure get_scope_spec_names( p_ss_id in raw, p_ss_names in out scope_names_cur_typ) is begin OPEN p_ss_names FOR SELECT match_types, schema, name FROM mgmt_cm_scopespec_names WHERE ss_guid = p_ss_id ORDER BY match_types ASC; END get_scope_spec_names; function scope_spec_has_schema_types( p_ss_id in raw) return number is l_ns_count NUMBER := 0; begin SELECT count(*) INTO l_ns_count FROM mgmt_cm_scopespec_names WHERE ss_guid = p_ss_id AND match_types != TABLESPACE_MATCH AND match_types != ROLLBACK_SEG_MATCH AND match_types != USER_MATCH AND match_types != ROLE_MATCH AND match_types != PROFILE_MATCH; RETURN l_ns_count; END scope_spec_has_schema_types; function scope_spec_has_nonschema_types( p_ss_id in raw) return number is l_ns_count NUMBER := 0; begin SELECT count(*) INTO l_ns_count FROM mgmt_cm_scopespec_names WHERE ss_guid = p_ss_id AND match_types = TABLESPACE_MATCH OR match_types = ROLLBACK_SEG_MATCH OR match_types = USER_MATCH OR match_types = ROLE_MATCH OR match_types = PROFILE_MATCH; RETURN l_ns_count; END scope_spec_has_nonschema_types; -- Initialize a cursor with the name patterns contained in a -- scope spec. procedure get_scope_name_patterns( p_ss_id in raw, p_ss_patterns in out weak_cur_typ) is begin OPEN p_ss_patterns FOR SELECT name FROM mgmt_cm_scopespec_names WHERE ss_guid = p_ss_id AND match_types = MGMT_CM.NAME_PATTERN_MATCH; end get_scope_name_patterns; -- Build a comma-separated list, enclosed in parentheses, -- of the schema names contained in the scope spec. -- This may be a good candidate to push into CM_MGMT package function get_schema_list (p_ss_id in raw) return varchar2 is l_s_names_cv mgmt_cm.weak_cur_typ; l_s_list VARCHAR2(10000) := '('; l_s_name VARCHAR2(30); l_first BOOLEAN := TRUE; begin OPEN l_s_names_cv FOR SELECT name FROM mgmt_cm_scopespec_names WHERE ss_guid = p_ss_id AND match_types = MGMT_CM.SCHEMA_MATCH; LOOP FETCH l_s_names_cv INTO l_s_NAME; EXIT WHEN l_s_names_cv%NOTFOUND; IF NOT l_first THEN l_s_list := l_s_list || ', '; END IF; l_s_list := l_s_list || '''' || l_s_name || ''''; l_first := FALSE; END LOOP; CLOSE l_s_names_cv; l_s_list := l_s_list || ')'; RETURN l_s_list; END get_schema_list; function get_name_pattern_list (p_ss_id in raw) return varchar2 is l_pattern_names_cv weak_cur_typ; l_list VARCHAR2(1000) := ''; l_pattern VARCHAR2(30); l_first BOOLEAN := TRUE; begin OPEN l_pattern_names_cv FOR SELECT name FROM mgmt_cm_scopespec_names WHERE ss_guid = p_ss_id AND match_types = MGMT_CM.NAME_PATTERN_MATCH; LOOP FETCH l_pattern_names_cv INTO l_pattern; EXIT WHEN l_pattern_names_cv%NOTFOUND; IF NOT l_first THEN l_list := l_list || ', '; END IF; l_list := l_list || l_pattern; l_first := FALSE; END LOOP; CLOSE l_pattern_names_cv; RETURN l_list; END get_name_pattern_list; function get_schema_list_from_map ( p_owner_id in raw, p_side in varchar2) return varchar2 is l_sm_names_cv weak_cur_typ; l_s_list VARCHAR2(10000) := '('; l_s_name VARCHAR2(30); l_first BOOLEAN := TRUE; begin IF p_side = 'L' THEN OPEN l_sm_names_cv FOR SELECT left_schema FROM mgmt_cm_schema_maps WHERE owner_id = p_owner_id; ELSE OPEN l_sm_names_cv FOR SELECT right_schema FROM mgmt_cm_schema_maps WHERE owner_id = p_owner_id; END IF; LOOP FETCH l_sm_names_cv INTO l_s_name; EXIT WHEN l_sm_names_cv%NOTFOUND; IF NOT l_first THEN l_s_list := l_s_list || ', '; END IF; l_s_list := l_s_list || '''' || l_s_name || ''''; l_first := FALSE; END LOOP; CLOSE l_sm_names_cv; l_s_list := l_s_list || ')'; RETURN l_s_list; END get_schema_list_from_map; -- Build a comma-separated list of types from a typemask function get_types_list (p_typemask in number) return varchar2 is l_t_list VARCHAR2(512) := '('; l_curr_type INTEGER := 0; l_first BOOLEAN := TRUE; begin LOOP IF bitand(p_typemask, power(2, l_curr_type)) > 0 THEN IF NOT l_first THEN l_t_list := l_t_list || ', '; END IF; l_first := FALSE; l_t_list := l_t_list || '''' || get_type_name( l_curr_type ) || ''''; END IF; l_curr_type := l_curr_type + 1; EXIT WHEN l_curr_type > MGMT_CM.PROFILE_MATCH; END LOOP; l_t_list := l_t_list || ')'; RETURN l_t_list; END get_types_list; function get_nonschema_types_list (p_typemask in number) return varchar2 is l_t_list VARCHAR2(512) := '('; l_curr_type INTEGER := 0; l_first BOOLEAN := TRUE; l_type VARCHAR2(20) := ''; begin LOOP IF bitand(p_typemask, power(2, l_curr_type)) > 0 THEN l_type := get_nonschema_type_name( l_curr_type ); IF (l_type != '**NO TYPE**') THEN IF NOT l_first THEN l_t_list := l_t_list || ', '; END IF; l_first := FALSE; l_t_list := l_t_list || '''' || l_type || ''''; END IF; END IF; l_curr_type := l_curr_type + 1; EXIT WHEN l_curr_type > MGMT_CM.PROFILE_MATCH; END LOOP; l_t_list := l_t_list || ')'; RETURN l_t_list; END get_nonschema_types_list; -- Return the type name corresponding to the code function get_type_name (p_type_code in number) return varchar2 is l_type_name VARCHAR2(30) := '**NO TYPE**'; begin CASE p_type_code WHEN TABLE_MATCH THEN l_type_name := 'TABLE'; WHEN INDEX_MATCH THEN l_type_name := 'INDEX'; WHEN VIEW_MATCH THEN l_type_name := 'VIEW'; WHEN TRIGGER_MATCH THEN l_type_name := 'TRIGGER'; WHEN SYNONYM_MATCH THEN l_type_name := 'SYNONYM'; WHEN PACKAGE_MATCH THEN l_type_name := 'PACKAGE'; -- Following may not be correct for MDAPI... WHEN PACKAGE_BODY_MATCH THEN l_type_name := 'PACKAGE_BODY'; WHEN PROCEDURE_MATCH THEN l_type_name := 'PROCEDURE'; WHEN FUNCTION_MATCH THEN l_type_name := 'FUNCTION'; WHEN SEQUENCE_MATCH THEN l_type_name := 'SEQUENCE'; WHEN DBLINK_MATCH THEN l_type_name := 'DB_LINK'; WHEN MVIEW_MATCH THEN l_type_name := 'MATERIALIZED VIEW'; WHEN MVIEW_LOG_MATCH THEN l_type_name := 'MATERIALIZED_VIEW_LOG'; WHEN CLUSTER_MATCH THEN l_type_name := 'CLUSTER'; WHEN TABLESPACE_MATCH THEN l_type_name := 'TABLESPACE'; WHEN ROLLBACK_SEG_MATCH THEN l_type_name := 'ROLLBACK_SEGMENT'; WHEN USER_MATCH THEN l_type_name := 'USER'; WHEN ROLE_MATCH THEN l_type_name := 'ROLE'; WHEN PROFILE_MATCH THEN l_type_name := 'PROFILE'; END CASE; RETURN l_type_name; END get_type_name; function get_nonschema_type_name (p_type_code in number) return varchar2 is l_type_name VARCHAR2(20) := '**NO TYPE**'; begin CASE p_type_code WHEN TABLESPACE_MATCH THEN l_type_name := 'TABLESPACE'; WHEN ROLLBACK_SEG_MATCH THEN l_type_name := 'ROLLBACK_SEGMENT'; WHEN USER_MATCH THEN l_type_name := 'USER'; WHEN ROLE_MATCH THEN l_type_name := 'ROLE'; WHEN PROFILE_MATCH THEN l_type_name := 'PROFILE'; ELSE NULL; END CASE; RETURN l_type_name; END get_nonschema_type_name; -- Create a baseline function create_baseline( p_name in varchar2, p_owner in varchar2, p_scope_spec in raw, p_source_id in raw, p_description in varchar2 default null, p_temporary in number default 0) return raw is l_baseline_guid RAW(16) := sys_guid(); l_source_name VARCHAR2(256) := 'EatAtJoes'; l_name VARCHAR2(40) := p_name; begin SELECT target_name INTO l_source_name FROM mgmt_targets WHERE target_guid = p_source_id; -- No data found - tgt deletion -- Exception needs to be handled.. -- Generate temporary name for temporary baseline... if p_temporary != 0 then l_name := 'temp_' || rawtohex(l_baseline_guid); end if; INSERT INTO mgmt_cm_baselines (baseline_guid, baseline_owner, baseline_name, baseline_ss, source_id, source_name, description, temp_bl) VALUES( l_baseline_guid, p_owner, l_name, p_scope_spec, p_source_id, l_source_name, p_description, p_temporary ); RETURN l_baseline_guid; END create_baseline; -- Get a baseline's ID from name and owner function get_baseline_id( p_name in varchar2, p_owner in varchar2) return raw is l_bl_id RAW(16); begin SELECT baseline_guid INTO l_bl_id FROM mgmt_cm_baselines WHERE baseline_owner = p_owner AND baseline_name = p_name; RETURN l_bl_id; END get_baseline_id; -- Get information about a baseline. procedure get_baseline_info (p_baseline_id in raw, p_baseline_info in out BaselineInfoRecTyp) is begin SELECT baseline_ss, source_id INTO p_baseline_info FROM mgmt_cm_baselines WHERE baseline_guid = p_baseline_id; end get_baseline_info; -- Get a baseline's scope spec function get_baseline_scope_spec( p_baseline_id in raw) return raw is l_ss_id RAW(16); begin SELECT baseline_ss INTO l_ss_id FROM mgmt_cm_baselines WHERE baseline_guid = p_baseline_id; RETURN l_ss_id; END get_baseline_scope_spec; -- Get a baseline's source database ID function get_baseline_source_id( p_baseline_id in raw) return raw is l_source_id RAW(16); begin SELECT source_id INTO l_source_id FROM mgmt_cm_baselines WHERE baseline_guid = p_baseline_id; RETURN l_source_id; END get_baseline_source_id; -- Add a baseline version function add_baseline_version( p_baseline_id in raw) return integer is l_newV INTEGER; begin SELECT MAX(baseline_version) INTO l_newV FROM mgmt_cm_baseline_versions WHERE baseline_guid = p_baseline_id; IF l_newV IS NULL THEN l_newV := 1; ELSE l_newV := l_newV + 1; END IF; INSERT INTO mgmt_cm_baseline_versions (baseline_guid, baseline_version) VALUES (p_baseline_id, l_newV); RETURN l_newV; END add_baseline_version; -- Get current baseline version function get_current_baseline_version( p_baseline_id in raw) return integer is l_currV INTEGER; begin SELECT MAX(baseline_version) INTO l_currV FROM mgmt_cm_baseline_versions WHERE baseline_guid = p_baseline_id; RETURN nvl(l_currV, 0); END get_current_baseline_version; -- Get previous baseline version function get_previous_baseline_version( p_baseline_id in raw) return integer is l_currV INTEGER; begin SELECT MAX(baseline_version) INTO l_currV FROM mgmt_cm_baseline_versions WHERE baseline_guid = p_baseline_id AND baseline_version < (SELECT MAX(baseline_version) FROM mgmt_cm_baseline_versions WHERE baseline_guid = p_baseline_id); RETURN nvl(l_currV, 0); END get_previous_baseline_version; -- Add a new object definition to a baseline version procedure add_obj_to_baseline( p_baseline_id in raw, p_object_type in number, p_object_schema in varchar2, p_object_name in varchar2, p_version_number in number, p_last_ddl in date, p_hash_value in varchar2, p_object_definition in blob, p_prev_version_action number default REMOVE_PREV_VERSION) is begin if p_prev_version_action = REMOVE_PREV_VERSION then -- This call will set the LAST_VERSION number of the existing version -- to the previous version. remove_obj_from_baseline( p_baseline_id, p_object_type, p_object_schema, p_object_name, p_version_number ); end if; INSERT INTO mgmt_cm_baseline_objects (baseline_guid, object_type, object_schema, object_name, first_version, last_ddl_time, hash_value, definition) VALUES( p_baseline_id, p_object_type, p_object_schema, p_object_name, p_version_number, p_last_ddl, p_hash_value, p_object_definition ); END add_obj_to_baseline; procedure add_init_param_to_baseline( p_baseline_id in raw, p_param_name in varchar2, p_param_value in varchar2, p_version_number in number) is begin remove_init_prm_from_baseline( p_baseline_id, p_param_name, p_version_number); INSERT INTO mgmt_cm_baseline_init_params (baseline_guid, param_name, param_value, first_version) VALUES (p_baseline_id, p_param_name, p_param_value, p_version_number); END add_init_param_to_baseline; -- Add a new dependent object definition to a baseline version procedure add_dependent_to_baseline( p_baseline_id in raw, p_base_obj_type in number, p_base_obj_schema in varchar2, p_base_obj_name in varchar2, p_base_obj_col in varchar2, p_dependent_type in number, p_hash_value in varchar2, p_version_number in number, p_object_definition in blob) is begin -- This call will set the LAST_VERSION number of the existing version -- to the previous version. remove_dependent_from_baseline( p_baseline_id, p_dependent_type, p_base_obj_type, p_base_obj_schema, p_base_obj_name, p_base_obj_col, p_version_number); INSERT INTO mgmt_cm_baseline_dependents (baseline_guid, base_object_type, base_object_schema, base_object_name, base_object_column, dependent_type, hash_value, first_version, definition) VALUES( p_baseline_id, p_base_obj_type, p_base_obj_schema, p_base_obj_name, p_base_obj_col, p_dependent_type, p_hash_value, p_version_number, p_object_definition ); END add_dependent_to_baseline; procedure add_objgrant_to_baseline( p_baseline_id in raw, p_grantee in varchar2, p_obj_owner in varchar2, p_obj_name in varchar2, p_col_name in varchar2, p_grantor in varchar2, p_privilege in varchar2, p_flag in number, p_version_number in number, p_obj_type in number) is begin -- This call will set the LAST_VERSION number of the existing version -- to the previous version. remove_objgrant_from_baseline( p_baseline_id, p_grantee, p_obj_owner, p_obj_name, p_col_name, p_grantor, p_privilege, p_version_number); INSERT INTO mgmt_cm_baseline_objgrants (baseline_guid, grantee_name, obj_owner, obj_name, col_name, grantor_name, privilege, flag, first_version, obj_type) VALUES( p_baseline_id, p_grantee, p_obj_owner, p_obj_name, p_col_name, p_grantor, p_privilege, p_flag, p_version_number, p_obj_type); END add_objgrant_to_baseline; procedure add_sysgrant_to_baseline( p_baseline_id in raw, p_grantee in varchar2, p_privilege in varchar2, p_flag in number, p_version_number in number) is begin -- This call will set the LAST_VERSION number of the existing version -- to the previous version. remove_sysgrant_from_baseline( p_baseline_id, p_grantee, p_privilege, p_version_number); INSERT INTO mgmt_cm_baseline_sysgrants (baseline_guid, grantee_name, privilege, flag, first_version) VALUES( p_baseline_id, p_grantee, p_privilege, p_flag, p_version_number); END add_sysgrant_to_baseline; procedure add_rolegrant_to_baseline( p_baseline_id in raw, p_grantee in varchar2, p_role in varchar2, p_flag in number, p_version_number in number) is begin -- This call will set the LAST_VERSION number of the existing version -- to the previous version. remove_rolegrant_from_baseline( p_baseline_id, p_grantee, p_role, p_version_number); INSERT INTO mgmt_cm_baseline_rolegrants (baseline_guid, grantee_name, rolepriv, flag, first_version) VALUES( p_baseline_id, p_grantee, p_role, p_flag, p_version_number); END add_rolegrant_to_baseline; procedure add_quotagrant_to_baseline( p_baseline_id in raw, p_grantee in varchar2, p_tablespace in varchar2, p_maxbytes in number, p_version_number in number) is begin -- This call will set the LAST_VERSION number of the existing version -- to the previous version. remove_qtagrnt_from_baseline( p_baseline_id, p_grantee, p_tablespace, p_version_number); INSERT INTO mgmt_cm_baseline_quotagrants (baseline_guid, grantee_name, tablespace, maxbytes, first_version) VALUES( p_baseline_id, p_grantee, p_tablespace, p_maxbytes, p_version_number); END add_quotagrant_to_baseline; procedure add_proxygrant_to_baseline( p_baseline_id in raw, p_grantee in varchar2, p_proxy_user in varchar2, p_direction in number, p_version_number in number) is begin -- This call will set the LAST_VERSION number of the existing version -- to the previous version. remove_prxygrnt_from_baseline( p_baseline_id, p_grantee, p_proxy_user, p_version_number); INSERT INTO mgmt_cm_baseline_proxygrants (baseline_guid, grantee_name, proxy_user, direction, first_version) VALUES( p_baseline_id, p_grantee, p_proxy_user, p_direction, p_version_number); END add_proxygrant_to_baseline; procedure add_cons_grp_to_baseline( p_baseline_id in raw, p_grantee in varchar2, p_cons_grp in varchar2, p_flag in number, p_version_number in number) is begin -- This call will set the LAST_VERSION number of the existing version -- to the previous version. remove_cons_grp_from_baseline( p_baseline_id, p_grantee, p_cons_grp, p_version_number); INSERT INTO mgmt_cm_baseline_cons_groups (baseline_guid, grantee_name, cons_group, flag, first_version) VALUES( p_baseline_id, p_grantee, p_cons_grp, p_flag, p_version_number); END add_cons_grp_to_baseline; function add_obj_to_baseline( p_baseline_id in raw, p_object_type in number, p_object_schema in varchar2, p_object_name in varchar2, p_version_number in number, p_last_ddl in date, p_hash_value in varchar2, p_addnl_info in varchar2 default null) return blob is l_definition blob; begin -- This call will set the LAST_VERSION number of the existing version -- to the previous version. remove_obj_from_baseline( p_baseline_id, p_object_type, p_object_schema, p_object_name, p_version_number ); INSERT INTO mgmt_cm_baseline_objects (baseline_guid, object_type, object_schema, object_name, first_version, last_ddl_time, hash_value, definition, addnl_info) VALUES( p_baseline_id, p_object_type, p_object_schema, p_object_name, p_version_number, p_last_ddl, p_hash_value, empty_blob(), p_addnl_info ) RETURNING definition INTO l_definition; RETURN l_definition; END add_obj_to_baseline; function add_dependent_to_baseline( p_baseline_id in raw, p_base_obj_type in number, p_base_obj_schema in varchar2, p_base_obj_name in varchar2, p_base_obj_col in varchar2, p_dependent_type in number, p_hash_value in varchar2, p_version_number in number) return blob is l_definition blob; begin -- This call will set the LAST_VERSION number of the existing version -- to the previous version. remove_dependent_from_baseline( p_baseline_id, p_dependent_type, p_base_obj_type, p_base_obj_schema, p_base_obj_name, p_base_obj_col, p_version_number); INSERT INTO mgmt_cm_baseline_dependents (baseline_guid, base_object_type, base_object_schema, base_object_name, base_object_column, dependent_type, hash_value, first_version, definition) VALUES( p_baseline_id, p_base_obj_type, p_base_obj_schema, p_base_obj_name, p_base_obj_col, p_dependent_type, p_hash_value, p_version_number, empty_blob() ) RETURNING definition INTO l_definition; RETURN l_definition; END add_dependent_to_baseline; -- Add a DBA dependency to a baseline version procedure add_dependency_to_baseline( p_baseline_id in raw, p_dep_obj_type in number, p_dep_obj_schema in varchar2, p_dep_obj_name in varchar2, p_ref_obj_type in number, p_ref_obj_schema in varchar2, p_ref_obj_name in varchar2, p_ref_type in varchar2, p_version_number in number) is begin IF p_version_number != 1 THEN remove_depcy_from_baseline( p_baseline_id, p_dep_obj_type, p_dep_obj_schema, p_dep_obj_name, p_ref_obj_type, p_ref_obj_schema, p_ref_obj_name, p_version_number); END IF; INSERT INTO mgmt_cm_baseline_dependencies (baseline_guid, first_version, type, owner, name, referenced_type, referenced_owner, referenced_name, dependency_type) VALUES( p_baseline_id, p_version_number, p_dep_obj_type, p_dep_obj_schema, p_dep_obj_name, p_ref_obj_type, p_ref_obj_schema, p_ref_obj_name, p_ref_type ); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN null; -- Dependency entries are sometimes duplicates END add_dependency_to_baseline; -- Remove an object from a baseline version -- (i.e., it is no longer present in the database) procedure remove_obj_from_baseline( p_baseline_id in raw, p_object_type in number, p_object_schema in varchar2, p_object_name in varchar2, p_version_number in number) is begin UPDATE mgmt_cm_baseline_objects SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND object_type = p_object_type AND (object_schema = p_object_schema OR object_schema is NULL) AND object_name = p_object_name AND last_version = MAX_VERSION_NUMBER; END remove_obj_from_baseline; procedure remove_nsobj_from_baseline( p_baseline_id in raw, p_object_type in number, p_hash_value in varchar2, p_version_number in number) is begin UPDATE mgmt_cm_baseline_objects SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND object_type = p_object_type AND hash_value = p_hash_value AND last_version = MAX_VERSION_NUMBER; END remove_nsobj_from_baseline; procedure remove_objgrant_from_baseline( p_baseline_id in raw, p_grantee in varchar2, p_obj_owner in varchar2, p_obj_name in varchar2, p_col_name in varchar2, p_grantor in varchar2, p_privilege in varchar2, p_version_number in number) is begin UPDATE mgmt_cm_baseline_objgrants SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND grantee_name = p_grantee AND obj_owner = p_obj_owner AND obj_name = p_obj_name AND (col_name = p_col_name OR col_name is NULL) AND grantor_name = p_grantor AND privilege = p_privilege AND last_version = MAX_VERSION_NUMBER; END remove_objgrant_from_baseline; procedure remove_sysgrant_from_baseline( p_baseline_id in raw, p_grantee in varchar2, p_privilege in varchar2, p_version_number in number) is begin UPDATE mgmt_cm_baseline_sysgrants SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND grantee_name = p_grantee AND privilege = p_privilege AND last_version = MAX_VERSION_NUMBER; END remove_sysgrant_from_baseline; procedure remove_rolegrant_from_baseline( p_baseline_id in raw, p_grantee in varchar2, p_role in varchar2, p_version_number in number) is begin UPDATE mgmt_cm_baseline_rolegrants SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND grantee_name = p_grantee AND rolepriv = p_role AND last_version = MAX_VERSION_NUMBER; END remove_rolegrant_from_baseline; procedure remove_qtagrnt_from_baseline( p_baseline_id in raw, p_grantee in varchar2, p_tablespace in varchar2, p_version_number in number) is begin UPDATE mgmt_cm_baseline_quotagrants SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND grantee_name = p_grantee AND tablespace = p_tablespace AND last_version = MAX_VERSION_NUMBER; END remove_qtagrnt_from_baseline; procedure remove_prxygrnt_from_baseline( p_baseline_id in raw, p_grantee in varchar2, p_proxy_user in varchar2, p_version_number in number) is begin UPDATE mgmt_cm_baseline_proxygrants SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND grantee_name = p_grantee AND proxy_user = p_proxy_user AND last_version = MAX_VERSION_NUMBER; END remove_prxygrnt_from_baseline; procedure remove_cons_grp_from_baseline( p_baseline_id in raw, p_grantee in varchar2, p_cons_grp in varchar2, p_version_number in number) is begin UPDATE mgmt_cm_baseline_cons_groups SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND grantee_name = p_grantee AND cons_group = p_cons_grp AND last_version = MAX_VERSION_NUMBER; END remove_cons_grp_from_baseline; procedure remove_init_prm_from_baseline( p_baseline_id in raw, p_param_name in varchar2, p_version_number in number) IS BEGIN UPDATE mgmt_cm_baseline_init_params SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND param_name = p_param_name AND last_version = MAX_VERSION_NUMBER; END remove_init_prm_from_baseline; procedure remove_dependent_from_baseline( p_baseline_id in raw, p_dep_type in number, p_base_obj_type in number, p_base_obj_schema in varchar2, p_base_obj_name in varchar2, p_base_obj_col in varchar2, p_version_number in number) is BEGIN UPDATE mgmt_cm_baseline_dependents SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND dependent_type = p_dep_type AND base_object_type = p_base_obj_type AND (base_object_schema = p_base_obj_schema OR base_object_schema IS NULL) AND base_object_name = p_base_obj_name AND ( ( p_base_obj_col IS NULL AND base_object_column IS NULL ) OR p_base_obj_col = base_object_column ) AND last_version = MAX_VERSION_NUMBER; END remove_dependent_from_baseline; procedure remove_dependent_from_baseline( p_baseline_id in raw, p_dep_type in number, p_hash_value in varchar2, p_version_number in number) is begin UPDATE mgmt_cm_baseline_dependents SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND dependent_type = p_dep_type AND hash_value = p_hash_value AND last_version = MAX_VERSION_NUMBER; END remove_dependent_from_baseline; procedure remove_mvlog_from_baseline( p_baseline_id in raw, p_hash_value in varchar2, p_version_number in number, p_obj_schema in varchar2) is begin UPDATE mgmt_cm_baseline_objects SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND object_type = mview_log_match AND hash_value = p_hash_value AND object_schema = p_obj_schema AND last_version = MAX_VERSION_NUMBER; END remove_mvlog_from_baseline; procedure remove_depcy_from_baseline( p_baseline_id in raw, p_dep_obj_type in number, p_dep_obj_schema in varchar2, p_dep_obj_name in varchar2, p_ref_obj_type in number, p_ref_obj_schema in varchar2, p_ref_obj_name in varchar2, p_version_number in number) is begin UPDATE mgmt_cm_baseline_dependencies SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND type = p_dep_obj_type AND (owner = p_dep_obj_schema OR owner IS NULL) AND name = p_dep_obj_name AND referenced_type = p_ref_obj_type AND (referenced_owner = p_ref_obj_schema OR referenced_owner IS NULL) AND referenced_name = p_ref_obj_name AND last_version = MAX_VERSION_NUMBER; END remove_depcy_from_baseline; -- Set a baseline version's capture date procedure set_capture_date( p_baseline_id in raw, p_version_number in number, p_execution_id in raw) is TYPE CaptDate IS RECORD ( l_capture_date date, l_timezone varchar2(64)); capture_date CaptDate; begin BEGIN SELECT start_time, timezone_region INTO capture_date FROM mgmt_job_exec_summary WHERE execution_id = p_execution_id AND rownum = 1; EXCEPTION WHEN OTHERS THEN NULL; END; IF capture_date.l_capture_date IS NULL THEN capture_date.l_capture_date := SYSDATE; END IF; UPDATE mgmt_cm_baseline_versions SET capture_time = capture_date.l_capture_date, timezone_region = capture_date.l_timezone WHERE baseline_guid = p_baseline_id AND baseline_version = p_version_number; END set_capture_date; -- Get objects in a baseline version procedure get_baseline_version_objects( p_baseline_id in raw, p_version_number in number, p_objects_cursor in out weak_cur_typ) is begin OPEN p_objects_cursor FOR SELECT object_type, object_schema, object_name, last_ddl_time FROM mgmt_cm_baseline_objects WHERE baseline_guid = p_baseline_id AND first_version <= p_version_number AND last_version >= p_version_number ORDER BY object_type ASC, object_schema ASC, object_name ASC; end get_baseline_version_objects; procedure get_baseline_version_nsobj( p_baseline_id in raw, p_version_number in number, p_objects_cursor in out weak_cur_typ, p_scopespec_id in raw default null, p_mode in number default SCHEMA_MODE) is begin IF p_scopespec_id IS NULL THEN OPEN p_objects_cursor FOR SELECT object_type, null, object_name, hash_value FROM mgmt_cm_baseline_objects WHERE baseline_guid = p_baseline_id AND object_schema is null AND first_version <= p_version_number AND last_version >= p_version_number ORDER BY object_type ASC, object_name ASC; ELSE -- Scoped query IF p_mode = SCHEMA_MODE THEN OPEN p_objects_cursor FOR SELECT bo.object_type, null, bo.object_name, bo.hash_value FROM mgmt_cm_baseline_objects bo, mgmt_cm_scopespecs ss WHERE bo.baseline_guid = p_baseline_id AND bo.object_schema is null AND bo.first_version <= p_version_number AND bo.last_version >= p_version_number AND bitand(power(2, bo.object_type), ss.object_types) > 0 AND ss.ss_guid = p_scopespec_id ORDER BY bo.object_type ASC, bo.object_name ASC; ELSE -- Scoped, object-mode -- NOTE: Currently, capture and comparison of individual nonschema -- objects is not supported, so this code should never be exercised. OPEN p_objects_cursor FOR SELECT bo.object_type, null, bo.object_name, bo.hash_value FROM mgmt_cm_baseline_objects bo, mgmt_cm_scopespec_names ssn WHERE bo.baseline_guid = p_baseline_id AND bo.object_schema is null AND bo.first_version <= p_version_number AND bo.last_version >= p_version_number AND bo.object_type = ssn.match_types AND bo.object_name = ssn.name AND ssn.ss_guid = p_scopespec_id; END IF; END IF; end get_baseline_version_nsobj; -- Get the definition of a single object in a baseline version function get_baseline_obj_definition( p_baseline_id in raw, p_version_number in number, p_obj_type in number, p_obj_schema in varchar2, p_obj_name in varchar2) return blob is l_def BLOB; begin SELECT definition INTO l_def FROM mgmt_cm_baseline_objects WHERE baseline_guid = p_baseline_id AND object_type = p_obj_type AND (object_schema = p_obj_schema OR object_schema IS NULL) AND object_name = p_obj_name AND first_version <= p_version_number AND last_version >= p_version_number; RETURN l_def; END get_baseline_obj_definition; procedure get_baseline_version_objgrant( p_baseline_id in raw, p_version_number in number, p_objects_cursor in out weak_cur_typ) is begin OPEN p_objects_cursor FOR SELECT grantee_name, obj_owner, obj_name, col_name, grantor_name, privilege, flag, obj_type FROM mgmt_cm_baseline_objgrants WHERE baseline_guid = p_baseline_id AND first_version <= p_version_number AND last_version >= p_version_number ORDER BY 1, 6, 5, 2, 3, 4 NULLS FIRST; end get_baseline_version_objgrant; procedure get_baseline_version_sysgrant( p_baseline_id in raw, p_version_number in number, p_objects_cursor in out weak_cur_typ) is begin OPEN p_objects_cursor FOR SELECT grantee_name, privilege, flag FROM mgmt_cm_baseline_sysgrants WHERE baseline_guid = p_baseline_id AND first_version <= p_version_number AND last_version >= p_version_number ORDER BY 1, 2; end get_baseline_version_sysgrant; procedure get_baseline_version_rolegrant( p_baseline_id in raw, p_version_number in number, p_objects_cursor in out weak_cur_typ) is begin OPEN p_objects_cursor FOR SELECT grantee_name, rolepriv, flag FROM mgmt_cm_baseline_rolegrants WHERE baseline_guid = p_baseline_id AND first_version <= p_version_number AND last_version >= p_version_number ORDER BY 1, 2; end get_baseline_version_rolegrant; procedure get_baseline_version_qtagrnt( p_baseline_id in raw, p_version_number in number, p_objects_cursor in out weak_cur_typ) is begin OPEN p_objects_cursor FOR SELECT grantee_name, tablespace, maxbytes FROM mgmt_cm_baseline_quotagrants WHERE baseline_guid = p_baseline_id AND first_version <= p_version_number AND last_version >= p_version_number ORDER BY 1, 2; end get_baseline_version_qtagrnt; procedure get_baseline_version_prxygrnt( p_baseline_id in raw, p_version_number in number, p_objects_cursor in out weak_cur_typ) is begin OPEN p_objects_cursor FOR SELECT grantee_name, proxy_user, direction FROM mgmt_cm_baseline_proxygrants WHERE baseline_guid = p_baseline_id AND first_version <= p_version_number AND last_version >= p_version_number ORDER BY 1, 2; end get_baseline_version_prxygrnt; procedure get_baseline_version_cons_grps( p_baseline_id in raw, p_version_number in number, p_objects_cursor in out weak_cur_typ) is begin OPEN p_objects_cursor FOR SELECT grantee_name, cons_group, flag FROM mgmt_cm_baseline_cons_groups WHERE baseline_guid = p_baseline_id AND first_version <= p_version_number AND last_version >= p_version_number ORDER BY 1, 2; end get_baseline_version_cons_grps; procedure get_baseline_version_init_prms( p_baseline_id in raw, p_version_number in number, p_objects_cursor in out weak_cur_typ) is begin OPEN p_objects_cursor FOR SELECT param_name, param_value FROM mgmt_cm_baseline_init_params WHERE baseline_guid = p_baseline_id AND first_version <= p_version_number AND last_version >= p_version_number ORDER BY 1; end get_baseline_version_init_prms; -- Remove a baseline version -- NOTE: We now allow removal of first or last version -- NOTE: This could also be done as a delete trigger on mgmt_cm_baseline_versions -- but this way may be better from an error-handling perspective. procedure remove_baseline_version( p_baseline_id in raw, p_version_number in number) is l_exec_id RAW(16); l_maxV INTEGER; l_prevV INTEGER; l_count NUMBER := 0; l_job_id MGMT_JOB.JOB_ID%TYPE; begin SELECT MAX(baseline_version) INTO l_maxV FROM mgmt_cm_baseline_versions WHERE baseline_guid = p_baseline_id; -- Block deletion of preceding baseline version -- if a new version is currently being captured. -- If a capture is in progress, the version being -- captured is l_maxV -- The "preceding version" is the highest-numbered -- version earlier than l_maxV with status = 'OK' SELECT max(baseline_version) INTO l_prevV FROM mgmt_cm_baseline_versions WHERE baseline_guid = p_baseline_id AND baseline_version < l_maxV AND version_status = VERSION_STATUS_OK; IF l_prevV IS NOT NULL AND p_version_number = l_prevV THEN SELECT job_execution_id INTO l_exec_id FROM mgmt_cm_baseline_versions WHERE baseline_guid = p_baseline_id AND baseline_version = l_maxV; IF l_exec_id IS NOT NULL THEN SELECT count(*) INTO l_count FROM mgmt_job_exec_summary WHERE source_execution_id = l_exec_id AND status_bucket = MGMT_JOBS.STATUS_BUCKET_RUNNING; IF l_count > 0 THEN raise_application_error( CANNOT_REMOVE_VERSION_ERR, CANNOT_REMOVE_VERSION_ERR_M); END IF; END IF; END IF; l_exec_id := null; l_count := 0; -- Make sure no comparison is currently comparing the -- baseline version. SELECT count(*) INTO l_count FROM ( SELECT bv.job_execution_id FROM mgmt_cm_baseline_versions bv, mgmt_cm_comparison_versions cv, mgmt_cm_comparisons c, mgmt_job_exec_summary js WHERE bv.baseline_version = p_version_number AND bv.baseline_guid = p_baseline_id AND bv.baseline_guid = c.left_id AND c.left_type = COMP_BASELINE_T AND c.comparison_guid = cv.comparison_guid AND cv.left_version = bv.baseline_version AND cv.job_execution_id = js.source_execution_id AND js.status_bucket = MGMT_JOBS.STATUS_BUCKET_RUNNING UNION ALL SELECT bv.job_execution_id FROM mgmt_cm_baseline_versions bv, mgmt_cm_comparison_versions cv, mgmt_cm_comparisons c, mgmt_job_exec_summary js WHERE bv.baseline_version = p_version_number AND bv.baseline_guid = p_baseline_id AND bv.baseline_guid = c.right_id AND c.right_type = COMP_BASELINE_T AND c.comparison_guid = cv.comparison_guid AND cv.right_version = bv.baseline_version AND cv.job_execution_id = js.source_execution_id AND js.status_bucket = MGMT_JOBS.STATUS_BUCKET_RUNNING ); IF l_count > 0 THEN raise_application_error( COMP_USING_BL_VERSION_ERR, COMP_USING_BL_VERSION_ERR_M); END IF; -- mgmt_cm_baseline_objects -- Remove object versions whose only version was this version IF l_maxV = p_version_number THEN DELETE FROM mgmt_cm_baseline_objects WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = MAX_VERSION_NUMBER; END IF; DELETE FROM mgmt_cm_baseline_objects WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = p_version_number; -- Adjust object versions whose first version was this version UPDATE mgmt_cm_baseline_objects SET first_version = p_version_number + 1 WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version > p_version_number; -- Adjust object versions whose last version was this version UPDATE mgmt_cm_baseline_objects SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND last_version = p_version_number AND first_version < p_version_number; -- mgmt_cm_baseline_dependents -- Remove object versions whose only version was this version IF l_maxV = p_version_number THEN DELETE FROM mgmt_cm_baseline_dependents WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = MAX_VERSION_NUMBER; END IF; DELETE FROM mgmt_cm_baseline_dependents WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = p_version_number; -- Adjust object versions whose first version was this version UPDATE mgmt_cm_baseline_dependents SET first_version = p_version_number + 1 WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version > p_version_number; -- Adjust object versions whose last version was this version UPDATE mgmt_cm_baseline_dependents SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND last_version = p_version_number AND first_version < p_version_number; -- mgmt_cm_baseline_objgrants -- Remove object versions whose only version was this version IF l_maxV = p_version_number THEN DELETE FROM mgmt_cm_baseline_objgrants WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = MAX_VERSION_NUMBER; END IF; DELETE FROM mgmt_cm_baseline_objgrants WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = p_version_number; -- Adjust object versions whose first version was this version UPDATE mgmt_cm_baseline_objgrants SET first_version = p_version_number + 1 WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version > p_version_number; -- Adjust object versions whose last version was this version UPDATE mgmt_cm_baseline_objgrants SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND last_version = p_version_number AND first_version < p_version_number; -- mgmt_cm_baseline_sysgrants -- Remove object versions whose only version was this version IF l_maxV = p_version_number THEN DELETE FROM mgmt_cm_baseline_sysgrants WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = MAX_VERSION_NUMBER; END IF; DELETE FROM mgmt_cm_baseline_sysgrants WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = p_version_number; -- Adjust object versions whose first version was this version UPDATE mgmt_cm_baseline_sysgrants SET first_version = p_version_number + 1 WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version > p_version_number; -- Adjust object versions whose last version was this version UPDATE mgmt_cm_baseline_sysgrants SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND last_version = p_version_number AND first_version < p_version_number; -- mgmt_cm_baseline_rolegrants -- Remove object versions whose only version was this version IF l_maxV = p_version_number THEN DELETE FROM mgmt_cm_baseline_rolegrants WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = MAX_VERSION_NUMBER; END IF; DELETE FROM mgmt_cm_baseline_rolegrants WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = p_version_number; -- Adjust object versions whose first version was this version UPDATE mgmt_cm_baseline_rolegrants SET first_version = p_version_number + 1 WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version > p_version_number; -- Adjust object versions whose last version was this version UPDATE mgmt_cm_baseline_rolegrants SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND last_version = p_version_number AND first_version < p_version_number; -- mgmt_cm_baseline_quotagrants -- Remove object versions whose only version was this version IF l_maxV = p_version_number THEN DELETE FROM mgmt_cm_baseline_quotagrants WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = MAX_VERSION_NUMBER; END IF; DELETE FROM mgmt_cm_baseline_quotagrants WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = p_version_number; -- Adjust object versions whose first version was this version UPDATE mgmt_cm_baseline_quotagrants SET first_version = p_version_number + 1 WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version > p_version_number; -- Adjust object versions whose last version was this version UPDATE mgmt_cm_baseline_quotagrants SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND last_version = p_version_number AND first_version < p_version_number; -- mgmt_cm_baseline_proxygrants -- Remove object versions whose only version was this version IF l_maxV = p_version_number THEN DELETE FROM mgmt_cm_baseline_proxygrants WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = MAX_VERSION_NUMBER; END IF; DELETE FROM mgmt_cm_baseline_proxygrants WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = p_version_number; -- Adjust object versions whose first version was this version UPDATE mgmt_cm_baseline_proxygrants SET first_version = p_version_number + 1 WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version > p_version_number; -- Adjust object versions whose last version was this version UPDATE mgmt_cm_baseline_proxygrants SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND last_version = p_version_number AND first_version < p_version_number; -- mgmt_cm_baseline_cons_groups -- Remove object versions whose only version was this version IF l_maxV = p_version_number THEN DELETE FROM mgmt_cm_baseline_cons_groups WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = MAX_VERSION_NUMBER; END IF; DELETE FROM mgmt_cm_baseline_cons_groups WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = p_version_number; -- Adjust object versions whose first version was this version UPDATE mgmt_cm_baseline_cons_groups SET first_version = p_version_number + 1 WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version > p_version_number; -- Adjust object versions whose last version was this version UPDATE mgmt_cm_baseline_cons_groups SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND last_version = p_version_number AND first_version < p_version_number; -- mgmt_cm_baseline_init_params -- Remove init param versions whose only version was this version IF l_maxV = p_version_number THEN DELETE FROM mgmt_cm_baseline_init_params WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = MAX_VERSION_NUMBER; END IF; DELETE FROM mgmt_cm_baseline_init_params WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = p_version_number; -- Adjust init param versions whose first version was this version UPDATE mgmt_cm_baseline_init_params SET first_version = p_version_number + 1 WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version > p_version_number; -- Adjust init param versions whose last version was this version UPDATE mgmt_cm_baseline_init_params SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND last_version = p_version_number AND first_version < p_version_number; -- mgmt_cm_baseline_dependencies IF l_maxV = p_version_number THEN DELETE FROM mgmt_cm_baseline_dependencies WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = MAX_VERSION_NUMBER; END IF; DELETE FROM mgmt_cm_baseline_dependencies WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version = p_version_number; -- Adjust init param versions whose first version was this version UPDATE mgmt_cm_baseline_dependencies SET first_version = p_version_number + 1 WHERE baseline_guid = p_baseline_id AND first_version = p_version_number AND last_version > p_version_number; -- Adjust init param versions whose last version was this version UPDATE mgmt_cm_baseline_dependencies SET last_version = p_version_number - 1 WHERE baseline_guid = p_baseline_id AND last_version = p_version_number AND first_version < p_version_number; -- Remove job execution corresponding to this baseline version -- Only remove the job if its jobtype is CaptureBaseline since now -- baseline version can be created through comparison job types BEGIN SELECT job_execution_id INTO l_exec_id FROM mgmt_cm_baseline_versions, mgmt_job, mgmt_job_exec_summary WHERE baseline_guid = p_baseline_id AND baseline_version = p_version_number AND job_execution_id = mgmt_job_exec_summary.execution_id AND mgmt_job.job_id = mgmt_job_exec_summary.job_id AND mgmt_job.job_type = 'CaptureBaseline'; IF (l_exec_id is not null) THEN BEGIN MGMT_JOBS.delete_job_execution(l_exec_id); EXCEPTION WHEN OTHERS THEN -- job does not exist IF SQLCODE = 6512 THEN NULL; END IF ; END; END IF; EXCEPTION WHEN OTHERS THEN -- no job execution id since jobtype could be comparison IF SQLCODE = 1403 THEN NULL; END IF ; END; -- Finally, remove the record of the version. DELETE FROM mgmt_cm_baseline_versions WHERE baseline_guid = p_baseline_id AND baseline_version = p_version_number; -- Update baseline's job_id in case this job has been removed SELECT count(*) INTO l_count FROM mgmt_cm_baselines WHERE baseline_guid = p_baseline_id AND EXISTS (SELECT * from mgmt_job WHERE mgmt_cm_baselines.job_id = mgmt_job.job_id); IF l_count = 0 THEN BEGIN SELECT mgmt_job_exec_summary.job_id INTO l_job_id FROM mgmt_job_exec_summary WHERE mgmt_job_exec_summary.execution_id = (SELECT job_execution_id FROM mgmt_cm_baseline_versions WHERE baseline_guid = p_baseline_id AND baseline_version = (SELECT MAX(baseline_version) FROM mgmt_cm_baseline_versions WHERE baseline_guid = p_baseline_id)); IF (l_job_id is not null) THEN UPDATE mgmt_cm_baselines SET job_id = l_job_id WHERE baseline_guid = p_baseline_id; END IF; EXCEPTION WHEN OTHERS THEN -- no max(version) since last one deleted IF SQLCODE = 1403 THEN NULL; END IF ; END; END IF; END remove_baseline_version; -- Remove an entire baseline procedure remove_baseline( p_baseline_id in raw) is l_job_id RAW(16); l_exec_ids MGMT_JOB_GUID_ARRAY; begin SELECT job_id INTO l_job_id FROM mgmt_cm_baselines WHERE baseline_guid = p_baseline_id; -- Only remove the job if its jobtype is CaptureBaseline since now -- baseline version can be created through comparison job types SELECT job_execution_id BULK COLLECT INTO l_exec_ids FROM mgmt_cm_baseline_versions, mgmt_job, mgmt_job_exec_summary WHERE baseline_guid = p_baseline_id AND job_execution_id = mgmt_job_exec_summary.execution_id AND mgmt_job.job_id = mgmt_job_exec_summary.job_id AND mgmt_job.job_type = 'CaptureBaseline'; -- Delete execution first. Should commit be 1 or 0? BEGIN MGMT_JOBS.delete_job_executions(p_execution_ids=>l_exec_ids, p_commit=>1); EXCEPTION WHEN OTHERS THEN -- job does not exist IF SQLCODE = 6512 THEN NULL; END IF ; END; -- Now delete (future) job. -- Stop execution first, delete job next. Should commit be 1 or 0? BEGIN MGMT_JOB_ENGINE.STOP_ALL_EXECUTIONS_WITH_ID(l_job_id); MGMT_JOBS.delete_job(p_job_id=>l_job_id, p_commit=>1); EXCEPTION WHEN OTHERS THEN -- job does not exist IF SQLCODE = 6512 THEN NULL; END IF ; END; -- Remove baseline DELETE FROM mgmt_cm_baselines WHERE baseline_guid = p_baseline_id; EXCEPTION WHEN OTHERS THEN -- no job id or execution id since job could be manually deleted IF SQLCODE = 1403 THEN NULL; END IF ; END remove_baseline; -- Create a comparison function create_comparison( p_name in varchar2, p_owner in varchar2, p_scope_spec in raw, p_left_source_type varchar2, -- 'B' or 'D' p_left_source_id raw, p_left_source_version number default 0, p_right_source_type varchar2, -- 'B' or 'D' p_right_source_id raw, p_right_source_version number default 0, p_description in varchar2 default null) return raw is l_comp_id RAW(16) := sys_guid(); l_left_source_name VARCHAR2(256); l_right_source_name VARCHAR2(256); begin IF p_left_source_type = 'B' THEN SELECT baseline_name INTO l_left_source_name FROM mgmt_cm_baselines WHERE baseline_guid = p_left_source_id; ELSIF p_left_source_type = 'D' THEN SELECT target_name INTO l_left_source_name FROM mgmt_targets WHERE target_guid = p_left_source_id; -- Else - Raise error (bad argument value) END IF; IF p_right_source_type = 'B' THEN SELECT baseline_name INTO l_right_source_name FROM mgmt_cm_baselines WHERE baseline_guid = p_right_source_id; ELSIF p_right_source_type = 'D' THEN SELECT target_name INTO l_right_source_name FROM mgmt_targets WHERE target_guid = p_right_source_id; -- Else - Raise error (bad argument value) END IF; INSERT INTO mgmt_cm_comparisons ( comparison_guid, comparison_name, comparison_owner, comparison_ss, description, left_type, left_id, left_name, left_version, right_type, right_id, right_name, right_version ) VALUES( l_comp_id, p_name, p_owner, p_scope_spec, p_description, p_left_source_type, p_left_source_id, l_left_source_name, p_left_source_version, p_right_source_type, p_right_source_id, l_right_source_name, p_right_source_version ); RETURN l_comp_id; END create_comparison; function get_comparison_id( p_name in varchar2, p_owner in varchar2) return raw is l_comp_id RAW(16); begin SELECT comparison_guid INTO l_comp_id FROM mgmt_cm_comparisons WHERE comparison_owner = p_owner AND comparison_name = p_name; RETURN l_comp_id; END get_comparison_id; -- Get a comparison's scope spec function get_comparison_scope_spec( p_comparison_id in raw) return raw is l_ss_id RAW(16); begin SELECT comparison_ss INTO l_ss_id FROM mgmt_cm_comparisons WHERE comparison_guid = p_comparison_id; RETURN l_ss_id; END get_comparison_scope_spec; procedure get_comparison_info (p_comparison_id in raw, p_comparison_info in out ComparisonInfoRecTyp) is CURSOR comp_rec_info_c (comp_id in raw) IS SELECT comparison_ss, left_type, left_id, left_version, right_type, right_id, right_version FROM mgmt_cm_comparisons WHERE comparison_guid = comp_id; begin OPEN comp_rec_info_c (p_comparison_id); FETCH comp_rec_info_c INTO p_comparison_info; CLOSE comp_rec_info_c; END get_comparison_info; function get_comparison_owner (p_comparison_id in raw) return varchar2 is l_comp_owner VARCHAR2(30); begin SELECT comparison_owner INTO l_comp_owner FROM mgmt_cm_comparisons WHERE comparison_guid = p_comparison_id; RETURN l_comp_owner; END get_comparison_owner; -- Add an entry to schema map, owner can be a comparison or synchronization procedure add_schema_map_entry (p_owner_id in raw, p_left_schema in varchar2, p_right_schema in varchar2) is begin INSERT INTO mgmt_cm_schema_maps (owner_id, left_schema, right_schema) VALUES (p_owner_id, p_left_schema, p_right_schema); END add_schema_map_entry; -- Add a comparison version function add_comparison_version( p_comparison_id in raw) return integer is l_newV INTEGER; begin SELECT MAX(comparison_version) INTO l_newV FROM mgmt_cm_comparison_versions WHERE comparison_guid = p_comparison_id; IF l_newV IS NULL THEN l_newV := 1; ELSE l_newV := l_newV + 1; END IF; INSERT INTO mgmt_cm_comparison_versions (comparison_guid, comparison_version) VALUES (p_comparison_id, l_newV); RETURN l_newV; END add_comparison_version; -- Get current comparison version function get_current_comparison_version( p_comparison_id in raw) return integer is l_currV INTEGER; begin SELECT MAX(comparison_version) INTO l_currV FROM mgmt_cm_comparison_versions WHERE comparison_guid = p_comparison_id; RETURN l_currV; END get_current_comparison_version; -- Set a comparison version's compare date procedure set_compare_date( p_comparison_id in raw, p_version_number in number, p_execution_id in raw) is TYPE CompDate IS RECORD ( l_compare_date date, l_timezone varchar2(64)); compare_date CompDate; begin BEGIN SELECT start_time, timezone_region INTO compare_date FROM mgmt_job_exec_summary WHERE execution_id = p_execution_id AND rownum = 1; EXCEPTION WHEN OTHERS THEN NULL; END; IF compare_date.l_compare_date IS NULL THEN compare_date.l_compare_date := SYSDATE; END IF; UPDATE mgmt_cm_comparison_versions SET comparison_time = compare_date.l_compare_date, timezone_region = compare_date.l_timezone WHERE comparison_guid = p_comparison_id AND comparison_version = p_version_number; END set_compare_date; -- Add or update object in a comparison procedure add_obj_to_comparison( p_comparison_id in raw, p_object_type in number, p_object_schema in varchar2, p_object_name in varchar2, p_obj_state in number, p_left_ddl_time in date, p_left_hash_key in varchar2, p_right_ddl_time in date, p_right_hash_key in varchar2, p_version_number in number, p_object_diffs in blob default null, p_prev_version_action number default REMOVE_PREV_VERSION, p_grants_hash in varchar2 default null, p_comments_hash in varchar2 default null) is begin if p_prev_version_action = REMOVE_PREV_VERSION then -- This call will set the LAST_VERSION number of the existing version -- to the previous version. remove_obj_from_comparison( p_comparison_id, p_object_type, p_object_schema, p_object_name, p_version_number ); end if; INSERT INTO mgmt_cm_comparison_objects (comparison_guid, object_type, object_schema, object_name, first_version, last_left_ddl_time, left_hash_value, last_right_ddl_time, right_hash_value, grants_diff_hash, comments_diff_hash, state, differences) VALUES( p_comparison_id, p_object_type, p_object_schema, p_object_name, p_version_number, p_left_ddl_time, p_left_hash_key, p_right_ddl_time, p_right_hash_key, p_grants_hash, p_comments_hash, p_obj_state, p_object_diffs ); END add_obj_to_comparison; function add_obj_to_comparison( p_comparison_id in raw, p_object_type in number, p_object_schema in varchar2, p_object_name in varchar2, p_obj_state in number, p_left_ddl_time in date, p_left_hash_key in varchar2, p_right_ddl_time in date, p_right_hash_key in varchar2, p_version_number in number, p_prev_version_action number default REMOVE_PREV_VERSION, p_grants_hash in varchar2 default null, p_comments_hash in varchar2 default null) return blob is l_differences blob; begin if p_prev_version_action = REMOVE_PREV_VERSION then -- This call will set the LAST_VERSION number of the existing version -- to the previous version. remove_obj_from_comparison( p_comparison_id, p_object_type, p_object_schema, p_object_name, p_version_number ); end if; INSERT INTO mgmt_cm_comparison_objects (comparison_guid, object_type, object_schema, object_name, first_version, last_left_ddl_time, left_hash_value, last_right_ddl_time, right_hash_value, grants_diff_hash, comments_diff_hash, state, differences) VALUES( p_comparison_id, p_object_type, p_object_schema, p_object_name, p_version_number, p_left_ddl_time, p_left_hash_key, p_right_ddl_time, p_right_hash_key, p_grants_hash, p_comments_hash, p_obj_state, empty_blob()) RETURNING differences INTO l_differences; RETURN l_differences; END add_obj_to_comparison; -- Remove an object from a comparison -- (i.e., both objects no longer present in the database) procedure remove_obj_from_comparison( p_comparison_id in raw, p_object_type in number, p_object_schema in varchar2, p_object_name in varchar2, p_version_number in number) is begin UPDATE mgmt_cm_comparison_objects SET last_version = p_version_number - 1 WHERE comparison_guid = p_comparison_id AND object_type = p_object_type AND (object_schema = p_object_schema OR object_schema IS NULL) AND object_name = p_object_name AND last_version = MAX_VERSION_NUMBER; END remove_obj_from_comparison; procedure add_init_prm_to_comp( p_comparison_id in raw, p_param_name in varchar2, p_state in number, p_left_param_value in varchar2, p_right_param_value in varchar2, p_version_number in number) is begin remove_init_prm_from_comp( p_comparison_id, p_param_name, p_version_number); INSERT INTO mgmt_cm_comparison_init_prms( comparison_guid, param_name, state, first_version, left_param_value, right_param_value) VALUES( p_comparison_id, p_param_name, p_state, p_version_number, p_left_param_value, p_right_param_value); END add_init_prm_to_comp; procedure remove_init_prm_from_comp( p_comparison_id in raw, p_param_name in varchar2, p_version_number in number) is begin UPDATE mgmt_cm_comparison_init_prms SET last_version = p_version_number - 1 WHERE comparison_guid = p_comparison_id AND param_name = p_param_name AND last_version = MAX_VERSION_NUMBER; END remove_init_prm_from_comp; -- Get objects in a comparison version procedure get_comparison_version_objects( p_comparison_id in raw, p_version_number in number, p_objects_cursor in out weak_cur_typ, p_mode in number default ALL_OBJECTS) is begin if p_mode = ALL_OBJECTS then OPEN p_objects_cursor FOR SELECT object_type, object_schema, object_name, state, last_left_ddl_time, last_right_ddl_time, left_hash_value, right_hash_value, grants_diff_hash, comments_diff_hash FROM mgmt_cm_comparison_objects WHERE comparison_guid = p_comparison_id AND first_version <= p_version_number AND last_version >= p_version_number ORDER BY object_type ASC, object_schema ASC, object_name ASC; elsif p_mode = MTIME_ONLY then OPEN p_objects_cursor FOR SELECT object_type, object_schema, object_name, state, last_left_ddl_time, last_right_ddl_time, left_hash_value, right_hash_value, grants_diff_hash, comments_diff_hash FROM mgmt_cm_comparison_objects WHERE comparison_guid = p_comparison_id AND first_version <= p_version_number AND last_version >= p_version_number AND object_schema IS NOT null AND object_type != DBLINK_MATCH AND object_type != MVIEW_LOG_MATCH AND object_type != MVIEW_MATCH AND object_type != UDT_MATCH ORDER BY object_type ASC, object_schema ASC, object_name ASC; elsif p_mode = NONSCHEMA_NONMTIME_OBJECTS then OPEN p_objects_cursor FOR SELECT object_type, object_schema, object_name, state, last_left_ddl_time, last_right_ddl_time, left_hash_value, right_hash_value, grants_diff_hash, comments_diff_hash FROM mgmt_cm_comparison_objects WHERE comparison_guid = p_comparison_id AND first_version <= p_version_number AND last_version >= p_version_number AND (object_schema IS null OR object_type = DBLINK_MATCH OR object_type = MVIEW_LOG_MATCH OR object_type = MVIEW_MATCH OR object_type = UDT_MATCH) ORDER BY object_type ASC, object_schema ASC, object_name ASC; end if; END get_comparison_version_objects; procedure get_comp_version_init_params( p_comparison_id in raw, p_version_number in number, p_params_cursor in out weak_cur_typ) is begin OPEN p_params_cursor FOR SELECT param_name, state, left_param_value, right_param_value FROM mgmt_cm_comparison_init_prms WHERE comparison_guid = p_comparison_id AND first_version <= p_version_number AND last_version >= p_version_number ORDER BY param_name ASC; END get_comp_version_init_params; -- Get the differences for a single object in a comparison version function get_comparison_obj_differences( p_comparison_id in raw, p_version_number in number, p_obj_type in number, p_obj_schema in varchar2, p_obj_name in varchar2) return blob is l_diffs BLOB; begin SELECT differences INTO l_diffs FROM mgmt_cm_comparison_objects WHERE comparison_guid = p_comparison_id AND object_type = p_obj_type AND (object_schema = p_obj_schema OR object_schema IS NULL) AND object_name = p_obj_name; RETURN l_diffs; END get_comparison_obj_differences; -- Remove a comparison version procedure remove_comparison_version( p_comparison_id in raw, p_version_number in number) is l_exec_id RAW(16); l_maxV INTEGER; l_prevV INTEGER; l_count NUMBER := 0; l_job_id MGMT_JOB.JOB_ID%TYPE; begin -- Remove object versions whose only version was this version SELECT MAX(comparison_version) INTO l_maxV FROM mgmt_cm_comparison_versions WHERE comparison_guid = p_comparison_id; -- Block deletion of preceding comparison version -- if a new version is currently being processed. -- If a comparison is in progress, the version being -- processd is l_maxV and its preceding version -- is l_maxV - 1. -- The "preceding version" is the highest-numbered -- version earlier than l_maxV with status = 'OK' SELECT max(comparison_version) INTO l_prevV FROM mgmt_cm_comparison_versions WHERE comparison_guid = p_comparison_id AND comparison_version < l_maxV AND version_status = VERSION_STATUS_OK; IF l_prevV IS NOT NULL AND p_version_number = l_prevV THEN SELECT job_execution_id INTO l_exec_id FROM mgmt_cm_comparison_versions WHERE comparison_guid = p_comparison_id AND comparison_version = l_maxV; IF l_exec_id IS NOT NULL THEN SELECT count(*) INTO l_count FROM mgmt_job_exec_summary WHERE source_execution_id = l_exec_id AND status_bucket = MGMT_JOBS.STATUS_BUCKET_RUNNING; IF l_count > 0 THEN raise_application_error( CANNOT_REMOVE_VERSION_ERR, CANNOT_REMOVE_VERSION_ERR_M); END IF; END IF; END IF; l_exec_id := null; l_count := 0; IF l_maxV = p_version_number THEN DELETE FROM mgmt_cm_comparison_objects WHERE comparison_guid = p_comparison_id AND first_version = p_version_number AND last_version = MAX_VERSION_NUMBER; END IF; DELETE FROM mgmt_cm_comparison_objects WHERE comparison_guid = p_comparison_id AND first_version = p_version_number AND last_version = p_version_number; -- Adjust object versions whose first version was this version UPDATE mgmt_cm_comparison_objects SET first_version = p_version_number + 1 WHERE comparison_guid = p_comparison_id AND first_version = p_version_number AND last_version > p_version_number; -- Adjust object versions whose last version was this version UPDATE mgmt_cm_comparison_objects SET last_version = p_version_number - 1 WHERE comparison_guid = p_comparison_id AND last_version = p_version_number AND first_version < p_version_number; -- Init params IF l_maxV = p_version_number THEN DELETE FROM mgmt_cm_comparison_init_prms WHERE comparison_guid = p_comparison_id AND first_version = p_version_number AND last_version = MAX_VERSION_NUMBER; END IF; DELETE FROM mgmt_cm_comparison_init_prms WHERE comparison_guid = p_comparison_id AND first_version = p_version_number AND last_version = p_version_number; -- Adjust object versions whose first version was this version UPDATE mgmt_cm_comparison_init_prms SET first_version = p_version_number + 1 WHERE comparison_guid = p_comparison_id AND first_version = p_version_number AND last_version > p_version_number; -- Adjust object versions whose last version was this version UPDATE mgmt_cm_comparison_init_prms SET last_version = p_version_number - 1 WHERE comparison_guid = p_comparison_id AND last_version = p_version_number AND first_version < p_version_number; -- Remove job execution corresponding to this comparison version SELECT job_execution_id INTO l_exec_id FROM mgmt_cm_comparison_versions WHERE comparison_guid = p_comparison_id AND comparison_version = p_version_number; BEGIN MGMT_JOBS.delete_job_execution(l_exec_id); EXCEPTION WHEN OTHERS THEN -- job does not exist IF SQLCODE = 6512 THEN NULL; END IF ; END; -- Finally, remove the record of the version. DELETE FROM mgmt_cm_comparison_versions WHERE comparison_guid = p_comparison_id AND comparison_version = p_version_number; -- Update comparison's job_id in case this job has been removed SELECT count(*) INTO l_count FROM mgmt_cm_comparisons WHERE comparison_guid = p_comparison_id AND EXISTS (SELECT * from mgmt_job WHERE mgmt_cm_comparisons.job_id = mgmt_job.job_id); IF l_count = 0 THEN BEGIN SELECT mgmt_job_exec_summary.job_id INTO l_job_id FROM mgmt_job_exec_summary WHERE mgmt_job_exec_summary.execution_id = (SELECT job_execution_id FROM mgmt_cm_comparison_versions WHERE comparison_guid = p_comparison_id AND comparison_version = (SELECT MAX(comparison_version) FROM mgmt_cm_comparison_versions WHERE comparison_guid = p_comparison_id)); IF (l_job_id is not null) THEN UPDATE mgmt_cm_comparisons SET job_id = l_job_id WHERE comparison_guid = p_comparison_id; END IF; EXCEPTION WHEN OTHERS THEN -- no max(version) since last one deleted IF SQLCODE = 1403 THEN NULL; END IF ; END; END IF; END remove_comparison_version; -- Remove an entire comparison procedure remove_comparison( p_comparison_id in raw) is l_job_id RAW(16); l_exec_ids MGMT_JOB_GUID_ARRAY; begin SELECT job_id INTO l_job_id FROM mgmt_cm_comparisons WHERE comparison_guid = p_comparison_id; SELECT job_execution_id BULK COLLECT INTO l_exec_ids FROM mgmt_cm_comparison_versions WHERE comparison_guid = p_comparison_id; -- Delete execution first. Should commit be 1 or 0? BEGIN MGMT_JOBS.delete_job_executions (p_execution_ids=>l_exec_ids, p_commit=>1); EXCEPTION WHEN OTHERS THEN -- job does not exist IF SQLCODE = 6512 THEN NULL; END IF ; END; -- Now delete (future) job. -- Stop execution first, delete job next. Should commit be 1 or 0? BEGIN MGMT_JOB_ENGINE.STOP_ALL_EXECUTIONS_WITH_ID(l_job_id); MGMT_JOBS.delete_job(p_job_id=>l_job_id, p_commit=>1); EXCEPTION WHEN OTHERS THEN -- job does not exist IF SQLCODE = 6512 THEN NULL; END IF ; END; -- Delete Comparison DELETE FROM mgmt_cm_comparisons WHERE comparison_guid = p_comparison_id; END remove_comparison; -- Set a baseline version's job execution id procedure set_execution_id( p_baseline_id in raw, p_version_number in number, p_execution_id in raw) is begin UPDATE mgmt_cm_baseline_versions SET job_execution_id = p_execution_id WHERE baseline_guid = p_baseline_id AND baseline_version = p_version_number; END set_execution_id; -- Set a baseline version's job execution id procedure set_baseline_db_version( p_baseline_id in raw, p_version_number in number, p_db_version in varchar2) is begin UPDATE mgmt_cm_baseline_versions SET db_version = substr(p_db_version, 1, 20) WHERE baseline_guid = p_baseline_id AND baseline_version = p_version_number; END set_baseline_db_version; -- Set a baseline's job id procedure set_baseline_job_id( p_baseline_id in raw, p_job_id in raw) is begin UPDATE mgmt_cm_baselines SET job_id = p_job_id WHERE baseline_guid = p_baseline_id; END set_baseline_job_id; -- Set a comparison version's job execution id procedure set_comparison_execution_id( p_comparison_id in raw, p_version_number in number, p_execution_id in raw) is begin UPDATE mgmt_cm_comparison_versions SET job_execution_id = p_execution_id WHERE comparison_guid = p_comparison_id AND comparison_version = p_version_number; END set_comparison_execution_id; -- Set a comparison's job id procedure set_comparison_job_id( p_comparison_id in raw, p_job_id in raw) is begin UPDATE mgmt_cm_comparisons SET job_id = p_job_id WHERE comparison_guid = p_comparison_id; END set_comparison_job_id; ------------------------------------------------- -- Synchronize ------------------------------------------------- function create_synchronization( p_name in varchar2, p_owner in varchar2, p_scope_spec in raw, p_source_type varchar2, -- 'B' or 'D' p_source_id raw, p_source_version number default 0, p_target_id raw, p_description in varchar2 default null) return raw is l_synch_id RAW(16) := sys_guid(); l_source_name VARCHAR2(256); l_target_name VARCHAR2(256); begin IF p_source_type = 'B' THEN SELECT baseline_name INTO l_source_name FROM mgmt_cm_baselines WHERE baseline_guid = p_source_id; ELSIF p_source_type = 'D' THEN SELECT target_name INTO l_source_name FROM mgmt_targets WHERE target_guid = p_source_id; -- Else - Raise error (bad argument value) END IF; SELECT target_name INTO l_target_name FROM mgmt_targets WHERE target_guid = p_target_id; INSERT INTO mgmt_cm_synchronizations ( synch_guid, synch_name, synch_owner, synch_ss, description, source_type, source_id, source_name, source_version, target_id, target_name ) VALUES( l_synch_id, p_name, p_owner, p_scope_spec, p_description, p_source_type, p_source_id, l_source_name, p_source_version, p_target_id, l_target_name ); RETURN l_synch_id; END create_synchronization; function get_synchronization_id( p_name in varchar2, p_owner in varchar2) return raw is l_synch_id RAW(16); begin SELECT synch_guid INTO l_synch_id FROM mgmt_cm_synchronizations WHERE synch_owner = p_owner AND synch_name = p_name; RETURN l_synch_id; END get_synchronization_id; -- Get a synchronization's scope spec function get_synchronization_scope_spec( p_synch_id in raw) return raw is l_ss_id RAW(16); begin SELECT synch_ss INTO l_ss_id FROM mgmt_cm_synchronizations WHERE synch_guid = p_synch_id; RETURN l_ss_id; END get_synchronization_scope_spec; function add_synchronization_version( p_synch_id in raw, p_processing_mode in number) return integer is l_newV INTEGER; l_vState INTEGER := -1; begin SELECT MAX(synch_version) INTO l_newV FROM mgmt_cm_synch_versions WHERE synch_guid = p_synch_id; IF l_newV IS NULL THEN l_newV := 1; ELSE -- Check for version waiting for user review of -- comparison results; do not create a new version -- if that is the case. SELECT version_state INTO l_vState FROM mgmt_cm_synch_versions WHERE synch_guid = p_synch_id AND synch_version = l_newV; IF l_vState != 2 THEN -- SYNCH_COMPARISON_SUCCEEDED l_newV := l_newV + 1; END IF; END IF; IF l_vState != 2 THEN INSERT INTO mgmt_cm_synch_versions (synch_guid, synch_version, processing_mode) VALUES (p_synch_id, l_newV, p_processing_mode); END IF; RETURN l_newV; END add_synchronization_version; -- Set a synch version's job execution id procedure set_synch_execution_id( p_synch_id in raw, p_version_number in number, p_execution_id in raw) is begin UPDATE mgmt_cm_synch_versions SET job_execution_id = p_execution_id WHERE synch_guid = p_synch_id AND synch_version = p_version_number; END set_synch_execution_id; -- Set a synchronization version's synch date procedure set_synch_date( p_synch_id in raw, p_version_number in number, p_execution_id in raw) is TYPE SynchDate IS RECORD ( l_synch_date date, l_timezone varchar2(64)); synch_date SynchDate; begin BEGIN SELECT start_time, timezone_region INTO synch_date FROM mgmt_job_exec_summary WHERE execution_id = p_execution_id AND rownum = 1; EXCEPTION WHEN OTHERS THEN NULL; END; IF synch_date.l_synch_date IS NULL THEN synch_date.l_synch_date := SYSDATE; END IF; UPDATE mgmt_cm_synch_versions SET generation_time = synch_date.l_synch_date, timezone_region = synch_date.l_timezone WHERE synch_guid = p_synch_id AND synch_version = p_version_number; END set_synch_date; -- Set a synchronization version's synch script function set_synch_script( p_synch_id in raw, p_version_number in number) return blob is l_script blob; begin UPDATE mgmt_cm_synch_versions SET script = empty_blob() WHERE synch_guid = p_synch_id AND synch_version = p_version_number RETURNING script INTO l_script; RETURN l_script; END set_synch_script; -- Add a script line function add_synch_script_line( p_synch_id in raw, p_version_number in number, p_object_type in number, p_object_schema in varchar2, p_object_name in varchar2, p_script_line_no in number, p_script_step_no in number, p_script_line_type in varchar2, p_script_section in varchar2, p_line_edited in varchar2) return blob is l_script_line blob; begin INSERT INTO mgmt_cm_synch_scripts (synch_guid, synch_version, object_type, object_schema, object_name, script_line_no, script_step_no, script_line_type, script_section, line_edited, script_line) VALUES (p_synch_id, p_version_number, p_object_type, p_object_schema, p_object_name, p_script_line_no, p_script_step_no, p_script_line_type, p_script_section, p_line_edited, empty_blob()) RETURNING script_line INTO l_script_line; RETURN l_script_line; END add_synch_script_line; -------------------------------------------------- -- Add an impact report entry -- Returns a weakly typed cursor that contains -- problem (blob) and action (blob) -------------------------------------------------- procedure add_synch_impact_entry( p_synch_id in raw, p_version_number in number, p_object_type in number, p_object_schema in varchar2, p_object_name in varchar2, p_message_type in varchar2, p_message_severity in number, p_result_cursor OUT weak_cur_typ) is l_rowid ROWID := null; begin INSERT INTO mgmt_cm_synch_impact_reports (synch_guid, synch_version, object_type, object_schema, object_name, message_type, message_severity, problem, action) VALUES (p_synch_id, p_version_number, p_object_type, p_object_schema, p_object_name, p_message_type, p_message_severity, empty_blob(), empty_blob()) RETURNING rowid into l_rowid; OPEN p_result_cursor FOR select problem, action from mgmt_cm_synch_impact_reports where rowid = l_rowid; end add_synch_impact_entry; -- Add an object to a synch version. function add_obj_to_synch( p_synch_id in raw, p_object_type in number, p_object_schema in varchar2, p_object_name in varchar2, p_comp_state in number, p_source_hash_key in varchar2, p_target_hash_key in varchar2, p_version_number in number, p_grants_hash in varchar2 default null, p_comments_hash in varchar2 default null, p_addnl_info in varchar2 default null, p_excluded in number default 0) return blob is l_differences blob; begin -- Set last_version of existing version (if any) to prev. version. remove_obj_from_synch( p_synch_id, p_object_type, p_object_schema, p_object_name, p_version_number ); insert into mgmt_cm_synch_objects ( synch_guid, object_type, object_schema, object_name, source_hash_value, target_hash_value, comp_state, first_version, grants_diff_hash, comments_diff_hash, addnl_info, excluded, sxml_document ) values ( p_synch_id, p_object_type, p_object_schema, p_object_name, p_source_hash_key, p_target_hash_key, p_comp_state, p_version_number, p_grants_hash, p_comments_hash, p_addnl_info, p_excluded, empty_blob() ) returning sxml_document into l_differences; return l_differences; END add_obj_to_synch; procedure remove_obj_from_synch( p_synch_id in raw, p_object_type in number, p_object_schema in varchar2, p_object_name in varchar2, p_version_number in number) is begin UPDATE mgmt_cm_synch_objects SET last_version = p_version_number - 1 WHERE synch_guid = p_synch_id AND object_type = p_object_type AND (object_schema = p_object_schema OR object_schema IS NULL) AND object_name = p_object_name AND last_version = MAX_VERSION_NUMBER; END remove_obj_from_synch; -- Set a Synchronization's job id procedure set_synch_job_id( p_synch_id in raw, p_job_id in raw) is begin UPDATE mgmt_cm_synchronizations SET job_id = p_job_id WHERE synch_guid = p_synch_id; END set_synch_job_id; -- Remove an entire synchronization procedure remove_synchronization( p_synch_id in raw) is l_job_id RAW(16); l_exec_ids MGMT_JOB_GUID_ARRAY; begin SELECT job_execution_id BULK COLLECT INTO l_exec_ids FROM mgmt_cm_synch_versions WHERE synch_guid = p_synch_id; -- Delete execution first. Should commit be 1 or 0? BEGIN MGMT_JOBS.delete_job_executions (p_execution_ids=>l_exec_ids, p_commit=>1); EXCEPTION WHEN OTHERS THEN -- job does not exist IF SQLCODE = 6512 THEN NULL; END IF ; END; -- Delete interactive comparison jobs BEGIN SELECT comparison_job_exec_id BULK COLLECT INTO l_exec_ids FROM mgmt_cm_synch_versions v, mgmt_job_exec_summary s WHERE v.synch_guid = p_synch_id AND v.comparison_job_exec_id = s.execution_id; MGMT_JOBS.delete_job_executions (p_execution_ids=>l_exec_ids, p_commit=>1); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN -- job does not exist IF SQLCODE = 6512 THEN NULL; END IF ; END; -- Delete interactive generation jobs BEGIN SELECT generation_job_exec_id BULK COLLECT INTO l_exec_ids FROM mgmt_cm_synch_versions v, mgmt_job_exec_summary s WHERE v.synch_guid = p_synch_id AND v.generation_job_exec_id = s.execution_id; MGMT_JOBS.delete_job_executions (p_execution_ids=>l_exec_ids, p_commit=>1); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN -- job does not exist IF SQLCODE = 6512 THEN NULL; END IF ; END; -- Now delete (future) job. -- Stop execution first, delete job next. Should commit be 1 or 0? SELECT job_id INTO l_job_id FROM mgmt_cm_synchronizations WHERE synch_guid = p_synch_id; BEGIN MGMT_JOB_ENGINE.STOP_ALL_EXECUTIONS_WITH_ID(l_job_id); MGMT_JOBS.delete_job(p_job_id=>l_job_id, p_commit=>1); EXCEPTION WHEN OTHERS THEN -- job does not exist IF SQLCODE = 6512 THEN NULL; END IF ; END; -- Delete Synchronization DELETE FROM mgmt_cm_synchronizations WHERE synch_guid = p_synch_id; END remove_synchronization; -- Remove a synchronization version procedure remove_synchronization_version( p_synch_id in raw, p_version_number in number) is l_exec_id RAW(16); l_maxV INTEGER; l_prevV INTEGER; l_count NUMBER := 0; l_job_id MGMT_JOB.JOB_ID%TYPE; begin -- Remove object versions whose only version was this version SELECT MAX(synch_version) INTO l_maxV FROM mgmt_cm_synch_versions WHERE synch_guid = p_synch_id; -- Block deletion of preceding synchronization version -- if a new version is currently being processed. -- If a synchronization is in progress, the version -- being processd is l_maxV and its preceding -- version is l_maxV - 1. -- The "preceding version" is the highest-numbered -- version earlier than l_maxV with status = 'OK' SELECT max(synch_version) INTO l_prevV FROM mgmt_cm_synch_versions WHERE synch_guid = p_synch_id AND synch_version < l_maxV AND version_status = VERSION_STATUS_OK; IF l_prevV IS NOT NULL AND p_version_number = l_prevV THEN SELECT job_execution_id INTO l_exec_id FROM mgmt_cm_synch_versions WHERE synch_guid = p_synch_id AND synch_version = l_maxV; IF l_exec_id IS NOT NULL THEN SELECT count(*) INTO l_count FROM mgmt_job_exec_summary WHERE source_execution_id = l_exec_id AND status_bucket = MGMT_JOBS.STATUS_BUCKET_RUNNING; IF l_count > 0 THEN raise_application_error( CANNOT_REMOVE_VERSION_ERR, CANNOT_REMOVE_VERSION_ERR_M); END IF; END IF; END IF; l_exec_id := null; l_count := 0; IF l_maxV = p_version_number THEN DELETE FROM mgmt_cm_synch_objects WHERE synch_guid = p_synch_id AND first_version = p_version_number AND last_version = MAX_VERSION_NUMBER; END IF; DELETE FROM mgmt_cm_synch_objects WHERE synch_guid = p_synch_id AND first_version = p_version_number AND last_version = p_version_number; -- Adjust object versions whose first version was this version UPDATE mgmt_cm_synch_objects SET first_version = p_version_number + 1 WHERE synch_guid = p_synch_id AND first_version = p_version_number AND last_version > p_version_number; -- Adjust object versions whose last version was this version UPDATE mgmt_cm_synch_objects SET last_version = p_version_number - 1 WHERE synch_guid = p_synch_id AND last_version = p_version_number AND first_version < p_version_number; -- Remove job execution corresponding to this synch version SELECT job_execution_id INTO l_exec_id FROM mgmt_cm_synch_versions WHERE synch_guid = p_synch_id AND synch_version = p_version_number; BEGIN MGMT_JOBS.delete_job_execution(l_exec_id); EXCEPTION WHEN OTHERS THEN -- job does not exist IF SQLCODE = 6512 THEN NULL; END IF ; END; -- Remove interactive mode comparison job execution corresponding to this synch version BEGIN SELECT comparison_job_exec_id INTO l_exec_id FROM mgmt_cm_synch_versions v, mgmt_job_exec_summary s WHERE v.synch_guid = p_synch_id AND v.synch_version = p_version_number AND v.comparison_job_exec_id = s.execution_id; MGMT_JOBS.delete_job_execution(l_exec_id); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN -- job does not exist IF SQLCODE = 6512 THEN NULL; END IF ; END; -- Remove interactive generation job execution corresponding to this synch version BEGIN SELECT generation_job_exec_id INTO l_exec_id FROM mgmt_cm_synch_versions v, mgmt_job_exec_summary s WHERE v.synch_guid = p_synch_id AND v.synch_version = p_version_number AND v.generation_job_exec_id = s.execution_id; MGMT_JOBS.delete_job_execution(l_exec_id); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN -- job does not exist IF SQLCODE = 6512 THEN NULL; END IF ; END; -- Clean out the script lines for this version (bug 7517202) DELETE FROM mgmt_cm_synch_scripts WHERE synch_guid = p_synch_id AND synch_version = p_version_number; -- Likewise for impact report entries DELETE FROM mgmt_cm_synch_impact_reports WHERE synch_guid = p_synch_id AND synch_version = p_version_number; -- Finally, remove the record of the version. DELETE FROM mgmt_cm_synch_versions WHERE synch_guid = p_synch_id AND synch_version = p_version_number; -- Update synch's job_id in case this job has been removed SELECT count(*) INTO l_count FROM mgmt_cm_synchronizations WHERE synch_guid = p_synch_id AND EXISTS (SELECT * from mgmt_job WHERE mgmt_cm_synchronizations.job_id = mgmt_job.job_id); IF l_count = 0 THEN BEGIN SELECT mgmt_job_exec_summary.job_id INTO l_job_id FROM mgmt_job_exec_summary WHERE mgmt_job_exec_summary.execution_id = (SELECT job_execution_id FROM mgmt_cm_synch_versions WHERE synch_guid = p_synch_id AND synch_version = (SELECT MAX(synch_version) FROM mgmt_cm_synch_versions WHERE synch_guid = p_synch_id)); IF (l_job_id is not null) THEN UPDATE mgmt_cm_synchronizations SET job_id = l_job_id WHERE synch_guid = p_synch_id; END IF; EXCEPTION WHEN OTHERS THEN -- no max(version) since last one deleted IF SQLCODE = 1403 THEN NULL; END IF ; END; END IF; END remove_synchronization_version; ------------------------------------------------- -- Generate DDL -- ------------------------------------------------- -- Reset DDL -- Side effect: Reset DDL, DDL_STATUS, DDL_TIME procedure drop_baseline_version_ddl (p_baseline_guid IN RAW ,p_baseline_version IN NUMBER ) is l_lob mgmt_cm_baseline_versions.ddl%type; begin -- Deallocate space for blob using TRIM SELECT ddl INTO l_lob FROM MGMT_CM_BASELINE_VERSIONS WHERE baseline_guid = p_baseline_guid AND baseline_version = p_baseline_version FOR UPDATE; IF (l_lob IS NOT NULL) THEN dbms_lob.trim(l_lob, 0); END IF; UPDATE MGMT_CM_BASELINE_VERSIONS SET ddl_status = STATUS_NEVERRUN, ddl_time = NULL WHERE baseline_guid = p_baseline_guid AND baseline_version = p_baseline_version; end drop_baseline_version_ddl; function get_ddl_for_generate (p_baseline_guid IN RAW ,p_baseline_version IN NUMBER ) return blob is l_lob blob; begin SELECT ddl INTO l_lob FROM MGMT_CM_BASELINE_VERSIONS WHERE baseline_guid = p_baseline_guid AND baseline_version = p_baseline_version FOR UPDATE; IF (l_lob IS NOT NULL) THEN dbms_lob.trim(l_lob, 0); ELSE UPDATE MGMT_CM_BASELINE_VERSIONS SET ddl = EMPTY_BLOB() WHERE baseline_guid = p_baseline_guid AND baseline_version = p_baseline_version RETURNING ddl INTO l_lob; END IF; return l_lob; end get_ddl_for_generate; -- Start Generate DDL -- Side effetc: Turn ddl_status code to 'Running' procedure start_generate_ddl (p_baseline_guid IN RAW ,p_baseline_version IN NUMBER ) is begin UPDATE MGMT_CM_BASELINE_VERSIONS SET ddl_status = MGMT_CM.STATUS_RUNNING WHERE baseline_guid = p_baseline_guid AND baseline_version = p_baseline_version; end start_generate_ddl; -- End Generate DDL -- Side effetc: Save DDL, Turn ddl_status code to 'Ok' procedure end_generate_ddl (p_baseline_guid IN RAW ,p_baseline_version IN NUMBER ) is begin UPDATE MGMT_CM_BASELINE_VERSIONS SET ddl_status = MGMT_CM.STATUS_OK, ddl_time = SYSDATE WHERE baseline_guid = p_baseline_guid AND baseline_version = p_baseline_version; end end_generate_ddl; -- Fail Generate DDL -- Side effetc: Turn ddl_status code to 'Problem' procedure fail_generate_ddl (p_baseline_guid IN RAW ,p_baseline_version IN NUMBER ) is l_lob mgmt_cm_baseline_versions.ddl%type; begin -- Deallocate space for blob using TRIM SELECT ddl INTO l_lob FROM MGMT_CM_BASELINE_VERSIONS WHERE baseline_guid = p_baseline_guid AND baseline_version = p_baseline_version FOR UPDATE; IF (l_lob IS NOT NULL) THEN dbms_lob.trim(l_lob, 0); END IF; UPDATE MGMT_CM_BASELINE_VERSIONS SET ddl_status = MGMT_CM.STATUS_PROBLEM WHERE baseline_guid = p_baseline_guid AND baseline_version = p_baseline_version; end fail_generate_ddl; -- Check if current em user has the right to delete -- an entire baseline or comparison -- returns 0 if can not remove -- returns 1 if can remove function has_remove_priv(p_guid IN RAW ,p_type IN VARCHAR2) return NUMBER is l_em_user VARCHAR2(256) := MGMT_USER.get_current_em_user(); l_owner MGMT_CM_BASELINES.BASELINE_OWNER%TYPE; begin -- Super user can remove everything. IF (MGMT_USER.has_priv(l_em_user, MGMT_USER.SUPER_USER) = 1) THEN return 1; ELSE -- Case baseline BEGIN IF p_type = BASELINE_T THEN SELECT baseline_owner INTO l_owner FROM mgmt_cm_baselines WHERE baseline_guid = p_guid; ELSIF p_type = COMPARISON_T THEN SELECT comparison_owner INTO l_owner FROM mgmt_cm_comparisons WHERE comparison_guid = p_guid; ELSIF p_type = SYNCH_T THEN SELECT synch_owner INTO l_owner FROM mgmt_cm_synchronizations WHERE synch_guid = p_guid; END IF; EXCEPTION WHEN OTHERS THEN NULL; END; END IF; -- Creator can remove it. IF (l_owner is not null and l_owner = l_em_user) THEN return 1; END IF; return 0; end has_remove_priv; -- Check if current em user has the right to create -- new version for a baseline / comparison / synchronization -- Used for: edit schedule, recapture/repeat comparison/synchronize again. -- returns 0 if can not create new version -- returns 1 if can function has_create_new_version_priv(p_guid IN RAW ,p_type IN VARCHAR2) return NUMBER is l_em_user VARCHAR2(256) := MGMT_USER.get_current_em_user(); l_owner MGMT_CM_BASELINES.BASELINE_OWNER%TYPE; begin BEGIN IF p_type = BASELINE_T THEN SELECT baseline_owner INTO l_owner FROM mgmt_cm_baselines WHERE baseline_guid = p_guid; ELSIF p_type = COMPARISON_T THEN SELECT comparison_owner INTO l_owner FROM mgmt_cm_comparisons WHERE comparison_guid = p_guid; ELSIF p_type = SYNCH_T THEN SELECT synch_owner INTO l_owner FROM mgmt_cm_synchronizations WHERE synch_guid = p_guid; END IF; EXCEPTION WHEN OTHERS THEN NULL; END; -- Only Creator can create new version. IF (l_owner is not null and l_owner = l_em_user) THEN return 1; END IF; return 0; end has_create_new_version_priv; -- Check if current em user has the right to generate DDL -- for a baseline version - currently use the same model as view md. -- returns 0 if can not generate DDL -- returns 1 if can function has_generate_ddl_priv(p_guid IN RAW ,p_version IN NUMBER) return NUMBER is begin return mgmt_cm.has_view_metadata_priv(p_guid, p_version, MGMT_CM.BASELINE_T); end has_generate_ddl_priv; -- Check if current em user has the right to view METADATA -- for a baseline/ comparison version/ synchronization version/ generated DDL. -- returns 0 if can view metadata -- returns 1 if can function has_view_metadata_priv(p_guid IN RAW ,p_version IN NUMBER ,p_type IN VARCHAR2) return NUMBER is l_em_user VARCHAR2(256) := MGMT_USER.get_current_em_user(); l_owner MGMT_CM_BASELINES.BASELINE_OWNER%TYPE; l_job_id MGMT_JOB.JOB_ID%TYPE; l_admin_out VARCHAR2(32); l_priv_out VARCHAR2(32); begin -- Super user can view metadata. IF (MGMT_USER.has_priv(l_em_user, MGMT_USER.SUPER_USER) = 1) THEN return 1; ELSE BEGIN SELECT baseline_owner INTO l_owner FROM mgmt_cm_baselines WHERE baseline_guid = p_guid; EXCEPTION WHEN OTHERS THEN NULL; END; -- Owner can view IF (l_owner is not null and l_owner = l_em_user) THEN return 1; END IF; -- View job user can view metadata. -- Find out the job_id for the version. BEGIN IF p_type = BASELINE_T THEN SELECT s.job_id INTO l_job_id FROM mgmt_job_exec_summary s, mgmt_cm_baseline_versions v WHERE v.baseline_guid = p_guid AND v.baseline_version = p_version AND v.job_execution_id = s.execution_id; ELSIF p_type = COMPARISON_T THEN SELECT s.job_id INTO l_job_id FROM mgmt_job_exec_summary s, mgmt_cm_comparison_versions v WHERE v.comparison_guid = p_guid AND v.comparison_version = p_version AND v.job_execution_id = s.execution_id; ELSIF p_type = SYNCH_T THEN SELECT s.job_id INTO l_job_id FROM mgmt_job_exec_summary s, mgmt_cm_synch_versions v WHERE v.synch_guid = p_guid AND v.synch_version = p_version AND v.job_execution_id = s.execution_id; END IF; EXCEPTION WHEN OTHERS THEN NULL; END; -- Should have at least been granted view job priv. IF (l_job_id is not null) THEN begin MGMT_USER.get_user_job_priv(l_job_id, l_em_user, l_admin_out, l_priv_out); EXCEPTION WHEN OTHERS THEN NULL; end; IF (l_priv_out is not null and (l_priv_out = MGMT_USER.VIEW_JOB or l_priv_out = MGMT_USER.FULL_JOB)) THEN return 1; END IF; END IF; END IF; return 0; end has_view_metadata_priv; ------------------------------------------------ -- UI: Baselines Rollup Information -- Returns a cursor that contains baseline -- rollup information: -- name, target name, description, -- versions, latest version #, latest version date, -- baseline guid, scope spec guid, timezone_region, -- source guid -- -- The procedure allows filtering by -- target name and type, owner, name -- There may be more filters, add those -- into in list. ------------------------------------------------ procedure get_baselines_rollup( p_target_name IN VARCHAR2 DEFAULT NULL , p_target_type IN VARCHAR2 DEFAULT NULL , p_owner IN VARCHAR2 DEFAULT NULL , p_name IN VARCHAR2 DEFAULT NULL , p_exact_match IN NUMBER DEFAULT 0 , p_result_cursor OUT weak_cur_typ ) is l_em_user VARCHAR2(256) := EM_CHECK.ENQUOTE_LITERAL(MGMT_USER.get_current_em_user()); l_filter_clause VARCHAR2(2000) := ''; l_filter_value VARCHAR2(128) := NULL; l_select_stmt VARCHAR2(512) := ''; l_latest_stmt VARCHAR2(512) := ''; l_vcount_stmt VARCHAR2(256) := ''; l_where_clause VARCHAR2(128) := ''; l_orderby_clause VARCHAR2(128) := ''; l_name VARCHAR2(30) := NULL; l_target_name VARCHAR2(30) := NULL; l_owner VARCHAR2(30) := NULL; begin -- Security asserts. Unpaired quote will raise exception. IF (p_name IS NOT NULL) THEN l_name := EM_CHECK.ENQUOTE_LITERAL(p_name); END IF; IF (p_target_name IS NOT NULL) THEN l_target_name := EM_CHECK.ENQUOTE_LITERAL(p_target_name); END IF; IF (p_owner IS NOT NULL) THEN l_owner := EM_CHECK.ENQUOTE_LITERAL(p_owner); END IF; -- Filter setup IF (p_target_name IS NOT NULL) THEN IF (p_exact_match = 0) THEN l_filter_value := UPPER(p_target_name) || '%'; l_filter_clause := ' AND UPPER(b.source_name) LIKE ' || EM_CHECK.ENQUOTE_LITERAL(l_filter_value) || ' '; ELSE l_filter_clause := ' AND b.source_name LIKE ' || l_target_name || ' '; END IF; ELSIF (p_owner IS NOT NULL) THEN IF (p_exact_match = 0) THEN l_filter_value := UPPER(p_owner) || '%'; l_filter_clause := ' AND UPPER(b.baseline_owner) LIKE ' || EM_CHECK.ENQUOTE_LITERAL(l_filter_value) || ' '; ELSE l_filter_clause := ' AND b.baseline_owner LIKE ' || l_owner || ' '; END IF; ELSIF (p_name IS NOT NULL) THEN IF (p_exact_match = 0) THEN l_filter_value := UPPER(p_name) || '%'; l_filter_clause := ' AND UPPER(b.baseline_name) LIKE ' || EM_CHECK.ENQUOTE_LITERAL(l_filter_value) || ' '; ELSE l_filter_clause := ' AND b.baseline_name LIKE ' || l_name || ' '; END IF; END IF; l_select_stmt := 'select b.baseline_name as NAME, ' || 'b.source_name as DATABASE, ' || 'b.description AS DESCRIPTION, ' || 'vcount.versions as VERSIONS, ' || 'latest.latestvdate as MOST_RECENT_VERSION, ' || 'b.baseline_owner as OWNER, ' || 'b.baseline_guid as BASELINE_GUID, ' || 'b.baseline_ss as SS_GUID, ' || 'latest.latestvnum as LATESTVNUM, ' || 'latest.timezone_region as TIMEZONE_REGION, ' || 'b.source_id as SOURCE_ID '; l_latest_stmt := '(select baseline_guid, ' || --------------> the latest vnum and date for each id 'baseline_version as latestvnum, ' || 'capture_time as latestvdate, ' || 'timezone_region ' || 'from (SELECT baseline_guid, ' || --------------> versions sorted on vnum group by id 'baseline_version, ' || 'capture_time, ' || 'timezone_region, ' || 'ROW_NUMBER() OVER ' || '(PARTITION BY baseline_guid ' || 'ORDER BY baseline_version desc) as seq ' || 'FROM mgmt_cm_baseline_versions) sort_in_group ' || 'where sort_in_group.seq = 1 ' || ') latest, '; l_vcount_stmt := '(select count(baseline_version) as ' || --------------> total versions group by id 'versions, ' || 'baseline_guid ' || 'from mgmt_cm_baseline_versions v ' || 'group by baseline_guid ' || ') vcount '; l_where_clause := 'where b.temp_bl = 0 ' || 'and b.baseline_guid = latest.baseline_guid(+) ' || 'and b.baseline_guid = vcount.baseline_guid(+) '; l_orderby_clause := 'order by MOST_RECENT_VERSION desc, NAME asc, OWNER asc '; ------------------------------------------------------------------- -- Super user can see everything. -- ------------------------------------------------------------------- IF (MGMT_USER.has_priv(MGMT_USER.get_current_em_user(), MGMT_USER.SUPER_USER) = 1) THEN open p_result_cursor FOR l_select_stmt || 'from mgmt_cm_baselines b, ' || l_latest_stmt || l_vcount_stmt || l_where_clause || l_filter_clause || l_orderby_clause; ELSE ------------------------------------------------------------------- -- Non-super user can only see baselines on the targets -- -- they can see. In case of baselines on deleted targets, -- -- Owners can see them but target_view users can't, because -- -- there is no way of determining target priv for deleted -- -- targets. -- ------------------------------------------------------------------- open p_result_cursor FOR l_select_stmt || 'from mgmt_cm_baselines b, ' || l_latest_stmt || l_vcount_stmt || l_where_clause || 'and (exists ' || '(select * from mgmt_targets t ' || 'where b.source_id = t.target_guid) ' || -----> VPD 'or b.baseline_owner = ' || l_em_user || ') ' || -----> Target Deleted l_filter_clause || l_orderby_clause; END IF; end get_baselines_rollup; -------------------------------------------------- -- UI: Comparisons Rollup Information -- Returns a cursor that contains the followings: -- Comparison Name, -- Left Name (version if 'B'), Left Type, Left Id, -- Right Name (version if 'B'), Right Type, Right Id, -- The Latest Version #, -- The Latest Version Creation Date, -- Comparison owner, -- Comparison guid, -- Comparison ssguid -- Latest Version Number -- -- The procedure allows filtering by -- source name and type, owner, name -- Currently source search semantics: -- If p_source_type is 'B', -- look in left/right for baseline name matching p_source_name -- If p_source_type is 'D', -- look in left/right for database name matching p_source_name -- There may be more filters, add those -- into in list. -------------------------------------------------- procedure get_comparisons_rollup( p_source_name IN VARCHAR2 DEFAULT NULL , p_source_type IN VARCHAR2 DEFAULT NULL , p_owner IN VARCHAR2 DEFAULT NULL , p_name IN VARCHAR2 DEFAULT NULL , p_exact_match IN NUMBER DEFAULT 0 , p_result_cursor OUT weak_cur_typ ) is l_em_user VARCHAR2(256) := EM_CHECK.ENQUOTE_LITERAL(MGMT_USER.get_current_em_user()); l_filter_clause VARCHAR2(2000) := ''; l_filter_value VARCHAR2(128) := NULL; l_select_stmt VARCHAR2(512) := ''; l_latest_stmt VARCHAR2(512) := ''; l_vcount_stmt VARCHAR2(256) := ''; l_where_clause VARCHAR2(128) := ''; l_orderby_clause VARCHAR2(128) := ''; l_name VARCHAR2(30) := NULL; l_source_name VARCHAR2(30) := NULL; l_source_type VARCHAR2(3) := NULL; l_owner VARCHAR2(30) := NULL; begin -- Security asserts. Unpaired quote will raise exception. IF (p_name IS NOT NULL) THEN l_name := EM_CHECK.ENQUOTE_LITERAL(p_name); END IF; IF (p_source_name IS NOT NULL) THEN l_source_name := EM_CHECK.ENQUOTE_LITERAL(p_source_name); END IF; IF (p_source_type IS NOT NULL) THEN l_source_type := EM_CHECK.ENQUOTE_LITERAL(p_source_type); END IF; IF (p_owner IS NOT NULL) THEN l_owner := EM_CHECK.ENQUOTE_LITERAL(p_owner); END IF; -- Filter setup IF (p_source_name IS NOT NULL AND p_source_type IS NOT NULL) THEN IF (p_exact_match = 0) THEN l_filter_value := UPPER(p_source_name) || '%'; l_filter_clause := ' AND (' || '( c.left_type = ' || UPPER(l_source_type) || ' AND ' || 'UPPER(c.left_name) LIKE ' || EM_CHECK.ENQUOTE_LITERAL(l_filter_value) || ' ) ' || 'OR ' || '( c.right_type = ' || UPPER(l_source_type) || ' AND ' || 'UPPER(c.right_name) LIKE ' || EM_CHECK.ENQUOTE_LITERAL(l_filter_value) || ' ))'; ELSE l_filter_clause := ' AND (' || '( c.left_type = ' || UPPER(l_source_type) || ' AND ' || 'c.left_name LIKE ' || l_source_name || ' ) ' || 'OR ' || '( c.right_type = ' || UPPER(l_source_type) || ' AND ' || 'c.right_name LIKE ' || l_source_name || ' ))'; END IF; ELSIF (p_owner IS NOT NULL) THEN IF (p_exact_match = 0) THEN l_filter_value := UPPER(p_owner) || '%'; l_filter_clause := ' AND UPPER(c.comparison_owner) LIKE ' || EM_CHECK.ENQUOTE_LITERAL(l_filter_value) || ' '; ELSE l_filter_clause := ' AND c.comparison_owner LIKE ' || l_owner || ' '; END IF; ELSIF (p_name IS NOT NULL) THEN IF (p_exact_match = 0) THEN l_filter_value := UPPER(p_name) || '%'; l_filter_clause := ' AND UPPER(c.comparison_name) LIKE ' || EM_CHECK.ENQUOTE_LITERAL(l_filter_value) || ' '; ELSE l_filter_clause := ' AND c.comparison_name LIKE ' || l_name || ' '; END IF; END IF; l_select_stmt := 'select c.comparison_name as NAME, ' || 'c.left_name as LEFT_NAME, ' || 'c.left_type as LEFT_TYPE, ' || 'c.right_name as RIGHT_NAME, ' || 'c.right_type as RIGHT_TYPE, ' || 'vcount.versions as VERSIONS, ' || 'latest.latestvdate as MOST_RECENT_VERSION, ' || 'c.comparison_owner as OWNER, ' || 'c.comparison_guid as COMPARISON_GUID, ' || 'c.comparison_ss as SS_GUID, ' || 'latest.latestvnum as LATESTVNUM, ' || 'c.left_id as LEFT_ID, ' || 'c.left_version as LEFT_VERSION, ' || 'c.right_id as RIGHT_ID, ' || 'c.right_version as RIGHT_VERSION, ' || 'latest.timezone_region as TIMEZONE_REGION '; l_latest_stmt := '(select comparison_guid, ' || --------------> the latest vnum and date for each id 'comparison_version as latestvnum, ' || 'comparison_time as latestvdate, ' || 'timezone_region ' || 'from (SELECT comparison_guid, ' || --------------> versions sorted on vnum group by id 'comparison_version, ' || 'comparison_time, ' || 'timezone_region, ' || 'ROW_NUMBER() OVER ' || '(PARTITION BY comparison_guid ' || 'ORDER BY comparison_version desc) as seq ' || 'FROM mgmt_cm_comparison_versions) sort_in_group ' || 'where sort_in_group.seq = 1 ' || ') latest, '; l_vcount_stmt := '(select count(comparison_version) as ' || --------------> total versions group by id 'versions, ' || 'comparison_guid ' || 'from mgmt_cm_comparison_versions v ' || 'group by comparison_guid ' || ') vcount '; l_where_clause := 'where c.comparison_guid = latest.comparison_guid(+) ' || 'and c.comparison_guid = vcount.comparison_guid(+) '; l_orderby_clause := 'order by MOST_RECENT_VERSION desc, NAME asc, OWNER asc '; ------------------------------------------------------------------- -- Super user can see everything. -- ------------------------------------------------------------------- IF (MGMT_USER.has_priv(MGMT_USER.get_current_em_user(), MGMT_USER.SUPER_USER) = 1) THEN open p_result_cursor FOR l_select_stmt || 'from mgmt_cm_comparisons c, ' || l_latest_stmt || l_vcount_stmt || l_where_clause || l_filter_clause || l_orderby_clause; ELSE ------------------------------------------------------------------- -- Non-super user can only see comparison if having view access -- -- to both the sources. -- -- Source Deletion Handling: If a source db/bl had been deleted -- -- we can not make sensible determination if the priv is right -- -- so we return it if the current em user is the creator -- -- of the comparison. -- ------------------------------------------------------------------- open p_result_cursor FOR -- Case both 'D'atabase types. l_select_stmt || 'from mgmt_cm_comparisons c, ' || l_latest_stmt || l_vcount_stmt || l_where_clause || 'and c.left_type = ''D'' and c.right_type = ''D'' ' || 'and (exists ' || '(select * from mgmt_targets t1, mgmt_targets t2 '|| -----> inner join for 2 targets 'where c.left_id = t1.target_guid ' || -----> VPD1 for db1 'and c.right_id = t2.target_guid) ' || -----> VPD2 for db2 'or c.comparison_owner = ' || l_em_user || ') ' || -----> any targets deleted. l_filter_clause || 'UNION ' || -- Case both 'B'aseline types. l_select_stmt || 'from mgmt_cm_comparisons c, ' || l_latest_stmt || l_vcount_stmt || l_where_clause || 'and c.left_type = ''B'' and c.right_type = ''B'' ' || 'and (exists ' || '(select * from mgmt_cm_baselines b1, ' || 'mgmt_cm_baselines b2, ' || 'mgmt_targets t1, mgmt_targets t2 '|| -----> inner join for 2 baseline db 'where c.left_id = b1.baseline_guid ' || 'and c.right_id = b2.baseline_guid ' || 'and b1.source_id = t1.target_guid ' || -----> VPD1 for baseline db1 'and b2.source_id = t2.target_guid) ' || -----> VPD2 for baseline db2 'or c.comparison_owner = ' || l_em_user || ') ' || -----> any baseline dbs deleted. l_filter_clause || 'UNION ' || -- Case left 'B' right 'D'. l_select_stmt || 'from mgmt_cm_comparisons c, ' || l_latest_stmt || l_vcount_stmt || l_where_clause || 'and c.left_type = ''B'' and c.right_type = ''D'' ' || 'and (exists ' || '(select * from mgmt_cm_baselines b, ' || 'mgmt_targets t1, mgmt_targets t2 '|| 'where c.left_id = b.baseline_guid ' || 'and b.source_id = t1.target_guid ' || -----> VPD1 for baseline db1 'and c.right_id = t2.target_guid) ' || -----> VPD2 for db2 'or c.comparison_owner = ' || l_em_user || ') ' || -----> any source db deleted. l_filter_clause || 'UNION ' || -- Case right 'D' left 'B' l_select_stmt || 'from mgmt_cm_comparisons c, ' || l_latest_stmt || l_vcount_stmt || l_where_clause || 'and c.left_type = ''D'' and c.right_type = ''B'' ' || 'and (exists ' || '(select * from mgmt_cm_baselines b, ' || 'mgmt_targets t1, mgmt_targets t2 '|| 'where c.right_id = b.baseline_guid ' || 'and b.source_id = t1.target_guid ' || -----> VPD1 for baseline db2 'and c.left_id = t2.target_guid) ' || -----> VPD2 for db1 'or c.comparison_owner = ' || l_em_user || ') ' || -----> any source db deleted. l_filter_clause || l_orderby_clause; END IF; end get_comparisons_rollup; -------------------------------------------------- -- UI: Synchronizations Rollup Information -- Returns a cursor that contains the followings: -- Synchronization Name, -- Source Name (version if type is 'B'), -- Destination Name, -- No. of versions, -- Synchronization owner, -- The Latest Version #, -- The Latest Version Status(step name + bucket status), -- Pending Action(latest processing mode + step name + bucket status), -- Synchronization guid, -- Synchronization ssguid, -- Latest Version Number, -- Source Id, -- Source Version (for baseline source) -- Destination Id, -- The Latest Version Creation Date, -- Source Type, -- The Latest Version Step Name, -- The Latest Version Status Bucket, -- The Latest Version Status Code, -- The Latest Version Job Exec Id, -- The Latest Version State, -- The Latest Version Processing Mode, -- The Latest Version Maximum Impact Report Severity -- -- The procedure allows filtering by -- source name and type, owner, name -- Currently source search semantics: -- If p_source_type is 'B', -- look for baseline name matching p_source_name -- If p_source_type is 'D', -- look for database name matching p_source_name -- Destination is always a database name -- There may be more filters, add those -- into in list. -------------------------------------------------- procedure get_synchronizations_rollup( p_source_name IN VARCHAR2 DEFAULT NULL , p_source_type IN VARCHAR2 DEFAULT NULL , p_owner IN VARCHAR2 DEFAULT NULL , p_name IN VARCHAR2 DEFAULT NULL , p_exact_match IN NUMBER DEFAULT 0 , p_result_cursor OUT weak_cur_typ ) is l_em_user VARCHAR2(256) := EM_CHECK.ENQUOTE_LITERAL(MGMT_USER.get_current_em_user()); l_filter_clause VARCHAR2(2000) := ''; l_filter_value VARCHAR2(128) := NULL; l_select_stmt VARCHAR2(1000) := ''; l_latest_stmt VARCHAR2(1000) := ''; l_vcount_stmt VARCHAR2(256) := ''; l_where_clause VARCHAR2(128) := ''; l_orderby_clause VARCHAR2(128) := ''; l_result_stmt VARCHAR2(4500) := ''; l_name VARCHAR2(30) := NULL; l_source_name VARCHAR2(30) := NULL; l_source_type VARCHAR2(3) := NULL; l_owner VARCHAR2(30) := NULL; begin -- Security asserts. Unpaired quote will raise exception. IF (p_name IS NOT NULL) THEN l_name := EM_CHECK.ENQUOTE_LITERAL(p_name); END IF; IF (p_source_name IS NOT NULL) THEN l_source_name := EM_CHECK.ENQUOTE_LITERAL(p_source_name); END IF; IF (p_source_type IS NOT NULL) THEN l_source_type := EM_CHECK.ENQUOTE_LITERAL(p_source_type); END IF; IF (p_owner IS NOT NULL) THEN l_owner := EM_CHECK.ENQUOTE_LITERAL(p_owner); END IF; -- Filter setup IF (p_source_name IS NOT NULL AND p_source_type IS NOT NULL) THEN IF (p_exact_match = 0) THEN l_filter_value := UPPER(p_source_name) || '%'; l_filter_clause := ' AND (' || '( s.source_type = ' || UPPER(l_source_type) || ' AND ' || 'UPPER(s.source_name) LIKE ' || EM_CHECK.ENQUOTE_LITERAL(l_filter_value) || ' ) ' || 'OR ' || '( UPPER(s.target_name) LIKE ' || EM_CHECK.ENQUOTE_LITERAL(l_filter_value) || ' ))'; ELSE l_filter_clause := ' AND (' || '( s.source_type = ' || UPPER(l_source_type) || ' AND ' || 's.source_name LIKE ' || l_source_name || ' ) ' || 'OR ' || '( s.target_name LIKE ' || l_source_name || ' ))'; END IF; ELSIF (p_owner IS NOT NULL) THEN IF (p_exact_match = 0) THEN l_filter_value := UPPER(p_owner) || '%'; l_filter_clause := ' AND UPPER(s.synch_owner) LIKE ' || EM_CHECK.ENQUOTE_LITERAL(l_filter_value) || ' '; ELSE l_filter_clause := ' AND s.synch_owner LIKE ' || l_owner || ' '; END IF; ELSIF (p_name IS NOT NULL) THEN IF (p_exact_match = 0) THEN l_filter_value := UPPER(p_name) || '%'; l_filter_clause := ' AND UPPER(s.synch_name) LIKE ' || EM_CHECK.ENQUOTE_LITERAL(l_filter_value) || ' '; ELSE l_filter_clause := ' AND s.synch_name LIKE ' || l_name || ' '; END IF; END IF; l_select_stmt := 'select s.synch_name as NAME, ' || 's.source_name as SOURCE_NAME, ' || 's.target_name as DESTINATION_NAME, ' || 'vcount.versions as VERSIONS, ' || 's.synch_owner as OWNER, ' || 'l.latestvdate as MOST_RECENT_VERSION, ' || 'l.status as MOST_RECENT_STATUS, ' || 'l.pending_action as PENDING_ACTION, ' || 's.synch_guid as SYNCHRONIZATION_GUID, ' || 's.synch_ss as SS_GUID, ' || 'l.latestvnum as LATESTVNUM, ' || 's.source_id as SOURCE_ID, ' || 's.source_version as SOURCE_VERSION, ' || 's.target_id as DESTINATION_ID, ' || 'l.timezone_region as TIMEZONE_REGION, ' || 's.source_type as SOURCE_TYPE, ' || 'l.step_name as MOST_RECENT_STEP_NAME, ' || 'l.status_bucket as MOST_RECENT_STATUS_BUCKET, ' || 'l.status_code as MOST_RECENT_STATUS_CODE, ' || 'l.job_exec_id as MOST_RECENT_JOB_EXEC_ID, ' || 'l.version_state as MOST_RECENT_VERSION_STATE, ' || 'l.processing_mode as MOST_RECENT_PROCESSING_MODE, ' || 'l.max_ir_severity as MOST_RECENT_MAX_IR_SEVERITY '; l_latest_stmt := --------------> the latest vnum and date for each id '(select * ' || 'from (SELECT v.synch_guid as synch_guid, ' || --------------> versions sorted on vnum group by id 'v.synch_version as latestvnum, '|| 'v.generation_time as latestvdate, ' || 'v.timezone_region as timezone_region, ' || 's.status_bucket as status_bucket, ' || 's.status_code as status_code, ' || 'h.step_name as step_name, ' || 'h.step_name || s.status_bucket as status, ' || 'v.processing_mode || h.step_name || s.status_bucket as pending_action, ' || 'v.job_execution_id as job_exec_id, ' || 'v.version_state as version_state, ' || 'v.processing_mode as processing_mode, ' || 'v.max_ir_severity as max_ir_severity, '|| 'ROW_NUMBER() OVER ' || '(PARTITION BY v.synch_guid ' || 'ORDER BY v.synch_version desc, h.step_id desc) as seq ' || 'FROM mgmt_cm_synch_versions v, mgmt_job_history h, mgmt_job_exec_summary s ' || 'WHERE h.execution_id (+) = v.job_execution_id ' || --> outer join handles job deletion ' AND s.execution_id (+) = v.job_execution_id) sort_in_group ' || --> outer join handles job deletion 'where sort_in_group.seq = 1 ' || ') l, '; l_vcount_stmt := '(select count(synch_version) as ' || --------------> total versions group by id 'versions, ' || 'synch_guid ' || 'from mgmt_cm_synch_versions v ' || 'group by synch_guid ' || ') vcount '; l_where_clause := 'where s.synch_guid = l.synch_guid(+) ' || 'and s.synch_guid = vcount.synch_guid(+) '; l_orderby_clause := 'order by MOST_RECENT_VERSION desc, NAME asc, OWNER asc '; ------------------------------------------------------------------- -- Super user can see everything. -- ------------------------------------------------------------------- IF (MGMT_USER.has_priv(MGMT_USER.get_current_em_user(), MGMT_USER.SUPER_USER) = 1) THEN l_result_stmt := l_select_stmt || 'from mgmt_cm_synchronizations s, ' || l_latest_stmt || l_vcount_stmt || l_where_clause || l_filter_clause || l_orderby_clause; ELSE ------------------------------------------------------------------- -- Non-super user can only see synchronization if having -- -- view access to both the sources. -- -- Source Deletion Handling: If a source db/bl had been deleted -- -- we can not make sensible determination if the priv is right -- -- so we return it if the current em user is the creator -- -- of the synchronization. -- ------------------------------------------------------------------- l_result_stmt := -- Case source 'D'atabase type l_select_stmt || 'from mgmt_cm_synchronizations s, ' || l_latest_stmt || l_vcount_stmt || l_where_clause || 'and s.source_type = ''D'' ' || 'and (exists ' || '(select * from mgmt_targets t1, mgmt_targets t2 '|| -----> inner join for 2 targets 'where s.source_id = t1.target_guid ' || -----> VPD1 for db1 'and s.target_id = t2.target_guid) ' || -----> VPD2 for db2 'or s.synch_owner = ' || l_em_user || ') ' || -----> any targets deleted. l_filter_clause || 'UNION ' || -- Case source 'B'aseline type l_select_stmt || 'from mgmt_cm_synchronizations s, ' || l_latest_stmt || l_vcount_stmt || l_where_clause || 'and s.source_type = ''B'' ' || 'and (exists ' || '(select * from mgmt_cm_baselines b, ' || 'mgmt_targets t1, mgmt_targets t2 '|| 'where s.source_id = b.baseline_guid ' || 'and b.source_id = t1.target_guid ' || -----> VPD1 for baseline db1 'and s.target_id = t2.target_guid) ' || -----> VPD2 for db2 'or s.synch_owner = ' || l_em_user || ') ' || -----> any source db deleted. l_filter_clause || l_orderby_clause; END IF; open p_result_cursor FOR l_result_stmt; end get_synchronizations_rollup; ------------- -- Annotation ------------- procedure add_one_comp_obj_annotation( p_comparison_id in raw, p_object_type in number, p_object_schema in varchar2, p_object_name in varchar2, p_version_number in number, p_user_name in varchar2, p_message in varchar2, p_annotation_type in varchar2 default null) is l_timezone_region mgmt_targets.timezone_region%TYPE := 'GMT'; l_annotation_guid RAW(16) := null; BEGIN SELECT annotation_guid INTO l_annotation_guid FROM mgmt_cm_comparison_objects WHERE comparison_guid = p_comparison_id AND object_type = p_object_type AND (object_schema = p_object_schema OR object_schema IS NULL) AND object_name = p_object_name AND first_version <= p_version_number AND last_version >= p_version_number; IF (l_annotation_guid is null) THEN l_annotation_guid := sys_guid(); UPDATE mgmt_cm_comparison_objects SET annotation_guid = l_annotation_guid WHERE comparison_guid = p_comparison_id AND object_type = p_object_type AND (object_schema = p_object_schema OR object_schema IS NULL) AND object_name = p_object_name AND first_version <= p_version_number AND last_version >= p_version_number; END IF; -- insert annotation INSERT INTO MGMT_ANNOTATION (source_obj_type, source_obj_guid, timestamp, annotation_type, user_name, message) VALUES (MGMT_GLOBAL.G_ANNOTATION_SOURCE_CM, l_annotation_guid, MGMT_GLOBAL.SYSDATE_TZRGN(l_timezone_region), p_annotation_type, p_user_name, p_message ); EXCEPTION WHEN OTHERS THEN NULL; END; procedure add_bulk_comp_obj_annotation( p_cm_objs in CM_OBJ_LIST, p_user_name in varchar2, p_message in varchar2, p_annotation_type in varchar2 default null) is l_timezone_region mgmt_targets.timezone_region%TYPE := 'GMT'; l_annotation_guid RAW(16) := null; BEGIN FOR i IN 1..p_cm_objs.COUNT LOOP SELECT annotation_guid INTO l_annotation_guid FROM mgmt_cm_comparison_objects WHERE comparison_guid = p_cm_objs(i).guid AND object_type = p_cm_objs(i).object_type AND (object_schema = p_cm_objs(i).object_schema OR object_schema IS NULL) AND object_name = p_cm_objs(i).object_name AND first_version <= p_cm_objs(i).version_number AND last_version >= p_cm_objs(i).version_number; IF (l_annotation_guid is null) THEN l_annotation_guid := sys_guid(); UPDATE mgmt_cm_comparison_objects SET annotation_guid = l_annotation_guid WHERE comparison_guid = p_cm_objs(i).guid AND object_type = p_cm_objs(i).object_type AND (object_schema = p_cm_objs(i).object_schema OR object_schema IS NULL) AND object_name = p_cm_objs(i).object_name AND first_version <= p_cm_objs(i).version_number AND last_version >= p_cm_objs(i).version_number; END IF; -- insert annotation INSERT INTO MGMT_ANNOTATION (source_obj_type, source_obj_guid, timestamp, annotation_type, user_name, message) VALUES (MGMT_GLOBAL.G_ANNOTATION_SOURCE_CM, l_annotation_guid, MGMT_GLOBAL.SYSDATE_TZRGN(l_timezone_region), p_annotation_type, p_user_name, p_message ); END LOOP; EXCEPTION WHEN OTHERS THEN NULL; END; --------------- -- Ignored flag --------------- procedure set_one_comp_obj_ignored( p_comparison_id in raw, p_object_type in number, p_object_schema in varchar2, p_object_name in varchar2, p_version_number in number, p_ignored in number) is BEGIN UPDATE mgmt_cm_comparison_objects SET ignored = p_ignored WHERE comparison_guid = p_comparison_id AND object_type = p_object_type AND (object_schema = p_object_schema OR object_schema IS NULL) AND object_name = p_object_name AND first_version <= p_version_number AND last_version >= p_version_number; EXCEPTION WHEN OTHERS THEN NULL; END; procedure set_bulk_comp_obj_ignored( p_cm_objs in CM_OBJ_LIST, p_ignored in number) is BEGIN FOR i IN 1..p_cm_objs.COUNT LOOP UPDATE mgmt_cm_comparison_objects SET ignored = p_ignored WHERE comparison_guid = p_cm_objs(i).guid AND object_type = p_cm_objs(i).object_type AND (object_schema = p_cm_objs(i).object_schema OR object_schema IS NULL) AND object_name = p_cm_objs(i).object_name AND first_version <= p_cm_objs(i).version_number AND last_version >= p_cm_objs(i).version_number; END LOOP; EXCEPTION WHEN OTHERS THEN NULL; END; procedure set_one_comp_obj_ignored( p_comparison_id in raw, p_object_type in number, p_object_schema in varchar2, p_object_name in varchar2, p_version_number in number, p_ignored in number, p_user_name in varchar2, p_message in varchar2, p_annotation_type in varchar2 default null) is BEGIN set_one_comp_obj_ignored(p_comparison_id, p_object_type, p_object_schema, p_object_name, p_version_number, p_ignored); add_one_comp_obj_annotation(p_comparison_id, p_object_type, p_object_schema, p_object_name, p_version_number, p_user_name, p_message, p_annotation_type); END; procedure set_bulk_comp_obj_ignored( p_cm_objs in CM_OBJ_LIST, p_ignored in number, p_user_name in varchar2, p_message in varchar2, p_annotation_type in varchar2 default null) is BEGIN set_bulk_comp_obj_ignored(p_cm_objs, p_ignored); -- Setting annotation both ignoring and reversing. IF p_message IS NOT NULL THEN add_bulk_comp_obj_annotation(p_cm_objs, p_user_name, p_message, p_annotation_type); END IF; END; procedure set_bulk_synch_obj_excluded( p_cm_objs in CM_OBJ_LIST, p_excluded in number) is BEGIN FOR i IN 1..p_cm_objs.COUNT LOOP UPDATE mgmt_cm_synch_objects SET excluded = p_excluded WHERE synch_guid = p_cm_objs(i).guid AND object_type = p_cm_objs(i).object_type AND (object_schema = p_cm_objs(i).object_schema OR object_schema IS NULL) AND object_name = p_cm_objs(i).object_name AND first_version <= p_cm_objs(i).version_number AND last_version >= p_cm_objs(i).version_number; END LOOP; EXCEPTION WHEN OTHERS THEN NULL; END; procedure get_object_history( p_baseline_id in raw, p_obj_type in number, p_obj_schema in varchar2, p_obj_name in varchar2, p_history_cursor in out weak_cur_typ) is begin OPEN p_history_cursor FOR select * from ( select first_version, new_version, capture_time, timezone_region from mgmt_cm_baseline_objects o1, mgmt_cm_baseline_versions v where v.baseline_guid=p_baseline_id and o1.baseline_guid=v.baseline_guid and o1.object_type=p_obj_type and (o1.object_schema=p_obj_schema or o1.object_schema is null) and o1.object_name=p_obj_name and not exists ( select last_version from mgmt_cm_baseline_objects o2 where o2.baseline_guid=o1.baseline_guid and o2.object_type=o1.object_type and (o2.object_schema=o1.object_schema or o2.object_schema is null) and o2.object_name=o1.object_name and last_version in ( select max(baseline_version) from mgmt_cm_baseline_versions where baseline_guid=p_baseline_id and baseline_version < o1.first_version ) ) and o1.first_version = v.baseline_version and v.baseline_guid=p_baseline_id union all select first_version, changed_version, capture_time, timezone_region from mgmt_cm_baseline_objects o1, mgmt_cm_baseline_versions v where v.baseline_guid=p_baseline_id and o1.baseline_guid=v.baseline_guid and o1.object_type=p_obj_type and (o1.object_schema=p_obj_schema or o1.object_schema is null) and o1.object_name=p_obj_name and exists ( select last_version from mgmt_cm_baseline_objects o2 where o2.baseline_guid=o1.baseline_guid and o2.object_type=o1.object_type and (o2.object_schema=o1.object_schema or o2.object_schema is null) and o2.object_name=o1.object_name and last_version in ( select max(baseline_version) from mgmt_cm_baseline_versions where baseline_guid=p_baseline_id and baseline_version < o1.first_version ) ) and o1.first_version = v.baseline_version union all select baseline_version, deleted_version, capture_time, timezone_region from mgmt_cm_baseline_versions v, mgmt_cm_baseline_objects o1, mgmt_cm_baseline_objects o2 where v.baseline_guid=p_baseline_id and o1.baseline_guid=v.baseline_guid and o1.object_type=p_obj_type and (o1.object_schema=p_obj_schema or o1.object_schema is null) and o1.object_name=p_obj_name and o2.baseline_guid=o1.baseline_guid and o2.object_type=o1.object_type and (o2.object_schema=o1.object_schema or o2.object_schema is null) and o2.object_name=o1.object_name and o2.first_version in ( select min(first_version) from mgmt_cm_baseline_objects o3 where o3.baseline_guid=o2.baseline_guid and o3.object_type=o2.object_type and (o3.object_schema=o2.object_schema or o3.object_schema is null) and o3.object_name=o2.object_name and o3.first_version > o1.last_version ) and baseline_version in ( select min(baseline_version) from mgmt_cm_baseline_versions where baseline_guid=p_baseline_id and baseline_version > o1.last_version and baseline_version < o2.first_version ) union all select baseline_version, deleted_version, capture_time, timezone_region from mgmt_cm_baseline_versions v, mgmt_cm_baseline_objects o1 where v.baseline_guid=p_baseline_id and o1.baseline_guid=v.baseline_guid and o1.object_type=p_obj_type and (o1.object_schema=p_obj_schema or o1.object_schema is null) and o1.object_name=p_obj_name and last_version in ( select max(last_version) from mgmt_cm_baseline_objects o2 where o2.baseline_guid=o1.baseline_guid and o2.object_type=o1.object_type and (o2.object_schema=o1.object_schema or o2.object_schema is null) and o2.object_name=o1.object_name ) and baseline_version in ( select min(baseline_version) from mgmt_cm_baseline_versions where baseline_guid=p_baseline_id and baseline_version > last_version ) ) order by 1 desc; end get_object_history; --------------------- END BODY -------------------------------- end; / show errors;