Rem drv: <create type="pkgbodys"/>
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/dg/dg_pkgbodys.sql /st_emdbsa_11.2/2 2009/02/24 09:50:41 ngade Exp $
Rem
Rem dg_pkgbodys.sql
Rem
Rem Copyright (c) 2008, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      dg_pkgbodys.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    gallison    10/24/08 - Use RTQ in stby_list for standbys
Rem    ngade       08/04/08 - 
Rem    rimmidi     08/04/08 - 
Rem    gallison    03/13/08 - Handle standby status
Rem    sjconnol    07/20/07 - Change mgmt_ha_dg_target_sum handler
Rem    ngade       03/18/07 - Created
Rem

-- This forces sqlplus to ignore ampersand chars
SET DEFINE OFF

CREATE OR REPLACE PACKAGE BODY MGMT_HA_DG as

PROCEDURE update_mgmt_ha_dg_target_sum(
    p_target_guid IN RAW,
    p_collection_timestamp IN DATE DEFAULT SYSDATE,
    p_source_type IN VARCHAR2 DEFAULT NULL,
    p_row_type IN VARCHAR2 DEFAULT NULL,
    p_using_broker IN VARCHAR2 DEFAULT NULL,
    p_active_stby IN VARCHAR2 DEFAULT NULL,
    p_db_unique_name IN VARCHAR2 DEFAULT NULL,
    p_db_id IN NUMBER DEFAULT NULL,
    p_prmy_db_unique_name IN VARCHAR2 DEFAULT NULL,
    p_prmy_db_id IN NUMBER DEFAULT NULL,
    p_role IN VARCHAR2 DEFAULT NULL,
    p_stby_list IN VARCHAR2 DEFAULT NULL,
    p_protection_mode IN VARCHAR2 DEFAULT NULL,
    p_fsfo_status IN VARCHAR2 DEFAULT NULL,
    p_status IN VARCHAR2 DEFAULT NULL,
    p_transport_lag IN NUMBER DEFAULT NULL,
    p_apply_lag IN NUMBER DEFAULT NULL) IS

 l_procstr   varchar2(1024);
 l_output    varchar2(64);
 l_count     number(2);
 l_procarray MGMT_SHORT_STRING_ARRAY := MGMT_SHORT_STRING_ARRAY();

BEGIN
    -- If adding a row for a primary,
    -- flag standby rows that are no longer in the list of standbys for this primary.
    BEGIN
      IF p_source_type = 'P' and p_row_type = 'P' THEN
        -- Parse list of standbys in p_stby_list
        l_procstr := p_stby_list||',';
        FOR i in 1..30 loop
          l_output:=substr(l_procstr,1,(instr(l_procstr,',')-1));
          exit when (l_output IS NULL);
          l_count:=instr(l_procstr,',')+1;
          l_procstr:=substr(l_procstr,l_count,length(l_procstr));
          l_procarray.extend(1);
          l_procarray(i) := l_output;
        END loop;

        UPDATE MGMT_HA_DG_TARGET_SUMMARY SET
         active_stby = 'NO'
        WHERE prmy_db_unique_name = p_db_unique_name 
        AND prmy_db_id = p_db_id
        AND db_unique_name NOT IN (select column_value from TABLE(CAST(l_procarray AS MGMT_SHORT_STRING_ARRAY)));
--        DELETE from MGMT_HA_DG_TARGET_SUMMARY 
--          WHERE prmy_db_unique_name = p_db_unique_name 
--          AND prmy_db_id = p_db_id
--          AND collection_timestamp < (select collection_timestamp from MGMT_HA_DG_TARGET_SUMMARY where target_guid = p_target_guid);
      END IF;        
    EXCEPTION WHEN OTHERS THEN NULL;
    END;

    -- Insert the entry into MGMT_HA_DG_TARGET_SUMMARY table

    -- Stby rows uploaded from the prmy collection are used only to update
    -- existing rows uploaded from the stby. Update only the status (which
    -- must be collected on the prmy to avoid stale data) 
    -- and prmy_db_unique_name (not collected on standby)
    -- and active_stby (always YES when uploaded from prmy; not uploaded from stby).
    IF p_source_type = 'P' and p_row_type = 'S' THEN
      BEGIN
        UPDATE MGMT_HA_DG_TARGET_SUMMARY SET
         collection_timestamp = p_collection_timestamp, 
         source_type = p_source_type,
         row_type = p_row_type,
         using_broker = p_using_broker,
         active_stby = p_active_stby,
         prmy_db_unique_name = p_prmy_db_unique_name, 
         status = p_status
        WHERE db_unique_name = p_db_unique_name
        AND prmy_db_id = p_prmy_db_id;
      EXCEPTION WHEN OTHERS THEN NULL;
      END;
    -- Primary row uploaded from the prmy: insert/update all data
    ELSIF p_source_type = 'P' and p_row_type = 'P' THEN
      BEGIN
        INSERT INTO MGMT_HA_DG_TARGET_SUMMARY(
           target_guid, collection_timestamp, source_type, row_type, using_broker, active_stby, db_unique_name, db_id, prmy_db_unique_name, prmy_db_id, role, stby_list, protection_mode, fsfo_status, status, transport_lag, apply_lag) 
        VALUES (
           p_target_guid, p_collection_timestamp, p_source_type, p_row_type, p_using_broker, p_active_stby, p_db_unique_name, p_db_id, p_prmy_db_unique_name, p_prmy_db_id, p_role, p_stby_list, p_protection_mode, p_fsfo_status, p_status, p_transport_lag, p_apply_lag);
      EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
        -- Entry already exists, update the entry (excluding lag values)
        UPDATE MGMT_HA_DG_TARGET_SUMMARY SET
         collection_timestamp = p_collection_timestamp, 
         source_type = p_source_type,
         row_type = p_row_type,
         using_broker = p_using_broker,
         active_stby = p_active_stby,
         db_unique_name = p_db_unique_name, 
         db_id = p_db_id,
         prmy_db_unique_name = p_prmy_db_unique_name, 
         prmy_db_id = p_prmy_db_id,
         role = p_role,
         stby_list = p_stby_list,
         protection_mode = p_protection_mode, 
         fsfo_status = p_fsfo_status, 
         status = p_status
        WHERE target_guid = p_target_guid;
      END;
    -- Standby row uploaded from the stby: insert all data, or update everything
    -- except prmy_db_unique_name, status, active_stby, stby_list,
    -- & other prmy-only props
    ELSE
      BEGIN
        INSERT INTO MGMT_HA_DG_TARGET_SUMMARY(
           target_guid, collection_timestamp, source_type, row_type, db_unique_name, db_id, prmy_db_unique_name, prmy_db_id, role, stby_list,
           protection_mode, fsfo_status, status, transport_lag, apply_lag) 
        VALUES (
           p_target_guid, p_collection_timestamp, p_source_type, p_row_type, p_db_unique_name, p_db_id, p_prmy_db_unique_name, p_prmy_db_id, p_role, p_stby_list,
           p_protection_mode, p_fsfo_status, p_status, p_transport_lag, p_apply_lag);
      EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
        -- Entry already exists, update the entry (excluding lag values)
        UPDATE MGMT_HA_DG_TARGET_SUMMARY SET
         collection_timestamp = p_collection_timestamp, 
         source_type = p_source_type,
         row_type = p_row_type,
         db_unique_name = p_db_unique_name, 
         db_id = p_db_id,
         prmy_db_id = p_prmy_db_id,
         role = p_role,
         stby_list = p_stby_list,
         transport_lag = p_transport_lag,
         apply_lag = p_apply_lag
        WHERE target_guid = p_target_guid;

        -- if the standby gets an error trying to reach the primary, update the primary's status since the primary is
        -- down and no longer collecting (Error ORA-16625: cannot reach the database). dgCollector.pl maintains the list of
        -- ORA errors. Any standby non-null status here is inserted into the primary's status.
        IF p_status IS NOT NULL THEN
           UPDATE MGMT_HA_DG_TARGET_SUMMARY SET
             collection_timestamp = p_collection_timestamp, 
             status = p_status             
           WHERE p_db_id = p_prmy_db_id
           AND row_type = 'P';
        END IF;
      END;
    END IF;        

EXCEPTION WHEN OTHERS THEN NULL;

END update_mgmt_ha_dg_target_sum;

END MGMT_HA_DG;
/
SET DEFINE ON
show errors;