Rem
Rem $Header: ecm_hostpatch_uln_pkgbody.sql 07-aug-2007.13:44:52 tasingh Exp $
Rem
Rem ecm_hostpatch_uln_pkgbody.sql
Rem
Rem Copyright (c) 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_hostpatch_uln_pkgbody.sql - Defines the MGMT_ECM_HOSTPATCH_ULN pkg
Rem
Rem    DESCRIPTION
Rem      Defines the MGMT_ECM_HOSTPATCH_ULN package
Rem
Rem    NOTES
Rem      None.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    tasingh     08/07/07 - Package body for linux patchign using unbreakable
Rem                           linux network project.
Rem    tasingh     08/07/07 - Created
Rem

CREATE OR REPLACE PACKAGE BODY mgmt_ecm_hostpatch_uln
IS

  --
  -- Delete the channel 
  --
  PROCEDURE delete_channel(
    p_channel_name IN VARCHAR2,
    p_stage_server_name IN VARCHAR2)
  IS
    l_stage_server_guid mgmt_ecm_uln_stage_servers.stage_server_guid%type;
    l_channel_guid mgmt_ecm_uln_channels.channel_guid%type;
  BEGIN
    -- get the stage server guid
    BEGIN
      SELECT target_guid INTO l_stage_server_guid
        FROM mgmt_targets
        WHERE target_name = p_stage_server_name
        AND   target_type = 'host';
    EXCEPTION
      --We don't know about this stage server - don't bother anymore.
      WHEN NO_DATA_FOUND THEN
        RETURN;
    END;

    -- Get the channel_guid
    BEGIN
      SELECT channel_guid INTO l_channel_guid
        FROM mgmt_ecm_uln_channels
        WHERE channel_name = p_channel_name;
    EXCEPTION
      -- We don't know about this channel - don't bother anymore.
      WHEN NO_DATA_FOUND THEN
        RETURN;
    END;

    -- Remove the entry from the mgmt_ecm_uln_ss_channels table
    DELETE FROM mgmt_ecm_uln_ss_channels 
      WHERE stage_server_guid = l_stage_server_guid
      AND   channel_guid = l_channel_guid;

    -- Check if there is any other staging server that has this channel.
    -- If not then we have to remove the channel from mgmt_ecm_uln_channels
    -- table as well
    DECLARE
      l_num_stage_servers NUMBER(1);
    BEGIN
      SELECT COUNT(stage_server_guid) INTO l_num_stage_servers
        FROM mgmt_ecm_uln_ss_channels
        WHERE channel_guid = l_channel_guid;

      IF l_num_stage_servers = 0
      THEN
        -- There is no other stage server that has this channel. So remove
        -- the channel from mgmt_ecm_uln_channels table as well
        DELETE FROM mgmt_ecm_uln_channels
          WHERE CHANNEL_GUID = l_channel_guid;
      END IF;
    END;
  END;

  --
  -- Add channel for a given stage server. If this channel is not present in
  -- the mgmt_ecm_uln_channels table, first make an entry in that table
  --
  PROCEDURE add_channel(
    p_channel_name IN VARCHAR2,
    p_stage_server_name IN VARCHAR2)
  IS
    l_stage_server_guid mgmt_ecm_uln_stage_servers.stage_server_guid%type;
    l_channel_guid mgmt_ecm_uln_channels.channel_guid%type;
  BEGIN
    -- see if this channel exists in the mgmt_ecm_uln_channels table
    BEGIN
      SELECT channel_guid INTO l_channel_guid
        FROM mgmt_ecm_uln_channels
        WHERE channel_name = p_channel_name;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      -- This channel does not exists in the channels table
      -- Enter this channel in the channels table
        BEGIN
          -- Get the GUID
          SELECT SYS_GUID() INTO l_channel_guid
            FROM dual;

          -- Now insert into table
          INSERT INTO
            mgmt_ecm_uln_channels(
              channel_guid,
              channel_name)
            VALUES(l_channel_guid, p_channel_name);
        END;
    END;

    -- Get the stage server GUID
    BEGIN
      SELECT target_guid INTO l_stage_server_guid
        FROM mgmt_targets
        WHERE target_name = p_stage_server_name
        AND   target_type = 'host';
    EXCEPTION
      --We don't know about this stage server - don't bother anymore.
      WHEN NO_DATA_FOUND THEN
        RETURN;
    END;

    -- Now make an entry in the mgmt_ecm_uln_ss_channels table
    INSERT INTO
      mgmt_ecm_uln_ss_channels(
        stage_server_guid,
        channel_guid)
      VALUES(l_stage_server_guid, l_channel_guid);
  END;

END mgmt_ecm_hostpatch_uln;
/

show errors;