Rem drv: <create type="pkgbodys" pos="credentials/credentials_pkgbody.sql+"/>
Rem
Rem $Header: properties_pkgbody.sql 18-sep-2007.12:33:22 rsamaved Exp $
Rem
Rem properties_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      properties_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rsamaved    09/17/07 - handle rac unencrypted password problem
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    jsadras     05/19/05 - property change callback 
Rem    jsadras     04/21/05 - Bug:4300503, Deadlock Bug 
Rem    asawant     02/09/05 - Add rowset handler. 
Rem    rpinnama    01/05/05 - Fix DISTINCT clause 
Rem    asawant     11/10/04 - Add propagate() 
Rem    asawant     09/29/04 - Removing savepoint. 
Rem    asawant     09/21/04 - Fixing extra rollback. 
Rem    asawant     08/06/04 - asawant_udtp_063004
Rem    asawant     06/05/04 - Created
Rem

CREATE OR REPLACE PACKAGE BODY mgmt_properties AS

G_MODULE_NAME VARCHAR2(20) := 'MGMT_PROPERTIES' ;
--------------------------------------------------------------------------------
--                      PACKAGE PRIVATE ROUTINES                              --
--------------------------------------------------------------------------------

-- Procedure log_error()
--
--------------
-- Notes:
--------------
-- Handles logging for package
--
PROCEDURE log_error(error_code IN NUMBER, errmsg IN VARCHAR2)
IS
BEGIN
  MGMT_LOG.LOG_ERROR(PROPERTIES_MODULE, 
                     error_code,
                     SUBSTR(errmsg, 1, MAX_ERR_MSG_SZ));
END log_error;
--
-- Private procedure to lock target type registration
--
-- Lock the input target types, If none is given, it locks the whole table
--
PROCEDURE lock_target_types(p_target_types IN SMP_EMD_STRING_ARRAY)
IS
l_target_type_list mgmt_medium_string_array := mgmt_medium_string_array() ;
l_lock_handle mgmt_lock_util.lock_handle_type ;
BEGIN
  IF p_target_types IS NOT NULL AND p_target_types.COUNT > 0
  THEN
    FOR rec  in ( SELECT column_value target_type
                   FROM TABLE(CAST(p_target_types AS SMP_EMD_STRING_ARRAY))
                   ORDER BY 1)
    LOOP
      EM_TARGET.get_type_registration_lock(rec.target_type) ; 
    END LOOP ;
  ELSE
    LOCK TABLE mgmt_target_type_versions IN SHARE ROW EXCLUSIVE MODE;
  END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL ;
END lock_target_types ;
--------------------------------------------------------------------------------
--                       PACKAGE PUBLIC ROUTINES                              --
--------------------------------------------------------------------------------

-- Procedure add_target_property()
--
--------------
-- Notes:
--------------
--   Do not call this procedure directly, instead call the procedures:
--     add_udp() if you are an EM integrator or end user
--     add_sdp() if you are an EM developer and the property WILL SHIP with EM.
--   Existing property definitions ARE ALWAYS OVERWRITTEN (except when a user
-- property attempts to overwrite a system property)!
--   It is the responsability of the caller to commit or rollback (including
-- when a deadlock is detected).
--   This procedure acquires a restrictive lock on mgmt_target_type_versions
-- and should be used cautiously.
-- PS: The only reason this routine is left as public, is due to the testing
-- code which needs to call this routing directly to test unlikely scenarios...
--------------
-- Parameters:
--------------
--   p_property_name: The name of the property to be added
--   p_property_type: The type of the property to be added
--   p_property_display_name: The display name of the property.
--   p_property_display_nlsid: The NLSid of the property (NULL if none)
--   p_credential_flag: Flags whether the property is a credential or not
--   p_required_flag : Flags whether the property is mandatory or not. The
--   agent will only respect this flag if the property is defined in the
--   target types metadata (in other words, user defined target properties
--   will not be ensured at the agent).
--   p_default_value: Property default value
--   p_computed_flag: Flags whether a property is computed by the system (OMS, 
--   agent, etc.), or set by the user.
--   p_read_only_flag: Flags whether a property may be modified by the user or 
--   not. Note: Although computed properties are also non-modifiable by the user
--   they are NOT explicilty flagged as read-only.
--   p_hidden_flag: Flags whether a property is displayed through the UI or not.
--   p_system_flag: Flags whether the property is part of EM (SDP) or added by
--   the user (UDP).
--   p_target_type_list: The list of target types the property applies to, this
--   can be an empty list or NULL if it applies to all target types.
---------------------
-- Exceptions thrown:
---------------------
--   0. E_UNAUTHORIZED: The user invoking the operation does not have the
--   appropriate privileges.
--   1. E_INVALID_PARAM: One or more parameters have invalid values.
--   2. E_INVALID_TYPE: One or more target types passed in are not defined
--   in the repository.
--   3. E_SYS_PROP_EXISTS: A system target property with this name already 
--   exists and cannot be overwritten. Only thrown if an attempt is made to
--   add a udp to all target types when a sdp with the same name exists on all
--   types already.
-----------------------------------------------------------------------------
PROCEDURE add_target_property(
  p_property_name          IN MGMT_TARGET_PROP_DEFS.property_name%TYPE,
  p_property_type          IN MGMT_TARGET_PROP_DEFS.property_type%TYPE,
  p_system_flag            IN MGMT_TARGET_PROP_DEFS.system_flag%TYPE,
  p_credential_flag        IN MGMT_TARGET_PROP_DEFS.credential_flag%TYPE,
  p_required_flag          IN MGMT_TARGET_PROP_DEFS.required_flag%TYPE,
  p_computed_flag          IN MGMT_TARGET_PROP_DEFS.computed_flag%TYPE,
  p_read_only_flag         IN MGMT_TARGET_PROP_DEFS.read_only_flag%TYPE,
  p_hidden_flag            IN MGMT_TARGET_PROP_DEFS.hidden_flag%TYPE,
  p_default_value          IN MGMT_TARGET_PROP_DEFS.default_value%TYPE,
  p_property_display_name  IN MGMT_TARGET_PROP_DEFS.property_display_name%TYPE,
  p_property_display_nlsid IN MGMT_TARGET_PROP_DEFS.property_display_nlsid%TYPE,
  p_target_type_list       IN SMP_EMD_STRING_ARRAY)
IS
  l_target_types  t_cursor_type;
  l_target_type   mgmt_target_type_versions.target_type%TYPE;
  l_type_meta_ver mgmt_target_type_versions.type_meta_ver%TYPE;
  l_system_flag   mgmt_target_prop_defs.system_flag%TYPE;
  l_type_count    NUMBER(10);
BEGIN
  -- Ensure the user has the appropriate privilege
  IF (mgmt_user.has_priv(mgmt_user.get_current_em_user(), 
                         mgmt_user.SUPER_USER) != 1) THEN
    RAISE E_UNAUTHORIZED;
  END IF;

  -- Ensure parameters are valid
  IF ((p_property_name IS NULL) OR (p_property_type IS NULL) OR
     (p_system_flag IS NULL) OR (p_system_flag > 1) OR (p_system_flag < 0) OR
     (p_credential_flag IS NULL) OR (p_credential_flag < 0) OR
     (p_credential_flag > 1) OR (p_required_flag IS NULL) OR
     (p_required_flag < 0) OR (p_required_flag > 1) OR 
     (p_credential_flag IS NULL) OR (p_credential_flag < 0) OR 
     (p_credential_flag > 1) OR (p_computed_flag IS NULL) OR 
     (p_computed_flag > 1) OR (p_computed_flag < 0) OR
     (p_read_only_flag IS NULL) OR (p_read_only_flag > 1) OR 
     (p_read_only_flag < 0) OR (p_hidden_flag IS NULL) OR
     (p_hidden_flag > 1) OR (p_hidden_flag < 0)) THEN
     RAISE E_INVALID_PARAMS;
  END IF;

  -- disallowing new target type versions to be added.
  -- This lock serializes all add/remove property operations as well as 
  -- property propagation.
  lock_target_types(p_target_type_list) ;

  -- Is this property for a set of target types or for all target types?
  IF ((p_target_type_list IS NULL) OR (p_target_type_list.COUNT = 0)) THEN
    -- property for all target types
    BEGIN
      -- make sure the prop def propagates to any new target added to the system
      INSERT INTO mgmt_all_target_props(property_name,
                                        property_type,
                                        property_display_name,
                                        property_display_nlsid,
                                        required_flag,
                                        credential_flag,
                                        default_value,
                                        computed_flag,
                                        read_only_flag,
                                        hidden_flag,
                                        system_flag)
             VALUES(p_property_name,
                    p_property_type,
                    p_property_display_name,
                    p_property_display_nlsid,
                    p_required_flag,
                    p_credential_flag,
                    p_default_value,
                    p_computed_flag,
                    p_read_only_flag,
                    p_hidden_flag,
                    p_system_flag);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        l_system_flag := 0;

        -- Overwrite unless existing prop is an SDP and new one is an UDP
        IF (p_system_flag = 0) THEN
          -- fetch the current properties system_flag
          SELECT system_flag INTO l_system_flag
            FROM mgmt_all_target_props
           WHERE property_name = p_property_name
             AND property_type = p_property_type
             FOR UPDATE;
        END IF;

        IF ((p_system_flag = 1) OR (l_system_flag = 0)) THEN
          UPDATE mgmt_all_target_props
             SET property_display_name = p_property_display_name,
                 property_display_nlsid = p_property_display_nlsid,
                 required_flag = p_required_flag,
                 credential_flag = p_credential_flag,
                 default_value = p_default_value,
                 computed_flag = p_computed_flag,
                 read_only_flag = p_read_only_flag,
                 hidden_flag = p_hidden_flag,
                 system_flag = p_system_flag
           WHERE property_name = p_property_name
             AND property_type = p_property_type;
        ELSE
          RAISE E_SYS_PROP_EXISTS;
        END IF;
    END;

    -- get the list of target type versions that might need propagation
    OPEN l_target_types FOR
      SELECT DISTINCT target_type, type_meta_ver
        FROM mgmt_target_type_versions;

  ELSE
    -- property for subset of target types

    -- ensure that all target types passed are valid types
    SELECT count(*) INTO l_type_count
      FROM (
             SELECT l.column_value, t.target_type AS target_type
               FROM mgmt_target_types t RIGHT OUTER JOIN 
                    TABLE(CAST(p_target_type_list AS SMP_EMD_STRING_ARRAY)) l
                 ON l.column_value = t.target_type
           )
     WHERE target_type IS NULL;

    IF (l_type_count != 0) THEN
      RAISE E_INVALID_TYPE;
    END IF;

    -- get all target type versions 
    OPEN l_target_types FOR
      SELECT DISTINCT s.target_type AS target_type,
             s.type_meta_ver AS type_meta_ver
        FROM TABLE(CAST(p_target_type_list AS SMP_EMD_STRING_ARRAY)) l,
             mgmt_target_type_versions s
        WHERE l.column_value = s.target_type;
  END IF;
  
  -- Propagate to all specified target type versions
  LOOP
    FETCH l_target_types INTO l_target_type, l_type_meta_ver;
    EXIT WHEN l_target_types%NOTFOUND;

    BEGIN
      INSERT INTO mgmt_target_prop_defs(target_type,
                                        type_meta_ver,
                                        property_name,
                                        property_type,
                                        property_display_name,
                                        property_display_nlsid,
                                        required_flag,
                                        credential_flag,
                                        default_value,
                                        computed_flag,
                                        read_only_flag,
                                        hidden_flag,
                                        system_flag,
                                        all_versions)
               VALUES(l_target_type,
                      l_type_meta_ver,
                      p_property_name,
                      p_property_type,
                      p_property_display_name,
                      p_property_display_nlsid,
                      p_required_flag,
                      p_credential_flag,
                      p_default_value,
                      p_computed_flag,
                      p_read_only_flag,
                      p_hidden_flag,
                      p_system_flag,
                      1);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        -- See that we only select props with all_versions = 1, this avoids
        -- contention with the loader. Only one row will be returned (if any).
        FOR prop_def IN
        (
          SELECT system_flag
            FROM mgmt_target_prop_defs
           WHERE target_type = l_target_type
             AND type_meta_ver = l_type_meta_ver
             AND property_name = p_property_name
             AND property_type = p_property_type
             AND all_versions = 1
             AND ((p_system_flag = 1) OR (system_flag = 0))
             FOR UPDATE
        )
        LOOP -- This loop will only have one iteration!
          -- Update
          UPDATE mgmt_target_prop_defs
             SET property_display_name = p_property_display_name,
                 property_display_nlsid = p_property_display_nlsid,
                 required_flag = p_required_flag,
                 credential_flag = p_credential_flag,
                 default_value = p_default_value,
                 computed_flag = p_computed_flag,
                 read_only_flag = p_read_only_flag,
                 hidden_flag = p_hidden_flag,
                 system_flag = p_system_flag
           WHERE property_name = p_property_name
             AND property_type = p_property_type
             AND target_type = l_target_type
             AND type_meta_ver = l_type_meta_ver;
        END LOOP;
    END;
  END LOOP;

EXCEPTION
  WHEN OTHERS THEN
    log_error(SQLCODE, 'EM_SEVERITY_REPOS.ADD_TARGET_PROPERTY(): '|| SQLERRM);
    -- Raise the exception again
    RAISE;
END add_target_property;

-- rm_target_property()  -> TBD
--
--------------
-- Notes:
--------------
PROCEDURE rm_target_property
IS
BEGIN

  -- Lock mgmt_target_type_versions table, disallowing new target type 
  -- versions to be added.
  -- Note also, that this locks serializes all add/remove/propagate property
  -- operations.
  -- The mode used still allows for "select for update" to go through (least
  -- restrictive possible).
  LOCK TABLE mgmt_target_type_versions IN SHARE ROW EXCLUSIVE MODE;

  -- 
EXCEPTION
  WHEN OTHERS THEN
    log_error(SQLCODE, 'MGMT_PROPERTIES.RM_TARGET_PROPERTY(): '|| SQLERRM);
    -- Raise the exception again
    RAISE;
END rm_target_property;

-- add_user_target_property()
--
--------------
-- Notes:
--------------
-- Adds user target properties. Please refer to the API of
-- add_target_property() above for detailed info.
--
PROCEDURE add_user_target_property(
     p_property_name          IN MGMT_TARGET_PROP_DEFS.property_name%TYPE,
     p_target_type_list       IN SMP_EMD_STRING_ARRAY,
     p_property_display_name  IN 
       MGMT_TARGET_PROP_DEFS.property_display_name%TYPE DEFAULT NULL,
     p_property_display_nlsid IN 
       MGMT_TARGET_PROP_DEFS.property_display_nlsid%TYPE DEFAULT NULL,
     p_default_value          IN 
       MGMT_TARGET_PROP_DEFS.default_value%TYPE DEFAULT NULL,
     p_computed_flag          IN 
       MGMT_TARGET_PROP_DEFS.computed_flag%TYPE DEFAULT 0,
     p_credential_flag        IN 
       MGMT_TARGET_PROP_DEFS.credential_flag%TYPE DEFAULT 0,
     p_required_flag          IN 
       MGMT_TARGET_PROP_DEFS.required_flag%TYPE DEFAULT 0,
     p_read_only_flag         IN 
       MGMT_TARGET_PROP_DEFS.read_only_flag%TYPE DEFAULT 0,
     p_hidden_flag            IN 
       MGMT_TARGET_PROP_DEFS.hidden_flag%TYPE DEFAULT 0)
IS
  l_property_name MGMT_TARGET_PROP_DEFS.property_name%TYPE;
  l_property_display_name MGMT_TARGET_PROP_DEFS.property_display_name%TYPE;
BEGIN
  -- Propagate the display name before rewritting it (if necessary)
  IF (p_property_display_name IS NULL) THEN
    l_property_display_name := p_property_name;
  ELSE
    l_property_display_name := p_property_display_name;
  END IF;

  -- Prefix the property name with "User Defined Property"
  l_property_name := DEFAULT_USR_PREFIX || p_property_name;

  -- Add the property
  add_target_property(p_property_name => l_property_name,
                      p_property_type => INSTANCE,
                      p_system_flag => 0,
                      p_credential_flag => p_credential_flag,
                      p_required_flag => p_required_flag,
                      p_computed_flag => p_computed_flag,
                      p_read_only_flag => p_read_only_flag,
                      p_hidden_flag => p_hidden_flag,
                      p_default_value => p_default_value,
                      p_property_display_name => l_property_display_name,
                      p_property_display_nlsid => p_property_display_nlsid,
                      p_target_type_list => p_target_type_list);
END add_user_target_property;


-- add_sys_target_property()
--
--------------
-- Notes:
--------------
-- Adds system target properties. Please refer to the API of
-- add_target_property() above for detailed info. Only Oracle developers should
-- use this API. Note that all property names are automaically prepended with
-- the default system prefix (DEFAULT_SYS_PREFIX), this avoids name colision
-- now and in the future with user defined properties.
--
PROCEDURE add_sys_target_property(
     p_property_name          IN MGMT_TARGET_PROP_DEFS.property_name%TYPE,
     p_target_type_list       IN SMP_EMD_STRING_ARRAY,
     p_property_display_name  IN 
       MGMT_TARGET_PROP_DEFS.property_display_name%TYPE DEFAULT NULL,
     p_property_display_nlsid IN 
       MGMT_TARGET_PROP_DEFS.property_display_nlsid%TYPE DEFAULT NULL,
     p_default_value          IN 
       MGMT_TARGET_PROP_DEFS.default_value%TYPE DEFAULT NULL,
     p_computed_flag          IN 
       MGMT_TARGET_PROP_DEFS.computed_flag%TYPE DEFAULT 0,
     p_credential_flag        IN 
       MGMT_TARGET_PROP_DEFS.credential_flag%TYPE DEFAULT 0,
     p_required_flag          IN 
       MGMT_TARGET_PROP_DEFS.required_flag%TYPE DEFAULT 0,
     p_read_only_flag         IN 
       MGMT_TARGET_PROP_DEFS.read_only_flag%TYPE DEFAULT 0,
     p_hidden_flag            IN 
       MGMT_TARGET_PROP_DEFS.hidden_flag%TYPE DEFAULT 0)
IS
  l_property_name MGMT_TARGET_PROP_DEFS.property_name%TYPE;
  l_property_display_name MGMT_TARGET_PROP_DEFS.property_display_name%TYPE;
BEGIN
  -- Propagate the display name before rewritting it (if necessary)
  IF (p_property_display_name IS NULL) THEN
    l_property_display_name := p_property_name;
  ELSE
    l_property_display_name := p_property_display_name;
  END IF;

  -- Prefix the property name with "System Defined Property"
  l_property_name := DEFAULT_SYS_PREFIX || p_property_name;

  -- Add the property
  add_target_property(p_property_name => l_property_name,
                      p_property_type => INSTANCE,
                      p_system_flag => 1,
                      p_credential_flag => p_credential_flag,
                      p_required_flag => p_required_flag,
                      p_computed_flag => p_computed_flag,
                      p_read_only_flag => p_read_only_flag,
                      p_hidden_flag => p_hidden_flag,
                      p_default_value => p_default_value,
                      p_property_display_name => l_property_display_name,
                      p_property_display_nlsid => p_property_display_nlsid,
                      p_target_type_list => p_target_type_list);
END add_sys_target_property;

-- propagate_target_properties()
--
--------------
-- Notes:
--------------
-- Propagates properties from other versions to the newly added version (and
-- in case this is the first version from mgmt_all_target_props).
-- add_target_property() above for detailed info.
--
-- This procedure propagates (User Defined Target Properties) UDTPs for all new 
-- target versions added and for all new target types added (CTPs only)
--
-- To simplify things and avoid dealing with all problems that might arrise (1)
-- if one were to execute this trigger every time a row is inserted into this
-- table, we make a fair assumption here, that every target type has at least 
-- one property, and therefore every time a target type version is added the
-- MGMT_METRICS table gets updated.
-- Note 1 : If we were to execute this trigger at every row insert, then we'd
-- have to consider things like "what order to rows come in", "when does the 
-- loader commit", etc. so that we can avoid deadlocks and duplicate value
-- exceptions. In particular, here's an example that would have to be dealt
-- with otherwise:
--  Thread A : insert values: orcl_db, 1.0, table_A
--  Thread B : insert values: orcl_db, 1.0, table_B
-- Also remember that Oracle DOES NOT AWAKEN a thread that is sleeping on a
-- resource if that resource becomes available due to a partial rollback (i.e.
-- savepoint rollback)...
--
PROCEDURE propagate_target_properties(
     p_target_type   IN 
       mgmt_target_type_versions.target_type%TYPE,
     p_type_meta_ver IN
       mgmt_target_type_versions.type_meta_ver%TYPE)
IS
BEGIN
  -- No need for lock on target type versions since we already disallow
  -- multiple versions for a target type to be added at same time

  -- Move over all UDTPs from previous versions of the target type skipping
  -- any that have the same name/type as the any TIP added in this version
  INSERT INTO mgmt_target_prop_defs
                 (target_type,
                  type_meta_ver,
                  property_name,
                  property_type,
                  property_display_name,
                  property_display_nlsid,
                  required_flag,
                  credential_flag,
                  default_value,
                  computed_flag,
                  system_flag,
                  all_versions)
    WITH prop_defs AS
    (
     SELECT type_meta_ver, property_name, property_type, property_display_name,
            property_display_nlsid, required_flag, credential_flag, 
            default_value, computed_flag, system_flag, all_versions
       FROM mgmt_target_prop_defs
      WHERE target_type = p_target_type
    )
    SELECT p_target_type, p_type_meta_ver, pdef1.property_name,
           pdef1.property_type, pdef1.property_display_name, 
           pdef1.property_display_nlsid, pdef1.required_flag, 
           pdef1.credential_flag, pdef1.default_value, pdef1.computed_flag, 
           pdef1.system_flag, pdef1.all_versions
      FROM
         (
           SELECT DISTINCT property_name, property_type, property_display_name,
                  property_display_nlsid, required_flag, credential_flag, 
                  default_value, computed_flag, system_flag, all_versions
             FROM prop_defs
            WHERE type_meta_ver != p_type_meta_ver
                    AND all_versions = 1
         ) pdef1 LEFT OUTER JOIN
         (
           SELECT *
            FROM prop_defs
           WHERE type_meta_ver = p_type_meta_ver
         ) pdef2
        ON pdef1.property_name = pdef2.property_name
       AND pdef1.property_type = pdef2.property_type
     WHERE pdef2.property_name IS NULL;
 
  -- Add all UDTPs that were not UDTPs in the previous target type versions
  -- because a TIP overwrote them, ignoring any properties already in this
  -- version. Note that doing this after all properties from the previous
  -- versions are copied over guarantees that any UDTPs defined on this type
  -- that are also defined at the global level have precedence over the global
  INSERT INTO mgmt_target_prop_defs
                 (target_type,
                  type_meta_ver,
                  property_name,
                  property_type,
                  property_display_name,
                  property_display_nlsid,
                  required_flag,
                  credential_flag,
                  default_value,
                  computed_flag,
                  system_flag,
                  all_versions)
    WITH prop_defs AS
    (
     SELECT type_meta_ver, property_name, property_type, property_display_name,
            property_display_nlsid, required_flag, credential_flag,
            default_value, computed_flag, system_flag, all_versions
       FROM mgmt_target_prop_defs
      WHERE target_type = p_target_type
        AND type_meta_ver = p_type_meta_ver
    )
    SELECT DISTINCT p_target_type, p_type_meta_ver, pdef1.property_name, 
           pdef1.property_type, pdef1.property_display_name, 
           pdef1.property_display_nlsid, 0, pdef1.credential_flag, 
           pdef1.default_value, pdef1.computed_flag, pdef1.system_flag, 1
      FROM mgmt_all_target_props pdef1 LEFT OUTER JOIN
           prop_defs pdef2
        ON (pdef1.property_name = pdef2.property_name
       AND pdef1.property_type = pdef2.property_type)
     WHERE pdef2.property_name IS NULL;

EXCEPTION
  WHEN OTHERS THEN
    log_error(SQLCODE, 'MGMT_PROPERTIES.PROPAGATE_PROPERTIES(' ||
              p_target_type || ', ' || p_type_meta_ver || '): '|| SQLERRM);
    RAISE;
END propagate_target_properties;


-- set_target_property()
--
--------------
-- Notes:
--------------
-- Sets the value of a given target property. The definition of the property
-- is validated in mgmt_target_prop_defs, and an error is thrown if this def
-- does not exist.
-- WARNING: This function does not update any copies of the property that
-- might exist at the agent(s).
--
PROCEDURE set_target_property(
     p_target_name    IN mgmt_targets.target_name%TYPE,
     p_target_type    IN mgmt_targets.target_type%TYPE,
     p_property_name  IN mgmt_target_properties.property_name%TYPE,
     p_property_type  IN mgmt_target_properties.property_type%TYPE,
     p_property_value IN mgmt_target_properties.property_value%TYPE)
IS
  l_property   MGMT_TARGET_PROPERTY;
  l_properties MGMT_TARGET_PROPERTY_LIST;
BEGIN
  l_property := MGMT_TARGET_PROPERTY(p_property_name, p_property_type,
                                     p_property_value);
  l_properties := MGMT_TARGET_PROPERTY_LIST(l_property);
  set_target_properties(p_target_name, p_target_type, l_properties);
END set_target_property;


-- set_target_properties()
--
--------------
-- Notes:
--------------
-- Sets the value of the given target properties. The definition of the
-- properties is validated in mgmt_target_prop_defs, and an error is thrown 
-- if defs don't exist.
-- WARNING: This function does not update the agent copies of the properties.
--
PROCEDURE set_target_properties(
     p_target_name        IN mgmt_targets.target_name%TYPE,
     p_target_type        IN mgmt_targets.target_type%TYPE,
     p_properties         IN MGMT_TARGET_PROPERTY_LIST)
IS
  l_tmp NUMBER(4);
  l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user();
  l_data_set_guid RAW(16);
  l_data_guids MGMT_USER_GUID_ARRAY;
  l_targets MGMT_JOB_TARGET_LIST;
  l_target_guid mgmt_targets.target_guid%TYPE;
BEGIN
  -- Verify privilege
  l_tmp := mgmt_user.has_priv(l_current_user, mgmt_user.OPERATOR_TARGET,
                              p_target_name, p_target_type);
  IF (l_tmp = 0) THEN
    raise_application_error(mgmt_global.INSUFFICIENT_PRIVILEGES_ERR,
      'set_target_property(...): The current user [' || l_current_user || 
      '] does not have enough privileges to perform this operation.');
  END IF;

  IF p_properties IS NULL OR
     p_properties.COUNT = 0 
  THEN
    RETURN ;
  END IF ;

  -- Verify that the properties are defined for the target type version in 
  -- question.
  SELECT count(*) INTO l_tmp
    FROM mgmt_targets tgt, mgmt_target_prop_defs pdef,
         TABLE(CAST(p_properties AS MGMT_TARGET_PROPERTY_LIST)) props
   WHERE tgt.target_name = p_target_name
     AND tgt.target_type = p_target_type
     AND tgt.target_type = pdef.target_type
     AND tgt.type_meta_ver = pdef.type_meta_ver
     AND pdef.property_name = props.property_name
     AND pdef.property_type = props.property_type;

  IF (l_tmp != p_properties.COUNT) THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'Undefined property. set_target_properties(' || p_target_name || ' ,' ||
                                                 p_target_type || ' , ...)');
  END IF;

  -- Get the target guid of the target
  SELECT target_guid INTO l_target_guid
    FROM mgmt_targets tgt
   WHERE target_name = p_target_name
     AND target_type = p_target_type;

  FOR i IN 1..p_properties.COUNT
  LOOP
    BEGIN
      INSERT INTO mgmt_target_properties
      (target_guid, property_name, property_type, property_value)
      VALUES
      (l_target_guid, p_properties(i).property_name,
       p_properties(i).property_type, p_properties(i).property_value);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        UPDATE mgmt_target_properties
           SET property_value = p_properties(i).property_value
         WHERE target_guid = l_target_guid
           AND property_name = p_properties(i).property_name
           AND property_type = p_properties(i).property_type;
    END;
  END LOOP;

END set_target_properties;

-- handle_mgmt_tgt_props()
--
--------------
-- Notes:
--------------
-- This function is called by the loader when the agent sending data is older
-- than 10.2, and is responsible for updating the property_type to INSTANCE
-- from DYNAMICINSTANCE if needed.
--
PROCEDURE handle_mgmt_tgt_props(
     p_target_guid      IN mgmt_target_properties.target_guid%TYPE
                           DEFAULT NULL,
     p_property_name    IN mgmt_target_properties.property_name%TYPE,
     p_property_type    IN mgmt_target_properties.property_type%TYPE 
                           DEFAULT mgmt_properties.INSTANCE,
     p_property_value   IN mgmt_target_properties.property_value%TYPE
                           DEFAULT ' ')
IS
  DYNAMICINSTANCE CONSTANT VARCHAR2(20) := 'DYNAMICINSTANCE';
  l_property_type mgmt_target_properties.property_type%TYPE;
  l_target_type mgmt_targets.target_type%TYPE;
  UNENCRYPTED_RACDB_PASSWD_FOUND EXCEPTION;
BEGIN
  IF (UPPER(p_property_type) = DYNAMICINSTANCE) THEN
    l_property_type := INSTANCE;
  ELSE
    l_property_type := p_property_type;
  END IF;

  -- For Metadata Version 4.1 with 10.2.0.1 agent,
  -- password property of rac_database target was 
  -- saved unencrypted into mgmt_target_properties.
  -- This code prevents such entries.
  -- See bugs 6396920 and 6432176.
  IF (p_property_name = 'password') THEN
    SELECT target_type into l_target_type
    FROM   mgmt_targets
    WHERE  target_guid = p_target_guid;

    IF (l_target_type = MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE) THEN
       RAISE UNENCRYPTED_RACDB_PASSWD_FOUND;
    END IF;
  END IF;

  INSERT INTO mgmt_target_properties(target_guid, property_name, property_type,
                                     property_value)
  VALUES (p_target_guid, p_property_name, l_property_type, p_property_value);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UPDATE mgmt_target_properties
       SET property_value = p_property_value
     WHERE target_guid = p_target_guid
       AND property_name = p_property_name
       AND property_type = l_property_type;
  WHEN UNENCRYPTED_RACDB_PASSWD_FOUND THEN
    NULL;
END handle_mgmt_tgt_props;
-- Generic Private procedure to add a callback
PROCEDURE add_callback
(
  p_callback_type  IN mgmt_callbacks.callback_type%TYPE,
  p_procedure_name IN mgmt_callbacks.callback_name%TYPE,
  p_signature      IN mgmt_short_string_array,
  p_selector_1     IN mgmt_callbacks.selector_1%TYPE DEFAULT '%',
  p_selector_2     IN mgmt_callbacks.selector_2%TYPE DEFAULT '%',
  p_selector_3     IN mgmt_callbacks.selector_3%TYPE DEFAULT '%',
  p_eval_order     IN NUMBER DEFAULT 0
)
AS
l_error_message VARCHAR2(1000);
BEGIN

  IF NOT EM_CHECK.is_valid_signature(p_procedure_name,
                                     p_signature,
                                     l_error_message)
  THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,l_error_message) ;
  END IF ;

  INSERT INTO mgmt_callbacks
   (callback_type,callback_name,selector_1,selector_2,
    selector_3,eval_order)
  VALUES 
   (p_callback_type, p_procedure_name, p_selector_1, p_selector_2,
    p_selector_3, p_eval_order);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    raise_application_error(mgmt_global.invalid_params_err,
                            'callback '||p_procedure_name||' already exists');
END add_callback;

PROCEDURE del_callback
(
  p_callback_type  IN mgmt_callbacks.callback_type%TYPE,
  p_procedure_name IN mgmt_callbacks.callback_name%TYPE,
  p_selector_1     IN mgmt_callbacks.selector_1%TYPE DEFAULT '%',
  p_selector_2     IN mgmt_callbacks.selector_2%TYPE DEFAULT '%',
  p_selector_3     IN mgmt_callbacks.selector_3%TYPE DEFAULT '%'
)
IS
BEGIN
  DELETE mgmt_callbacks
   WHERE callback_type = p_callback_type
     AND callback_name = p_procedure_name
     AND selector_1    = p_selector_1
     AND selector_2    = p_selector_2
     AND selector_3    = p_selector_3 ;
END del_callback ;

PROCEDURE add_property_change_callback
            (p_procedure_name     IN mgmt_callbacks.callback_name%TYPE,
             p_target_type        IN mgmt_callbacks.selector_1%TYPE := '%',
             p_property_name      IN mgmt_callbacks.selector_2%TYPE := '%',
             p_new_property_value IN mgmt_callbacks.selector_3%TYPE := '%',
             p_eval_order         IN mgmt_callbacks.eval_order%TYPE := 0)
IS
BEGIN
  EM_CHECK.check_not_null(p_target_type,'p_target_type') ;
  EM_CHECK.check_not_null(p_procedure_name,'p_procedure_name') ;
  EM_CHECK.check_not_null(p_property_name,'p_property_name') ;
  EM_CHECK.check_not_null(p_new_property_value,'p_new_property_value') ;
  add_callback(p_callback_type=>MGMT_GLOBAL.G_TARGET_PROP_UPD_CALLBACK,
               p_procedure_name=>p_procedure_name,
               p_signature=>mgmt_short_string_array('MGMT_TARGET_PROPERTY_CBK_OBJ'),
               p_selector_1=>p_target_type,
               p_selector_2=>p_property_name,
               p_selector_3=>p_new_property_value,
               p_eval_order=>p_eval_order) ;
END add_property_change_callback ;

PROCEDURE del_property_change_callback
            (p_procedure_name     IN mgmt_callbacks.callback_name%TYPE,
             p_target_type        IN mgmt_callbacks.selector_1%TYPE := '%',
             p_property_name      IN mgmt_callbacks.selector_2%TYPE := '%',
             p_new_property_value IN mgmt_callbacks.selector_3%TYPE := '%'
            )
IS
BEGIN
  EM_CHECK.check_not_null(p_target_type,'p_target_type') ;
  EM_CHECK.check_not_null(p_procedure_name,'p_procedure_name') ;
  EM_CHECK.check_not_null(p_property_name,'p_property_name') ;
  EM_CHECK.check_not_null(p_new_property_value,'p_new_property_value') ;
  del_callback(p_callback_type=>MGMT_GLOBAL.G_TARGET_PROP_UPD_CALLBACK,
               p_procedure_name=>p_procedure_name,
               p_selector_1=>p_target_type,
               p_selector_2=>p_property_name,
               p_selector_3=>p_new_property_value) ;
END del_property_change_callback ;

PROCEDURE exec_property_change_callbacks
            (p_target_guid        IN mgmt_targets.target_guid%TYPE,
             p_property_name      IN mgmt_callbacks.selector_1%TYPE,
             p_old_property_value IN mgmt_callbacks.selector_2%TYPE,
             p_new_property_value IN mgmt_callbacks.selector_3%TYPE
            )
IS
l_callback_obj mgmt_target_property_cbk_obj ;
BEGIN
  IF EMDW_LOG.P_IS_INFO_SET
  THEN
    EMDW_LOG.INFO('exec_property_change_callback:Enter',G_MODULE_NAME) ;
    EMDW_LOG.DEBUG('exec_property_change_callback:'||
             ' Target='||p_target_guid ||
             ' property='||p_property_name||
             ' property value old ='||p_old_property_value||
             ' property value new ='||p_new_property_value ,G_MODULE_NAME) ;
  END IF ;
  FOR rec IN ( SELECT cbk.callback_name,tgt.target_name
                 FROM mgmt_callbacks cbk,
                      mgmt_targets tgt
                WHERE tgt.target_guid   = p_target_guid 
                  AND cbk.callback_type = MGMT_GLOBAL.G_TARGET_PROP_UPD_CALLBACK
                  AND tgt.target_type      LIKE cbk.selector_1 ESCAPE '\'
                  AND p_property_name      LIKE cbk.selector_2 ESCAPE '\'
                  AND p_new_property_value LIKE cbk.selector_3 ESCAPE '\'
               ORDER BY cbk.eval_order,cbk.callback_name 
              )
  LOOP
    BEGIN
      IF EMDW_LOG.P_IS_DEBUG_SET
      THEN
        EMDW_LOG.DEBUG('exec_property_change_callback:Executing '||
             rec.callback_name,G_MODULE_NAME) ;
      END IF ;
      l_callback_obj := mgmt_target_property_cbk_obj.new(
                          p_target_guid=>p_target_guid,
                          p_property_name=>p_property_name,
                          p_old_property_value=>p_old_property_value,
                          p_new_property_value=>p_new_property_value) ;
      EXECUTE IMMEDIATE 
        'BEGIN '|| EM_CHECK.QUALIFIED_SQL_NAME(rec.callback_name)|| 
        '(:1); END;'
        USING l_callback_obj ;
    EXCEPTION
    WHEN OTHERS THEN
      log_error(sqlcode,
            sqlerrm||' when  executing callback '||rec.callback_name|| 
             ' Target='||rec.target_name ||
             ' property='||p_property_name||
             ' property value old ='||p_old_property_value||
             ' property value new ='||p_new_property_value) ;
      IF EMDW_LOG.P_IS_ERROR_SET
      THEN
        EMDW_LOG.ERROR('exec_property_change_callback:Exception '||rec.callback_name
                       ||':'||sqlerrm, G_MODULE_NAME) ;
      END IF ;
    END ;
  END LOOP ;
  IF EMDW_LOG.P_IS_INFO_SET
  THEN
    EMDW_LOG.INFO('exec_property_change_calbacks:Exit',G_MODULE_NAME) ;
  END IF ;
END exec_property_change_callbacks ;

END mgmt_properties;
/
SHOW ERRORS;