Rem drv: <create type="pkgbodys"/>
Rem
Rem $Header: net_pkgbodys.sql 10-jan-2006.22:44:20 vivsharm Exp $
Rem
Rem net_pkgbodys.sql
Rem
Rem Copyright (c) 2002, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      net_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    vivsharm    01/10/06 - bug 4938487 
Rem    vivsharm    09/12/05 - perf fix 
Rem    jpegu       07/08/05 - Performance Bug: 4470041 add get_lsnr_home_page_data
Rem    chyu        06/28/05 - New repmgr header impl 
Rem    dkapoor     11/11/03 - use host guid in MGMT_DBNET_TNS_ADMINS 
Rem    dkapoor     11/04/03 - fix 3231681 
Rem    hasriniv    10/16/03 - Moving proc get_lsnr_homepage_info here 
Rem    hasriniv    10/10/03 - Fix for bug 3187592 
Rem    hasriniv    09/02/03 - Fix for bug 3122598 
Rem    hasriniv    06/26/03 - Fix for bug 2797194
Rem    dkapoor     02/05/03 - dkapoor_impl_net_srvc
Rem    dkapoor     11/08/02 - implement
Rem    dkapoor     11/04/02 - Created
Rem

CREATE OR REPLACE PACKAGE body MGMT_DBNET AS

/*
Get the oracle homes and tns_admin locations for a Host Target.
The values returned as OUT parameters are used for Oracle Net Services Administration
*/
procedure getNetAdminInfo(
      	hostName IN VARCHAR2,
        hostType IN VARCHAR2,
        netAdmin_cur_out OUT cursorType) IS
BEGIN
    -- get all oracle homes on host
    OPEN netAdmin_cur_out FOR
        select n.ORACLE_HOME , n.TNS_ADMIN
                  from MGMT_DBNET_TNS_ADMINS n,
                       MGMT_TARGETS targets
                  where  targets.TARGET_GUID = n.HOST_GUID
                  and targets.target_name= hostName
                  and targets.target_type= hostType;
END getNetAdminInfo;   

/*
Return a cursor for the chart data based on the value for RANGE
*/
procedure getLsnrPerfChartData(
      	l_lsnrtargetName	IN	VARCHAR2,
        l_range			IN	VARCHAR2,
        l_lsnrData_cur_out	OUT cursorType) 
IS 
  l_lsnrTgt_guid		MGMT_TARGETS.target_guid%TYPE;
  l_type_meta_ver		MGMT_TARGETS.TYPE_META_VER%TYPE;
  l_category_prop_1		MGMT_TARGETS.CATEGORY_PROP_1%TYPE;
  l_category_prop_2		MGMT_TARGETS.CATEGORY_PROP_2%TYPE;
  l_category_prop_3		MGMT_TARGETS.CATEGORY_PROP_3%TYPE;
  l_category_prop_4		MGMT_TARGETS.CATEGORY_PROP_4%TYPE;
  l_category_prop_5		MGMT_TARGETS.CATEGORY_PROP_5%TYPE;
  l_timezone_region		MGMT_TARGETS.TIMEZONE_REGION%TYPE;
  l_estConn_Metric_guid		MGMT_METRICS.metric_guid%TYPE;
  l_refConn_Metric_guid		MGMT_METRICS.metric_guid%TYPE;
  l_cutoff_tstamp		MGMT_METRICS_RAW.COLLECTION_TIMESTAMP%TYPE;
  l_time_range			NUMBER;

BEGIN 

    BEGIN
	-- get Target Guid first	
	SELECT 	target_guid, type_meta_ver, category_prop_1, category_prop_2,
		category_prop_3, category_prop_4, category_prop_5, timezone_region
	INTO 	l_lsnrTgt_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
		l_category_prop_3, l_category_prop_4, l_category_prop_5, l_timezone_region
	FROM mgmt_targets
	WHERE target_name = l_lsnrtargetName and target_type = 'oracle_listener';

	EXCEPTION
	  WHEN NO_DATA_FOUND
	    THEN raise MGMT_GLOBAL.target_does_not_exist;
    END;

    BEGIN
	-- note: the "rownum = 1" is to ensure only one row is returned. This may
	-- not return the correct metric row if EMD allows empty and non-empty 
	-- values of category_prop_* columns to exist at the same time. The problem
	-- will also occur during migration.
	-- get Metric Guid for estConns
	SELECT metric_guid
	INTO l_estConn_Metric_guid
	FROM mgmt_metrics
	WHERE target_type = 'oracle_listener'
		AND metric_name = 'Load'
		AND metric_column = 'estConns'
		AND usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
		AND type_meta_ver = l_type_meta_ver
		AND (category_prop_1 = l_category_prop_1 OR category_prop_1 = ' ')
		AND (category_prop_2 = l_category_prop_2 OR category_prop_2 = ' ')
		AND (category_prop_3 = l_category_prop_3 OR category_prop_3 = ' ')
		AND (category_prop_4 = l_category_prop_4 OR category_prop_4 = ' ')
		AND (category_prop_5 = l_category_prop_5 OR category_prop_5 = ' ')
		AND rownum = 1;

	EXCEPTION
		WHEN NO_DATA_FOUND
		THEN raise MGMT_GLOBAL.no_such_metric_found;
    END;

    BEGIN
	-- get Metric Guid for refConns
	SELECT metric_guid
	INTO l_refConn_Metric_guid
	FROM mgmt_metrics
	WHERE target_type = 'oracle_listener'
		AND metric_name = 'Load'
		AND metric_column = 'refConns'
		AND usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
		AND type_meta_ver = l_type_meta_ver
		AND (category_prop_1 = l_category_prop_1 OR category_prop_1 = ' ')
		AND (category_prop_2 = l_category_prop_2 OR category_prop_2 = ' ')
		AND (category_prop_3 = l_category_prop_3 OR category_prop_3 = ' ')
		AND (category_prop_4 = l_category_prop_4 OR category_prop_4 = ' ')
		AND (category_prop_5 = l_category_prop_5 OR category_prop_5 = ' ')
		AND rownum = 1;

	EXCEPTION
	WHEN NO_DATA_FOUND
	THEN raise MGMT_GLOBAL.no_such_metric_found;
    END;

    BEGIN
	-- get the cutoff timestamp for graph

	IF l_range in ('2', '12') THEN 
		l_time_range := l_range/24;
	ELSIF l_range = '7*24' THEN
		l_time_range := 7;
	ELSE
		l_time_range := 2/24;
	END IF;

	SELECT 	COLLECTION_TIMESTAMP - l_time_range
	INTO 	l_cutoff_tstamp
	FROM	mgmt_current_metrics
	WHERE	METRIC_GUID = l_estConn_Metric_guid AND 
		TARGET_GUID = l_lsnrTgt_guid AND rownum = 1;

	EXCEPTION
	  WHEN NO_DATA_FOUND
	    --THEN raise MGMT_GLOBAL.target_does_not_exist;
	    THEN select SYSDATE into l_cutoff_tstamp from dual; 
    END;

    IF (l_range = '2') THEN 
      OPEN l_lsnrData_cur_out FOR 
	SELECT 
	  NVL(estConns.VALUE,0)  , 
	  NVL(refConns.VALUE,0) ,  
	  TO_DATE( TO_CHAR(estConns.COLLECTION_TIMESTAMP, 'MM/DD/YYYY HH24:MI:SS'), 'MM/DD/YYYY HH24:MI:SS') 
	FROM       
		(select * from MGMT_METRICS_RAW 
		 where	METRIC_GUID = l_estConn_Metric_guid 	AND
			TARGET_GUID = l_lsnrTgt_guid		AND
			COLLECTION_TIMESTAMP >= l_cutoff_tstamp) estConns,
		(select * from MGMT_METRICS_RAW 
		 where	METRIC_GUID = l_refConn_Metric_guid 	AND
			TARGET_GUID = l_lsnrTgt_guid		AND
			COLLECTION_TIMESTAMP >= l_cutoff_tstamp) refConns   
	WHERE 
		estConns.COLLECTION_TIMESTAMP = refConns.COLLECTION_TIMESTAMP  
	ORDER BY 3;
    ELSIF (l_range = '12') THEN 
      OPEN l_lsnrData_cur_out FOR 
	SELECT 
		AVG(NVL(estConns.VALUE,0)), 
		AVG(NVL(refConns.VALUE,0)),  
		TO_DATE( TO_CHAR(estConns.COLLECTION_TIMESTAMP, 'MM/DD/YYYY HH24'),'MM/DD/YYYY HH24')  
	FROM       
		(select * from MGMT_METRICS_RAW 
		 where	METRIC_GUID = l_estConn_Metric_guid 	AND
			TARGET_GUID = l_lsnrTgt_guid		AND
			COLLECTION_TIMESTAMP >= l_cutoff_tstamp) estConns,
		(select * from MGMT_METRICS_RAW 
		 where	METRIC_GUID = l_refConn_Metric_guid 	AND
			TARGET_GUID = l_lsnrTgt_guid		AND
			COLLECTION_TIMESTAMP >= l_cutoff_tstamp) refConns   
	WHERE 
		estConns.COLLECTION_TIMESTAMP = refConns.COLLECTION_TIMESTAMP   
	GROUP BY 
		TO_CHAR(estConns.COLLECTION_TIMESTAMP, 'MM/DD/YYYY HH24') 
	ORDER BY 3;
   ELSIF (l_range = '7*24') THEN
      OPEN l_lsnrData_cur_out FOR
	SELECT 
		AVG(NVL(estConns.VALUE,0)), 
		AVG(NVL(refConns.VALUE,0)),  
		TO_DATE( TO_CHAR(estConns.COLLECTION_TIMESTAMP, 'MM/DD/YYYY'),'MM/DD/YYYY')  
	FROM       
		(select * from MGMT_METRICS_RAW 
		 where	METRIC_GUID = l_estConn_Metric_guid 	AND
			TARGET_GUID = l_lsnrTgt_guid		AND
			COLLECTION_TIMESTAMP >= l_cutoff_tstamp) estConns,
		(select * from MGMT_METRICS_RAW 
		 where	METRIC_GUID = l_refConn_Metric_guid 	AND
			TARGET_GUID = l_lsnrTgt_guid		AND
			COLLECTION_TIMESTAMP >= l_cutoff_tstamp) refConns   
	WHERE 
		estConns.COLLECTION_TIMESTAMP = refConns.COLLECTION_TIMESTAMP   
	GROUP BY 
		TO_CHAR(estConns.COLLECTION_TIMESTAMP, 'MM/DD/YYYY') 
	ORDER BY 3;
   END IF;

END getLsnrPerfChartData;   

/*
Updates MGMT_DBNET_TNS_ADMINS
with the oracle homes and tns_admin locations for the specified Host Target.
*/
procedure updateNetAdminInfo(
      	hostName IN VARCHAR2,
        hostType IN VARCHAR2,
        fileSeparator IN VARCHAR2) IS
  homesCursor	      CURSORTYPE;
  lcount            NUMBER;
  oracle_home       MGMT_INV_CONTAINER.CONTAINER_location%TYPE;
  host_guid         MGMT_TARGETS.target_guid%TYPE;
BEGIN

  -- get all oracle homes for the host
    DECLARE
       CURSOR homesCursor IS
         select cont.CONTAINER_location as oracle_home, targets.TARGET_GUID as host_guid
                            from MGMT_INV_CONTAINER cont,mgmt_ecm_snapshot snap,MGMT_INV_COMPONENT comp , MGMT_TARGETS targets
                            where comp.is_top_level = 'Y' 
			    and comp.container_guid = cont.container_guid 
			    and snap.target_type=targets.target_type
                            and snap.target_name= targets.target_name
			    and snap.target_type=hostType
                            and snap.target_name= hostName
                            and snap.snapshot_guid=cont.snapshot_guid and cont.container_type='O';
	
	CURSOR deinstallOHCursor IS
		select HOST_GUID, ORACLE_HOME from  MGMT_DBNET_TNS_ADMINS
		 WHERE ORACLE_HOME not in (select cont.CONTAINER_location as oracle_home
                            from MGMT_INV_CONTAINER cont,mgmt_ecm_snapshot snap,MGMT_INV_COMPONENT comp 
                            where comp.is_top_level = 'Y' 
			    and comp.container_guid = cont.container_guid 
			    and snap.target_type=hostType
                            and snap.target_name= hostName
                            and snap.snapshot_guid=cont.snapshot_guid and cont.container_type='O')
		AND HOST_GUID=(select TARGET_GUID from MGMT_TARGETS targets
				where targets.target_type=hostType AND targets.target_name=hostName);

	BEGIN
		FOR deinstallOHRec IN deinstallOHCursor LOOP
	  	 DELETE from MGMT_DBNET_TNS_ADMINS 
	  	 WHERE HOST_GUID = deinstallOHRec.HOST_GUID
	 	 AND ORACLE_HOME = deinstallOHRec.ORACLE_HOME;
		END LOOP;
		commit;
       FOR homesRec IN homesCursor LOOP
            SELECT count(*) into lcount
                  FROM MGMT_DBNET_TNS_ADMINS
                  WHERE  HOST_GUID=homesRec.host_guid
                  AND    ORACLE_HOME= homesRec.oracle_home;
            IF lcount = 0 THEN
              INSERT INTO MGMT_DBNET_TNS_ADMINS 
                 ( HOST_GUID, ORACLE_HOME, TNS_ADMIN )
                VALUES (
                  homesRec.host_guid, 
                  homesRec.oracle_home, 
                    homesRec.oracle_home 
                      || fileSeparator || 'network'
                      || fileSeparator || 'admin');
             commit;
            END IF;
       END LOOP;
    END;
END updateNetAdminInfo;   

/*
Gets a target property
given the target name , target type and the property
*/
procedure getPropertyValue(
      	targetName IN VARCHAR2,
        targetType IN VARCHAR2,
        propertyName IN VARCHAR2,
        propertyValue OUT VARCHAR2) IS
BEGIN
        /*
        initialize OUT parameters
        */
        propertyValue := '';
    -- get the property value
        select p.property_value into propertyValue
        from mgmt_target_properties p, mgmt_targets t 
        where t.target_name=targetName and t.target_type=targetType and 
        t.target_guid = p.target_guid and p.property_name=propertyName;
END getPropertyValue;   

/*
Get the oracle homes and tns_admin locations for a Host Target after updating
Oracle Homes not yet present.
The values returned as OUT parameters are used for Oracle Net Services Administration
*/
procedure getNetAdminInfoWithUpdate(
	hostName IN VARCHAR2,
        hostType IN VARCHAR2,
        fileSeparator IN VARCHAR2,
        netAdmin_cur_out OUT cursorType) IS
BEGIN
        updateNetAdminInfo(hostName,hostType,fileSeparator);
        getNetAdminInfo(hostName,hostType,netAdmin_cur_out);
END getNetAdminInfoWithUpdate;   
   
/*
Get the oracle homes and tns_admin locations for a Host Target after updating
Oracle Homes not yet present.
The values returned as OUT parameters are used for Oracle Net Services Administration
*/
procedure getNetAdminInfoWithUpdateForSA(
	hostTargetName IN VARCHAR2,
	hostTargetType IN VARCHAR2,
        fileSeparator IN VARCHAR2,
        netAdmin_cur_out OUT cursorType) IS
   lcount            NUMBER;
   hostGuid    RAW(16);
   databasesCursor	      CURSORTYPE;
   remDatabasesCursor         CURSORTYPE;
BEGIN
DECLARE

       CURSOR databasesCursor IS
	select  p.property_value as oraHome from mgmt_targets t, mgmt_target_properties p 
	where t.target_type='oracle_database' 
	and t.host_name=hostTargetName
	and p.property_name='OracleHome'
	and p.target_guid = t.target_guid;

--For databases instances that are no longer part of the RAC
	CURSOR remDatabasesCursor IS
	select oracle_home as oraHome from MGMT_DBNET_TNS_ADMINS
	WHERE  ORACLE_HOME NOT IN  (select  p.property_value from mgmt_targets t, 
		mgmt_target_properties p 
		where t.target_type='oracle_database' 
		and t.host_name=hostTargetName
		and p.property_name='OracleHome'
		and p.target_guid = t.target_guid)
	AND HOST_GUID=(select t.target_guid from 
	mgmt_targets t where t.target_name=hostTargetName);
BEGIN
	
	--Get the host guid
	select t.target_guid into hostGuid from 
	mgmt_targets t where t.target_name=hostTargetName;
	--Delete stale data from MGMT_DBNET_TNS_ADMINS
	FOR remDatabaseRec IN remDatabasesCursor LOOP
		DELETE from MGMT_DBNET_TNS_ADMINS 
	  	WHERE HOST_GUID = hostGuid
	 	 AND ORACLE_HOME = remDatabaseRec.oraHome;
		END LOOP;
		commit;
		
	--Update the  MGMT_DBNET_TNS_ADMINS table if data not there
		FOR databaseRec IN databasesCursor LOOP
	 SELECT count(*) into lcount
                  FROM MGMT_DBNET_TNS_ADMINS where HOST_GUID=hostGuid
                  AND ORACLE_HOME= databaseRec.oraHome;
	IF lcount = 0 THEN
              INSERT INTO MGMT_DBNET_TNS_ADMINS 
                 ( HOST_GUID, ORACLE_HOME, TNS_ADMIN )
                VALUES (
                  hostGuid,
                  databaseRec.oraHome, 
                  databaseRec.oraHome 
                      || fileSeparator || 'network'
                      || fileSeparator || 'admin');
             commit;
            END IF;
	END LOOP;

	--Now get the data
	OPEN netAdmin_cur_out FOR
	select ORACLE_HOME, TNS_ADMIN  
	from MGMT_DBNET_TNS_ADMINS where HOST_GUID=hostGuid; 
END;
END getNetAdminInfoWithUpdateForSA;        

/*
Update MGMT_DBNET_TNS_ADMINS
with the oracle home and tns_admin location for the specified Host Target.
*/
procedure updateTNS_ADMIN(
      	hostName IN VARCHAR2,
        hostType IN VARCHAR2,
        oracleHome IN VARCHAR2, 
        tnsadmin  IN VARCHAR2) IS
  hostGuid   MGMT_TARGETS.target_guid%TYPE;
BEGIN
          hostGuid := '';        
          select unique TARGET_GUID into hostGuid
                            from MGMT_TARGETS 
                            where  target_type=hostType
                            and target_name= hostName;
         UPDATE MGMT_DBNET_TNS_ADMINS 
          SET TNS_ADMIN = tnsadmin
         WHERE HOST_GUID = hostGuid and ORACLE_HOME = oracleHome;
         commit;
END updateTNS_ADMIN;   

/*
Update MGMT_DBNET_TNS_ADMINS
with the oracle home and tns_admin location for the specified Host Target.
*/
procedure updateTNS_ADMIN_FOR_SA(
	hostName IN VARCHAR2,
        hostType IN VARCHAR2,
      	oracleHome IN VARCHAR2, 
        tnsadmin  IN VARCHAR2) IS
        hostGuid    RAW(16);
BEGIN  
	SELECT target_guid into hostGuid from mgmt_targets 
	where target_name=hostName and target_type=hostType;
    
         UPDATE MGMT_DBNET_TNS_ADMINS 
         SET TNS_ADMIN = tnsadmin
         WHERE HOST_GUID = hostGuid and ORACLE_HOME = oracleHome;
         commit;
END updateTNS_ADMIN_FOR_SA;  

--
-- PROCEDURE: get_lsnr_homepage_info
--
-- PURPOSE:
--   This procedure returns all the relevant information for a given listener
--   target name and target type.
--
PROCEDURE get_lsnr_homepage_info(target_name_in IN VARCHAR2,
                                 target_type_in IN VARCHAR2,
                                 type_display_name_out OUT VARCHAR2,
                                 last_load_time_out OUT VARCHAR2,
                                 current_status_out OUT INTEGER,
                                 avail_pct_out OUT NUMBER,
                                 severity_code_out OUT INTEGER,
                                 num_severity_out OUT INTEGER,
                                 host_name_out OUT VARCHAR2,
                                 host_display_name_out OUT VARCHAR2,
                                 tnsping_severity_out OUT INTEGER,
                                 start_timestamp_out OUT Date,
                                 metrics_cur_out OUT cursorType,
				 databases_cur_out OUT cursorType) IS

  l_target_guid mgmt_targets.target_guid%TYPE;

BEGIN

  -- target guid, type display name, last load time, host emd url
  BEGIN
    SELECT target_guid, type_display_name, TO_CHAR(last_load_time), host_name
      INTO l_target_guid, type_display_name_out, last_load_time_out, host_name_out
      FROM mgmt_targets
     WHERE target_name = target_name_in
       AND target_type = target_type_in;

    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        raise MGMT_GLOBAL.target_does_not_exist;
  END;

  -- host display name
  BEGIN
    SELECT unique display_name
      INTO host_display_name_out
      FROM mgmt_targets
     WHERE target_type = 'host'
       AND target_name = host_name_out;

    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        raise MGMT_GLOBAL.target_does_not_exist;

      WHEN TOO_MANY_ROWS
      THEN
        raise;
  END;

  -- current status
  current_status_out := mgmt_target.get_avail_current_status(l_target_guid);

  -- availability percentage
  avail_pct_out := ROUND(mgmt_target.get_avail_pct_value(l_target_guid, 1), 2);

  -- severity code
  severity_code_out := mgmt_target.get_target_max_severity(l_target_guid);

  -- total number of severities
  num_severity_out := mgmt_target.get_target_num_severity(l_target_guid);

  -- start timestamp of current status
  start_timestamp_out := mgmt_target.get_current_status_timestamp(l_target_guid);

  -- all metrics
  OPEN metrics_cur_out FOR
    SELECT metric_name, metric_column, ROUND(value, 2), string_value
      FROM mgmt_current_metrics cm, mgmt_metrics m
      WHERE cm.target_guid = l_target_guid
     AND   cm.metric_guid = m.metric_guid;

  -- TNS ping severity
  BEGIN
    SELECT unique severity_code
      INTO tnsping_severity_out
      FROM mgmt_current_severity cs, mgmt_metrics m
     WHERE cs.target_guid = l_target_guid
       AND cs.metric_guid = m.metric_guid
       AND metric_name = 'Response'
       AND metric_column = 'tnsPing';

    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        tnsping_severity_out := 15;
  END;

--	Get serviced database tab's information
	get_databases_info(target_name_in,target_type_in,databases_cur_out);

END get_lsnr_homepage_info;


--
-- PROCEDURE: get_lsnr_homepage_info
--
-- PURPOSE:
--   This procedure returns all the relevant information for a given listener home page
--   target name and target type. For performance.
--
PROCEDURE get_lsnr_home_page_data(target_name_in IN VARCHAR2,
                                 target_type_in IN VARCHAR2,
                                 type_display_name_out OUT VARCHAR2,
                                 last_load_time_out OUT VARCHAR2,
                                 current_status_out OUT INTEGER,
                                 avail_pct_out OUT NUMBER,
                                 severity_code_out OUT INTEGER,
                                 num_severity_out OUT INTEGER,
                                 host_name_out OUT VARCHAR2,
                                 host_display_name_out OUT VARCHAR2,
                                 tnsping_severity_out OUT INTEGER,
                                 start_timestamp_out OUT Date,
                                 metrics_cur_out OUT cursorType) IS

  l_target_guid mgmt_targets.target_guid%TYPE;

BEGIN

  -- target guid, type display name, last load time, host emd url
  BEGIN
    SELECT target_guid, type_display_name, TO_CHAR(last_load_time), host_name
      INTO l_target_guid, type_display_name_out, last_load_time_out, host_name_out
      FROM mgmt_targets
     WHERE target_name = target_name_in
       AND target_type = target_type_in;

    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        raise MGMT_GLOBAL.target_does_not_exist;
  END;

  -- host display name
  BEGIN
    SELECT unique display_name
      INTO host_display_name_out
      FROM mgmt_targets
     WHERE target_type = 'host'
       AND target_name = host_name_out;

    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        raise MGMT_GLOBAL.target_does_not_exist;

      WHEN TOO_MANY_ROWS
      THEN
        raise;
  END;

  -- current status
  current_status_out := mgmt_target.get_avail_current_status(l_target_guid);

  -- availability percentage
  avail_pct_out := ROUND(mgmt_target.get_avail_pct_value(l_target_guid, 1), 2);

  -- severity code
  severity_code_out := mgmt_target.get_target_max_severity(l_target_guid);

  -- total number of severities
  num_severity_out := mgmt_target.get_target_num_severity(l_target_guid);

  -- start timestamp of current status
  start_timestamp_out := mgmt_target.get_current_status_timestamp(l_target_guid);

  -- all metrics
  OPEN metrics_cur_out FOR
    SELECT metric_name, metric_column, ROUND(value, 2), string_value
      FROM mgmt_current_metrics cm, mgmt_metrics m
      WHERE cm.target_guid = l_target_guid
     AND   cm.metric_guid = m.metric_guid;

  -- TNS ping severity
  BEGIN
    SELECT unique severity_code
      INTO tnsping_severity_out
      FROM mgmt_current_severity cs, mgmt_metrics m
     WHERE cs.target_guid = l_target_guid
       AND cs.metric_guid = m.metric_guid
       AND metric_name = 'Response'
       AND metric_column = 'tnsPing';

    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        tnsping_severity_out := 15;
  END;


END get_lsnr_home_page_data;

--
-- PROCEDURE: get_databases_info
--
-- PURPOSE:
--   This procedure returns a cursor containing all databases information
--   for the given listener.
--
PROCEDURE get_databases_info(target_name_in IN VARCHAR2,
                             target_type_in IN VARCHAR2,
			     databases_cur_out OUT cursorType)
IS
  l_target_guid mgmt_targets.target_guid%TYPE;
  l_host_name VARCHAR2(1024);

  l_list VARCHAR2(4000);
  l_port_number VARCHAR2(1024);
  l_sid VARCHAR2(1024);
  l_curr_index INTEGER := 0;
  l_db_sids SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();
  l_db_guids MGMT_GUID_ARRAY := MGMT_GUID_ARRAY();

BEGIN

  -- target guid, host emd url
  BEGIN
    SELECT target_guid, host_name
      INTO l_target_guid, l_host_name
      FROM mgmt_targets
     WHERE target_name = target_name_in
       AND target_type = target_type_in;

    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        raise MGMT_GLOBAL.target_does_not_exist;
  END;


  -- find the sidList
  BEGIN
    SELECT unique string_value
      INTO l_list
      FROM mgmt_current_metrics cm, mgmt_metrics m
     WHERE cm.target_guid = l_target_guid
       AND cm.metric_guid = m.metric_guid
       AND metric_name = 'General Status'
       AND metric_column = 'sidList';

    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        l_list := ' ';

      WHEN TOO_MANY_ROWS
      THEN
        RAISE;
  END;

  -- find the port number
  BEGIN
    SELECT DISTINCT(property_value)
      INTO l_port_number
      FROM mgmt_target_properties
     WHERE target_guid = l_target_guid
       AND property_name = 'Port';

    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        l_port_number := NULL;

      WHEN TOO_MANY_ROWS
      THEN
        RAISE;
  END;

  IF(l_list != ' ' AND l_port_number IS NOT NULL AND l_host_name IS NOT NULL) THEN

    -- parse sid list and store sids
    WHILE(l_list != ' ') LOOP
      IF(INSTR(l_list, ',') != 0) THEN
        l_sid := SUBSTR(l_list, 1, INSTR(l_list, ',') - 1);
        l_list := SUBSTR(l_list, INSTR(l_list, ',') + 1, LENGTH(l_list) );
      ELSE
        l_sid := l_list;
        l_list := ' ';
      END IF;

      l_db_sids.extend;
      l_curr_index := l_curr_index + 1;
      l_db_sids(l_curr_index) := SMP_EMD_NVPAIR(l_sid, '');
    END LOOP;

    -- find all databases on the same host as the listener, with the ports of the 
    -- listener's port and the sids in the listener's sid list
    SELECT MGMT_GUID_OBJ(prop.target_guid, '') BULK COLLECT INTO l_db_guids
      FROM mgmt_target_properties prop,
           (SELECT tgt.target_guid
              FROM mgmt_targets tgt, mgmt_target_properties p
             WHERE tgt.target_type = 'oracle_database'
               AND tgt.host_name = l_host_name
               AND tgt.target_guid = p.target_guid
               AND p.property_name = 'Port'
               AND p.property_value = l_port_number) guids,
           TABLE(CAST(l_db_sids AS SMP_EMD_NVPAIR_ARRAY) ) sids
     WHERE prop.target_guid = guids.target_guid
       AND prop.property_name = 'SID'
       AND prop.property_value = sids.name;

    OPEN databases_cur_out FOR
        SELECT tgt.target_name, 
               tgt.target_type, 
               tgt.display_name, 
               tgt.type_display_name, 
               15 AS severity_code,
               ROUND(mgmt_target.get_avail_pct_value(tgt.target_guid, 1), 0) AS avail_pct,
               mgmt_target.get_avail_current_status(tgt.target_guid) AS current_status,
               NVL(sev_detail.warning_count, 0) AS warning_count,
               NVL(sev_detail.critical_count, 0) AS critical_count,
               tgt.target_guid
          FROM mgmt_targets tgt,
               (  SELECT target_guid, 
                         SUM(DECODE(severity_code, MGMT_GLOBAL.G_SEVERITY_WARNING, 1, 0) ) AS warning_count,
                         SUM(DECODE(severity_code, MGMT_GLOBAL.G_SEVERITY_CRITICAL, 1, 0) ) AS critical_count
                    FROM mgmt_current_severity
                GROUP BY target_guid ) sev_detail,
               TABLE(CAST(l_db_guids AS MGMT_GUID_ARRAY) ) db_guids
         WHERE tgt.target_guid = sev_detail.target_guid (+)
           AND tgt.target_guid = db_guids.guid
      ORDER BY tgt.type_display_name, tgt.target_name;

  ELSE
    -- open a dummy empty cursor
    OPEN databases_cur_out FOR
        SELECT * 
          FROM dual
		 WHERE rownum < 1;
  
  END IF;

END get_databases_info;


end MGMT_DBNET;
/
show errors;