Rem drv:
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/changemgr/cm_pkgdef.sql /st_emdbsa_11.2/1 2009/02/02 12:13:29 abodge Exp $
Rem
Rem cm_pkgdef.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem cm_pkgdef - 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 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/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/17/05 - grabtrans 'lhan_cm_annotate'
Rem xshen 05/16/05 - adding ignore partition consts
Rem lhan 05/05/05 - add annotation support
Rem lhan 04/28/05 - add ignore table column position flag
Rem abodge 04/21/05 - Continue pattern work
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 - Add err and msg constants
Rem abodge 03/04/05 - Add constant for version status
Rem abodge 03/04/05 - Define constant
Rem lhan 01/13/05 - synchronizing capture and compare
Rem xshen 11/15/04 - adding exact match to rollup interface
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 lhan 09/14/04 - lhan_cm_0914
Rem lhan 09/14/04 - move to cm_pkgdef.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 definition
-- Includes schema map support
CREATE OR REPLACE
PACKAGE MGMT_CM as
-- Constants for scope spec flags (bit mask values)
-- The first two are used in capture, comparison and synchronization.
include_grants CONSTANT INTEGER := 1;
include_all_schemas CONSTANT INTEGER := 2;
-- The rest are used in comparision and synchronization.
-- Default is 0 (false)
ignore_physical_atts CONSTANT INTEGER := 4;
ignore_tablespace CONSTANT INTEGER := 8;
match_constraints CONSTANT INTEGER := 16;
-- match_constraints values:
match_consts_by_def CONSTANT INTEGER := 0;
match_consts_by_name CONSTANT INTEGER := 1;
constraint_name_diffs CONSTANT INTEGER := 32;
-- constraint_name_diffs values
ignore_name_diffs CONSTANT INTEGER := 0;
report_name_diffs CONSTANT INTEGER := 1;
logical_sql_compare CONSTANT INTEGER := 64;
compare_stats CONSTANT INTEGER := 128;
-- for baseline capture before compare or synch
capture_left_first CONSTANT INTEGER := 256;
capture_source_first CONSTANT INTEGER := capture_left_first;
capture_right_first CONSTANT INTEGER := 512;
include_init_params CONSTANT INTEGER := 1024;
ignore_tblcol_position CONSTANT INTEGER := 2048;
ignore_partition CONSTANT INTEGER := 4096;
-- Capture or compare public synonyms on objects in schemas
include_public_synonyms CONSTANT INTEGER := 8192;
-- Constants for use in scopespec_names match_types col
name_pattern_match CONSTANT INTEGER := -3;
schema_match CONSTANT INTEGER := -2;
object_match_any_type CONSTANT INTEGER := -1;
-- Type-specific codes (these are also bit positions in object_types col)
table_match CONSTANT INTEGER := 0;
index_match CONSTANT INTEGER := 1;
view_match CONSTANT INTEGER := 2;
trigger_match CONSTANT INTEGER := 3;
synonym_match CONSTANT INTEGER := 4;
package_match CONSTANT INTEGER := 5;
package_body_match CONSTANT INTEGER := 6;
procedure_match CONSTANT INTEGER := 7;
function_match CONSTANT INTEGER := 8;
sequence_match CONSTANT INTEGER := 9;
dblink_match CONSTANT INTEGER := 10;
mview_match CONSTANT INTEGER := 11;
mview_log_match CONSTANT INTEGER := 12;
cluster_match CONSTANT INTEGER := 13;
tablespace_match CONSTANT INTEGER := 14;
rollback_seg_match CONSTANT INTEGER := 15;
user_match CONSTANT INTEGER := 16;
role_match CONSTANT INTEGER := 17;
profile_match CONSTANT INTEGER := 18;
udt_match CONSTANT INTEGER := 19;
-- Values for scope spec object_types bitmask - integer value
-- of the corresponding xxx_match code used as a bit position
table_int_value CONSTANT INTEGER := power(2, table_match);
index_int_value CONSTANT INTEGER := power(2, index_match);
view_int_value CONSTANT INTEGER := power(2, view_match);
trigger_int_value CONSTANT INTEGER := power(2, trigger_match);
synonym_int_value CONSTANT INTEGER := power(2, synonym_match);
package_int_value CONSTANT INTEGER := power(2, package_match);
package_body_int_value CONSTANT INTEGER := power(2, package_body_match);
procedure_int_value CONSTANT INTEGER := power(2, procedure_match);
function_int_value CONSTANT INTEGER := power(2, function_match);
sequence_int_value CONSTANT INTEGER := power(2, sequence_match);
dblink_int_value CONSTANT INTEGER := power(2, dblink_match);
mview_int_value CONSTANT INTEGER := power(2, mview_match);
mview_log_int_value CONSTANT INTEGER := power(2, mview_log_match);
cluster_int_value CONSTANT INTEGER := power(2, cluster_match);
tablespace_int_value CONSTANT INTEGER := power(2, tablespace_match);
rollback_seg_int_value CONSTANT INTEGER := power(2, rollback_seg_match);
user_int_value CONSTANT INTEGER := power(2, user_match);
role_int_value CONSTANT INTEGER := power(2, role_match);
profile_int_value CONSTANT INTEGER := power(2, profile_match);
udt_int_value CONSTANT INTEGER := power(2, udt_match);
-- Scope spec modes
schema_mode CONSTANT INTEGER := 0;
object_mode CONSTANT INTEGER := 1;
-- dependent types for_baseline
comment CONSTANT INTEGER := 1;
-- Directions for proxy user grants
proxy_user CONSTANT INTEGER := 1;
proxied_for_user CONSTANT INTEGER := 2;
-- Constants for comparison states
-- NEITHER means the object is no longer present in either
-- source in this version of the comparison
neither CONSTANT INTEGER := 0;
left_only CONSTANT INTEGER := 1;
right_only CONSTANT INTEGER := 2;
both_same CONSTANT INTEGER := 3;
both_different CONSTANT INTEGER := 4;
both_unknown CONSTANT INTEGER := 5;
-- Constants indicating which side a mapped query is for
unspecified CONSTANT INTEGER := 0;
left_side CONSTANT INTEGER := 1; -- CMProcessor.SIDE_1
right_side CONSTANT INTEGER := 2; -- CMProcessor.SIDE_2
-- Action codes returned by update_baseline_version
must_update CONSTANT INTEGER := 1;
update_maybe CONSTANT INTEGER := 2;
-- Action codes for add_obj_to_baseline
remove_prev_version CONSTANT INTEGER := 1;
no_remove_prev_version CONSTANT INTEGER := 2;
-- Constants for GET_COMPARISON_VERSION_OBJECTS
all_objects CONSTANT INTEGER := 1;
mtime_only CONSTANT INTEGER := 2;
nonschema_objects CONSTANT INTEGER := 3;
nonschema_nonmtime_objects CONSTANT INTEGER := 4;
-- Constants for including/excluding specified schemas
include_mode CONSTANT INTEGER := 1; -- Include specified schemas
exclude_mode CONSTANT INTEGER := 2; -- Exclude specified schemas
-- Status codes for comparison and baseline version status
VERSION_STATUS_OK CONSTANT VARCHAR2(2) := 'OK';
VERSION_STATUS_BAD CONSTANT VARCHAR2(3) := 'BAD';
-------------------------------------------------
-- Status Codes for Generate DDL --
-------------------------------------------------
STATUS_NEVERRUN CONSTANT NUMBER(4) := MGMT_JOBS.STATUS_BUCKET_ALL;
STATUS_RUNNING CONSTANT NUMBER(3) := MGMT_JOBS.STATUS_BUCKET_RUNNING;
STATUS_PROBLEM CONSTANT NUMBER(3) := MGMT_JOBS.STATUS_BUCKET_PROBLEM;
STATUS_OK CONSTANT NUMBER(3) := MGMT_JOBS.STATUS_BUCKET_OK;
-- Comparison Source Type
COMP_DATABASE_T CONSTANT VARCHAR2(1) := 'D';
COMP_BASELINE_T CONSTANT VARCHAR2(1) := 'B';
-- Security: Baseline or Comparison type
BASELINE_T CONSTANT VARCHAR2(1) := 'B'; -- not using comp_baseline_t
COMPARISON_T CONSTANT VARCHAR2(1) := 'C';
SYNCH_T CONSTANT VARCHAR2(1) := 'S';
-- Error codes raised by MGMT_CM functions and procedures
CANNOT_REMOVE_VERSION_ERR CONSTANT INTEGER := -20666;
CANNOT_REMOVE_VERSION_ERR_M VARCHAR2(100) := 'Cannot remove comparison or baseline version - new version being processed.';
COMP_USING_BL_VERSION_ERR CONSTANT INTEGER := -20667;
COMP_USING_BL_VERSION_ERR_M VARCHAR2(100) := 'Cannot remove baseline version - comparison currently comparing it';
-- Baseline Object History: Change Type: New, Changed, Deleted.
new_version CONSTANT INTEGER := 1;
changed_version CONSTANT INTEGER := 2;
deleted_version CONSTANT INTEGER := 3;
-- Record types and cursors
TYPE ScopeNamesRecTyp IS RECORD (
match_types number,
schema varchar2(30),
name varchar2(30));
TYPE scope_names_cur_typ IS REF CURSOR RETURN ScopeNamesRecTyp;
/*
TYPE ObjectActionRecTyp IS RECORD (
obj_type number,
schema varchar2(30),
name varchar2(30),
action number);
TYPE object_action_cur_typ IS REF CURSOR RETURN ObjectActionRecTyp;
*/
-- Weakly-typed cursor
TYPE weak_cur_typ IS REF CURSOR;
-- Record type for top-level information about a baseline
TYPE BaselineInfoRecTyp IS RECORD (
ss_id RAW(16),
source_id RAW(16));
-- Record type for top-level information about a comparison
TYPE ComparisonInfoRecTyp IS RECORD (
ss_id RAW(16),
left_type VARCHAR2(1),
left_source_id RAW(16),
left_version NUMBER,
right_type VARCHAR2(1),
right_source_id RAW(16),
right_version NUMBER);
-- Overloaded for JDBC: booleans set from JDBC are converted to bits.
-- Use setBoolean when calling this.
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;
-- Create a scope spec
-- p_typemask: Bitmask designating types selected by this scope spec
-- Sum of xxx_INT_VALUE constants
-- p_include_grants: If true, grant information is also selected
-- p_include_all_schemas: If true, objects in all schemas are selected
-- except those specifically excluded. By default, no schemas are
-- selected excpet those specifically included.
-- p_comparison_flags: Logical OR of all comparison-only flags:
-- ignore_physical_atts
-- ignore_tablespace
-- match_constraints
-- constraint_name_diffs
-- logical_sql_compare
-- compare_stats
-- p_include_init_params: If true, capture or compare database
-- init params.
-- p_include_public_syn: If true, capture or compare public
-- synonyms for objects in the schemas.
-- Returns: ID of new 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;
-- Add a schema to a scope spec. Objects in the schema are either
-- included in the scope or excluded, depending on the scope
-- spec's include_all_schemas setting.
-- p_ss_id: Scope spec ID.
-- p_name: Schema name.
procedure add_scope_schema(
p_ss_id in raw,
p_schema_name in varchar2);
-- Add a name pattern to a scope spec. For the 10gR2 release,
-- the pattern is used to select schema objects based on their
-- prefix. For example, adding a pattern "HZ_" selects schema
-- objects whose names begin with "HZ_".
-- p_ss_id: Scope spec ID.
-- p_pattern: Name pattern
procedure add_scope_name_pattern(
p_ss_id in raw,
p_pattern in varchar2);
-- Add an object name to a scope spec
-- p_ss_id: Scope spec ID.
-- p_name: Object name.
-- p_match_type: Object type.
-- By default, objects of all types having this name are matched,
-- subject to scope spec's type bitmask. (OBJECT_MATCH_ANY_TYPE)
-- xxx_MATCH: Match the specific type. This overrides the type bitmask.
-- p_match_schema: Name of schema containing specific schema object.
-- Example: To select table SCOTT.EMP:
-- p_name = 'EMP'
-- p_match_type = TABLE_MATCH
-- p_obj_schema = 'SCOTT'
-- To select user SCOTT:
-- p_name = 'SCOTT'
-- p_match_type = USER_MATCH
-- p_obj_schema = null
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);
-- Get scope spec typemask
-- p_ss_id: ID of scope spec
function get_scope_spec_typemask(
p_ss_id in raw)
return number;
-- Get scope spec flags
-- p_ss_id: ID of scope spec
function get_scope_spec_flags(
p_ss_id in raw)
return number;
-- Get scope spec names
-- p_ss_id: ID of scope spec
-- p_ss_names: Cursor to be initialized with scope name data
procedure get_scope_spec_names(
p_ss_id in raw,
p_ss_names in out scope_names_cur_typ);
-- Indicate whether an "object-mode" scope spec has schema types.
-- p_ss_id: ID of scope spec
-- Returns: 0 if scope spec contains no schema types
-- >0 otherwise
function scope_spec_has_schema_types(
p_ss_id in raw)
return number;
-- Indicate whether an "object-mode" scope spec has nonschema types.
-- p_ss_id: ID of scope spec
-- Returns: 0 if scope spec contains no nonschema types
-- >0 otherwise
function scope_spec_has_nonschema_types(
p_ss_id in raw)
return number;
-- Get comma-separated list of schemas included (or excluded)
-- in a scope spec.
-- p_ss_id: Scope spec ID.
function get_schema_list (p_ss_id in raw) return varchar2;
-- Get comma-separated list of object name patterns
-- in a scope spec.
-- p_ss_id: Scope spec ID.
function get_name_pattern_list (p_ss_id in raw) return varchar2;
-- 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);
-- Get comma-separated list of schemas included (or excluded)
-- in one side of a schema map
-- p_owner_id: ID of schema map's owner (e.g., comparison).
-- p_side: 'L' or 'R', indicating which side to get the list from
function get_schema_list_from_map (
p_owner_id in raw,
p_side in varchar2) return varchar2;
-- Get comma-separated list of types included in a scope spec typemask.
function get_types_list (p_typemask in number) return varchar2;
function get_nonschema_types_list (p_typemask in number) return varchar2;
-- Get name of type represented by numeric type code
function get_type_name (p_type_code in number) return varchar2;
function get_nonschema_type_name (p_type_code in number) return varchar2;
-- Create a baseline
-- p_name: Baseline name
-- p_owner: Baseline owner (EM admin)
-- p_scope_spec: ID of the baseline's scope specification
-- p_source_id: ID of baseline's source database
-- Must be a valid target_guid from mgmt_targets
-- p_description (optional): Description of the baseline
-- p_temporary (optional): 0 if permanent (user-defined) baseline
-- 1 if temporary baseline (used by Compare)
-- Returns: ID of created 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;
-- Get a baseline's ID from name and owner
function get_baseline_id(
p_name in varchar2,
p_owner in varchar2)
return raw;
-- Get information about a baseline.
procedure get_baseline_info (p_baseline_id in raw,
p_baseline_info in out BaselineInfoRecTyp);
-- Get a baseline's scope spec
-- p_baseline_id: Baseline ID
-- Return: ID of baseline's scope spec.
function get_baseline_scope_spec(
p_baseline_id in raw)
return raw;
-- Get a baseline's source database ID
-- p_baseline_id: Baseline ID
-- Return: ID of baseline's source database.
function get_baseline_source_id(
p_baseline_id in raw)
return raw;
-- Add a new baseline version
-- p_baseline_id: Baseline ID
-- Return: Version number of the new version
-- 1 for initial version
-- curr_version+1 for subsequent versions
function add_baseline_version(
p_baseline_id in raw)
return integer;
-- Get current baseline version
-- p_baseline_id: Baseline ID
-- Return: Version number of the current baseline version
-- NOTE: Returns 0 if no baseline version has been created.
function get_current_baseline_version(
p_baseline_id in raw)
return integer;
-- Get previous baseline version (before the current version)
-- p_baseline_id: Baseline ID
-- Return: Version number of the previous baseline version
-- NOTE: Returns 0 if no previous baseline version exists.
function get_previous_baseline_version(
p_baseline_id in raw)
return integer;
-- Add an object definition to a baseline version
-- TO DO: Force version_number to current version, do not allow user to
-- specify version? However, reduces efficiency of this call.)
-- TO DO: A version of this that accepts an array of objects.
-- p_baseline_id: Baseline ID
-- p_object_type: Type of object being added,
-- one of the xxx_MATCH values
-- p_object_schema: Schema containing object being added
-- null for nonschema objects
-- p_object_name: Name of object being added
-- p_version_number: Version to which object is added
-- THIS MUST BE THE CURRENT VERSION
-- p_object_definition: BLOB containing object's XML definition
-- p_prev_version_action:
-- REMOVE_PREV_VERSION (default): Remove the previous version
-- from this baseline version
-- NO_REMOVE_PREVIOUS_VERSION: Do not remove the previous
-- version (user is responsible for determining that there is no
-- previous 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);
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);
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);
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);
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);
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);
procedure add_proxygrant_to_baseline(
p_baseline_id in raw,
p_grantee in varchar2,
p_proxy_user in varchar2,
p_direction in number, -- MGMT_CM.PROXY_USER or PROXIED_FOR_USER
p_version_number in number);
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);
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;
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;
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);
-- 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);
-- Remove an object from a baseline version
-- Call this to indicate that the object has been dropped since
-- the last version of this baseline.
-- TO DO: Force to current version, as above.
-- p_baseline_id: Baseline ID
-- p_object_type: Type of object being removed,
-- one of the xxx_MATCH values
-- p_object_schema: Schema containing object being removed;
-- null for nonschema objects
-- p_object_name: Name of object being removed
-- p_version_number: Version from which object is removed
-- THIS MUST BE THE CURRENT VERSION
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);
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);
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);
-- Remove a dependent identified by dependent type and base object
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);
-- Remove a depenent identified by dependent type and hash value
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);
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);
procedure remove_sysgrant_from_baseline(
p_baseline_id in raw,
p_grantee in varchar2,
p_privilege in varchar2,
p_version_number in number);
procedure remove_rolegrant_from_baseline(
p_baseline_id in raw,
p_grantee in varchar2,
p_role in varchar2,
p_version_number in number);
procedure remove_qtagrnt_from_baseline(
p_baseline_id in raw,
p_grantee in varchar2,
p_tablespace in varchar2,
p_version_number in number);
procedure remove_prxygrnt_from_baseline(
p_baseline_id in raw,
p_grantee in varchar2,
p_proxy_user in varchar2,
p_version_number in number);
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);
procedure remove_init_prm_from_baseline(
p_baseline_id in raw,
p_param_name in varchar2,
p_version_number in number);
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);
-- Set a baseline version's capture date
-- p_baseline_id: Baseline ID
-- p_version_number: Version for which to set date
-- p_start_date: Job execution id.
procedure set_capture_date(
p_baseline_id in raw,
p_version_number in number,
p_execution_id in raw);
-- Get objects in a baseline version
-- p_baseline_id: Baseline ID
-- p_version_number: Version for which to return objects
-- p_objects_cursor: Cursor initialized with the objects in
-- this baseline version. Columns are:
-- obj_type NUMBER
-- obj_schema VARCHAR2
-- obj_name VARCHAR2
-- last_ddl DATE
-- NOTE: This is not a strongly-typed cursor because the procedure will
-- be called from Java code.
-- NOTE: THIS MAY BE REMOVED in favor of the schema and non-schema
-- versions below.
procedure get_baseline_version_objects(
p_baseline_id in raw,
p_version_number in number,
p_objects_cursor in out weak_cur_typ);
-- Get nonschema objects in a baseline version
-- p_baseline_id: Baseline ID
-- p_version_number: Version for which to return schema objects
-- p_objects_cursor: Cursor initialized with the objects in
-- this baseline version. Columns are:
-- obj_type NUMBER
-- obj_schema VARCHAR2
-- obj_name VARCHAR2
-- last_ddl DATE
-- NOTE: This is not a strongly-typed cursor because the procedure will
-- be called from Java code.
-- p_scopespec_id (optional): the ID of a scope spec used to restrict
-- the objects returned in the query.
-- p_mode (optional): SCHEMA_MODE or OBJECT_MODE. Only used in scoped queries.
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);
-- Get the definition of a single object in a baseline version
-- p_baseline_id: Baseline ID
-- p_version_number: Version from which to return the object definition
-- p_obj_type: Type of the object
-- p_obj_schema: Schema containing the object (null if non-schema)
-- p_obj_name: Object name
-- Returns: BLOB, object's XML definition
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;
procedure get_baseline_version_objgrant(
p_baseline_id in raw,
p_version_number in number,
p_objects_cursor in out weak_cur_typ);
procedure get_baseline_version_sysgrant(
p_baseline_id in raw,
p_version_number in number,
p_objects_cursor in out weak_cur_typ);
procedure get_baseline_version_rolegrant(
p_baseline_id in raw,
p_version_number in number,
p_objects_cursor in out weak_cur_typ);
procedure get_baseline_version_qtagrnt(
p_baseline_id in raw,
p_version_number in number,
p_objects_cursor in out weak_cur_typ);
procedure get_baseline_version_prxygrnt(
p_baseline_id in raw,
p_version_number in number,
p_objects_cursor in out weak_cur_typ);
procedure get_baseline_version_cons_grps(
p_baseline_id in raw,
p_version_number in number,
p_objects_cursor in out weak_cur_typ);
procedure get_baseline_version_init_prms(
p_baseline_id in raw,
p_version_number in number,
p_objects_cursor in out weak_cur_typ);
-- Remove a baseline version
-- You cannot remove the latest version (why not?) or
-- the initial version (TO DO: Error checking)
-- p_baseline_id: ID of the baseline
-- p_version_number: Version number of the baseline to remove
procedure remove_baseline_version(
p_baseline_id in raw,
p_version_number in number);
-- Remove an entire baseline
-- p_baseline_id: ID of the baseline to remove
procedure remove_baseline(
p_baseline_id in raw);
-- Create a comparison
-- p_name: Comparison name
-- p_owner: Comparison owner (EM admin)
-- p_scope_spec: ID of the comparison's scope specification
-- p_left_source_type:
-- 'B' for Baseline or
-- 'D' for Database
-- p_left_source_id: ID of source
-- For Baseline: Must be a valid baseline_guid
-- For Database: Must be a valid target_guid from mgmt_targets
-- p_left_source_version: Baseline version number
-- (ignored for database targets)
-- p_right_source_type, p_right_source_id, p_right_source_version:
-- Same as for p_left parameters
-- p_dependent_type: the description for comparison, optional
-- Returns: ID of created 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;
-- Get a comparison's ID from name and owner
function get_comparison_id(
p_name in varchar2,
p_owner in varchar2)
return raw;
-- Get a comparison's scope spec
function get_comparison_scope_spec(
p_comparison_id in raw)
return raw;
procedure get_comparison_info (p_comparison_id in raw,
p_comparison_info in out ComparisonInfoRecTyp);
function get_comparison_owner (p_comparison_id in raw)
return varchar2;
-- 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);
-- Add a new comparison version
-- p_comparison_id: Comparison ID
-- Return: Version number of the new version
-- 1 for initial version
-- curr_version+1 for subsequent versions
function add_comparison_version(
p_comparison_id in raw)
return integer;
-- Get current comparison version
-- p_comparison_id: Comparison ID
-- Return: Version number of the current comparison version
-- NOTE: Returns null if no comparison version has been created.
function get_current_comparison_version(
p_comparison_id in raw)
return integer;
/*
-- Get the set of objects in a new comparison version that are present
-- in both sources and therefore need to be compared.
-- This assumes that the MGMT_CM_TEMP table has been populated with
-- information about the objects present in the left and right
-- sources.
-- p_comparison_id: Comparison ID
-- p_objs_for_compare: Cursor, will be initialized with objects present
-- in both left and right sources.
procedure get_comp_objects_for_compare(
p_comparison_id in raw,
p_objs_for_compare in out object_action_cur_typ);
*/
-- Set a baseline version's compare date
-- p_comparison_id: Baseline ID
-- p_version_number: Version for which to set date
-- p_execution_id: Job execution id.
procedure set_compare_date(
p_comparison_id in raw,
p_version_number in number,
p_execution_id in raw);
-- Add an object record to a comparison version
-- TO DO: Force version_number to current version, do not allow user to
-- specify version? (However, reduces efficiency of this call.)
-- TO DO: A version of this that accepts an array of objects?
-- p_comparison_id: Comparison ID
-- p_object_type: Type of object being added,
-- one of the xxx_MATCH values
-- p_object_schema: Schema containing object being added
-- null for nonschema objects
-- p_object_name: Name of object being added
-- p_obj_state:
-- LEFT_ONLY: Object is only in left source
-- RIGHT_ONLY: Object is only in right source
-- BOTH_SAME: Object is in both sources and is identical
-- BOTH_DIFFERENT: Object is in both sources and is different
-- p_version_number: Version to which object is added
-- THIS MUST BE THE CURRENT VERSION
-- p_object_diffs: BLOB containing object differences
-- Only for state = BOTH_DIFFERENT
-- p_prev_version_action:
-- REMOVE_PREV_VERSION (default): Remove the previous version
-- from this comparison version
-- NO_REMOVE_PREVIOUS_VERSION: Do not remove the previous
-- version (user is responsible for determining that there is no
-- previous version).
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);
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;
-- Remove an object from a comparison version
-- (i.e., both objects no longer present in the database)
-- Call this to indicate that the object has been dropped in both sources
-- the last version of this comparison.
-- TO DO: Force to current version, as above.
-- p_comparison_id: Comparison ID
-- p_object_type: Type of object being removed,
-- one of the xxx_MATCH values
-- p_object_schema: Schema containing object being removed
-- null for nonschema objects
-- p_object_name: Name of object being removed
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);
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);
procedure remove_init_prm_from_comp(
p_comparison_id in raw,
p_param_name in varchar2,
p_version_number in number);
-- Get objects in a comparison version
-- p_comparison_id: Comparison ID
-- p_version_number: Version for which to return objects
-- p_objects_cursor: Cursor initialized with the objects in
-- this comparison version. Columns are:
-- obj_type NUMBER
-- obj_schema VARCHAR2
-- obj_name VARCHAR2
-- obj_state NUMBER
-- last_left_ddl DATE
-- last_right_ddl DATE
-- left_hash_value VARCHAR2
-- right_hash_value VARCHAR2
-- NOTE: This is not a strongly-typed cursor because the procedure will
-- be called from Java code.
-- p_mode:
-- ALL_OBJECTS: All objects in the baseline
-- MTIME_ONLY: Only objects with a valid last-DDL time
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);
-- Get initialization parameters in a comparison version
-- p_comparison_id: Comparison ID
-- p_version_number: Version for which to return objects
-- p_params_cursor: Cursor initialized with the paramaters in
-- this comparison version. Columns are:
-- param_name
-- state
-- left_param_value
-- right_param_value
procedure get_comp_version_init_params(
p_comparison_id in raw,
p_version_number in number,
p_params_cursor in out weak_cur_typ);
-- Get the differences for a single object in a comparison version
-- p_comparison_id: Comparison ID
-- p_version_number: Version from which to return the object differences
-- p_obj_type: Type of the object
-- p_obj_schema: Schema containing the object (null if non-schema)
-- p_obj_name: Object name
-- Returns: BLOB, object's XML differences (null if no differences)
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;
-- Remove a comparison version
-- You cannot remove the latest version (why not?) or
-- the initial version (TO DO: Error checking)
-- p_comparison_id: ID of the comparison
-- p_version_number: Version number of the comparison to remove
procedure remove_comparison_version(
p_comparison_id in raw,
p_version_number in number);
-- Remove an entire comparison
-- p_comparison_id: ID of the comparison to remove
procedure remove_comparison(
p_comparison_id in raw);
-- Set a baseline version's job execution id
-- p_baseline_id: Baseline ID
-- p_version_number: Version for which to set date
-- p_exec_id: Job execution ID for this baseline version
procedure set_execution_id(
p_baseline_id in raw,
p_version_number in number,
p_execution_id in raw);
-- Set a baseline version's job execution id
-- p_baseline_id: Baseline ID
-- p_version_number: Version for which to set date
-- p_exec_id: Job execution ID for this baseline version
procedure set_baseline_db_version(
p_baseline_id in raw,
p_version_number in number,
p_db_version in varchar2);
-- Set a baseline's job id
-- p_baseline_id: Baseline ID
-- p_job_id: Job job ID for this baseline
procedure set_baseline_job_id(
p_baseline_id in raw,
p_job_id in raw);
-- Set a comparison version's job execution id
-- p_comparison_id: comparison ID
-- p_version_number: Version for which to set date
-- p_exec_id: Job execution ID for this comparison version
procedure set_comparison_execution_id(
p_comparison_id in raw,
p_version_number in number,
p_execution_id in raw);
-- Set a comparison's job id
-- p_comparison_id: Comparison ID
-- p_job_id: Job job ID for this baseline
procedure set_comparison_job_id(
p_comparison_id in raw,
p_job_id in raw);
-------------------------------------------------
-- 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;
-- Get a synchronization's ID from name and owner
function get_synchronization_id(
p_name in varchar2,
p_owner in varchar2)
return raw;
-- Get a synchronization's scope spec
function get_synchronization_scope_spec(
p_synch_id in raw)
return raw;
function add_synchronization_version(
p_synch_id in raw,
p_processing_mode in number)
return integer;
-- Remove an entire synchronization
-- p_synch_id: ID of the synchronization to remove
procedure remove_synchronization(
p_synch_id in raw);
-- Remove a synchronization version
-- p_synch_id: ID of the synchronization
-- p_version_number: Version number of the synchronization to remove
procedure remove_synchronization_version(
p_synch_id in raw,
p_version_number in number);
-- 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);
-- 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);
-- Set a synchronization version's synch script
function set_synch_script(
p_synch_id in raw,
p_version_number in number)
return blob;
-- 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;
-- Add an impact report entry
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);
-- 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;
-- Remove an object from a synch version
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);
-- Set a synchronization's job id
-- p_synch_id: Synch ID
-- p_job_id: job ID for this synch
procedure set_synch_job_id(
p_synch_id in raw,
p_job_id in raw);
-------------------------------------------------
-- Generate DDL For Baseline Version --
-------------------------------------------------
-- Drop DDL
-- Side effect
procedure drop_baseline_version_ddl
(p_baseline_guid IN RAW
,p_baseline_version IN NUMBER
);
-- Prepare a lob for ddl generation
function get_ddl_for_generate
(p_baseline_guid IN RAW
,p_baseline_version IN NUMBER
)
return blob;
-- Start Generate DDL
-- Side effect: Turn ddl_status code to 'Running'
procedure start_generate_ddl
(p_baseline_guid IN RAW
,p_baseline_version IN NUMBER
);
-- End Generate DDL
-- Side effect: Save DDL and Turn ddl_status code to 'Ok'
procedure end_generate_ddl
(p_baseline_guid IN RAW
,p_baseline_version IN NUMBER
);
-- Fail Generate DDL
-- Side effect: Turn ddl_status code to 'Problem'
procedure fail_generate_ddl
(p_baseline_guid IN RAW
,p_baseline_version IN NUMBER
);
-- Check if current em user has the right to delete
-- an entire baseline or comparison/ version / drop ddl.
-- 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;
-- Check if current em user has the right to create
-- new version for a baseline / comparison.
-- Used for: edit schedule, recapture/repeat comparison.
-- 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;
-- Check if current em user has the right to generate DDL
-- for a baseline version.
-- 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;
-- Check if current em user has the right to view METADATA
-- for a baseline / comparison 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;
------------------------------------------------
-- UI: Baselines Rollup Information --
------------------------------------------------
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
);
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
);
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
);
-------------
-- 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);
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);
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);
procedure set_bulk_comp_obj_ignored(
p_cm_objs in CM_OBJ_LIST,
p_ignored in number);
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);
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);
procedure set_bulk_synch_obj_excluded(
p_cm_objs in CM_OBJ_LIST,
p_excluded in number);
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);
------------- END SPEC ----------------------------------
end;
/
show errors;