REM drv: <create type="pkgbodys"/>
Rem
Rem $Header: pa_pkgbody.sql 14-dec-2006.08:05:46 qsong Exp $
Rem
Rem pa_pkgbody.sql
Rem
Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      pa_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    milshah     02/21/06 - Get DB Sids being serviced by ASM 
Rem    mningomb    11/03/05 - Accept interview_type as a parameter 
Rem    spahuja     08/24/05 - spahuja_creds
Rem    spahuja     08/17/05 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100


CREATE OR REPLACE PACKAGE BODY PA_PKG
IS

--
-- Function: get_home_dir
--           The function gets the home location for the selected targets from the mgmt_target_propperties table.
--           It takes an array of target names and returns a REF_CURSOR which
--           contains the correspoinding homes 
-- ---------   ------  ------------------------------------------

FUNCTION GET_HOME_DIR(
         p_target_names in VARCHAR2_TABLE,
         p_interview_type in VARCHAR2)
RETURN OUTPUT_REF_CURSOR
IS

p_row_index number := 0;
p_row_count number := 0;
p_home_dir mgmt_target_properties.property_value%TYPE;
p_prop_name mgmt_target_properties.property_name%TYPE := 'OracleHome';
-- this is varchar2 (1024) type

p_home_dir_cur OUTPUT_REF_CURSOR;

p_all_home_dirs VARCHAR2_TABLE := VARCHAR2_TABLE();

cursor home_dir_cur(p_target_name in varchar2, p_home_prop_name in varchar2) is
  select tp.property_value
  from mgmt_target_properties tp, mgmt_targets tgt
  where 
  tp.property_name = p_home_prop_name 
  and tgt.target_name = p_target_name
  and tgt.target_guid = tp.target_guid;

BEGIN
 if p_interview_type is not null AND p_interview_type = 'cluster' then
   p_prop_name := 'CRSHome';
 end if;
   
  p_row_count := p_target_names.count;
  FOR p_row_index in 1..p_row_count LOOP
 
   p_home_dir := NULL;

    OPEN home_dir_cur(p_target_names(p_row_index),p_prop_name);
    LOOP
      FETCH home_dir_cur INTO p_home_dir;
      EXIT when home_dir_cur%NOTFOUND;
    END LOOP;
    CLOSE home_dir_cur;

    p_all_home_dirs.EXTEND;
    p_all_home_dirs(p_row_index) := p_home_dir;
  END LOOP;

  OPEN p_home_dir_cur FOR
  SELECT *
  FROM
  TABLE (CAST(p_all_home_dirs AS VARCHAR2_TABLE));
  
  RETURN p_home_dir_cur;

END;


-- Function: get_db_sids_for_asm
-- This function takes an ASM instance name and returns an ampersand 
-- seperated list of db guids which are being serviced by the ASM instance. 

FUNCTION GET_DB_GUIDS_FOR_ASM(
        p_osm_instance MGMT_TARGETS.TARGET_NAME%TYPE)
RETURN VARCHAR2
IS

  struct_obj SMP_EMD_OSMSITEMAP_OBJ;
  target_guid varchar2(32);
  meta_ver    varchar2(8);
  cat_prop_1  varchar2(64);
  cat_prop_2  varchar2(64);
  cat_prop_3  varchar2(64);
  cat_prop_4  varchar2(64);
  cat_prop_5  varchar2(64);

  c1 mgmt_target.cursor_type;
  c2 mgmt_target.cursor_type;
  c3 mgmt_target.cursor_type;

  db_target_name  varchar2(200);
  db_target_type  varchar2(200);
  db_display_name  varchar2(200); 
  db_type_display_name  varchar2(200);
  targetSev number;
  targetAvail number;
  targetStatus number;
  warningAlert number;
  criticalAlert number;
  db_target_guid varchar2(200);
  db_Name varchar2(200);

  /* Return Parameter */
  l_db_sid MGMT_TARGET_PROPERTIES.PROPERTY_VALUE%TYPE;
  l_asm_db_guids varchar2(4096);
  l_index int;

BEGIN

  /* Initialize */
  l_db_sid := '';
  l_asm_db_guids := '';
  l_index := 1;

  emd_osm.getOsmSiteMapInfo(p_osm_instance,'osm_instance',struct_obj);
  target_guid := struct_obj.target_guid;
  meta_ver    := struct_obj.meta_ver;
  cat_prop_1  := struct_obj.CAT_PROP_1;
  cat_prop_2  := struct_obj.CAT_PROP_2;
  cat_prop_3  := struct_obj.CAT_PROP_3;
  cat_prop_4  := struct_obj.CAT_PROP_4;
  cat_prop_5  := struct_obj.CAT_PROP_5;

  emd_osm.getDatabasesInfo(target_guid, meta_ver, cat_prop_1,cat_prop_2,cat_prop_3,cat_prop_4,cat_prop_5,c1,c2,c3);
 
  loop

    FETCH c1 INTO db_target_name, db_target_type, db_display_name, db_type_display_name, targetSev, targetAvail, targetStatus, warningAlert, criticalAlert, db_target_guid, db_Name;
    EXIT WHEN c1%NOTFOUND;
  
    /* Get the db sid */
    /* SELECT property_value
    INTO l_db_sid
    FROM mgmt_target_properties
    WHERE target_guid=db_target_guid and property_name='SID';*/
 
    /* Add the db sid to the db sid list */
    /* IF l_index = 1
    THEN
      l_asm_db_guids := l_db_sid;
    ELSE
      l_asm_db_sids := l_asm_db_sids || '&' || l_db_sid;
    END IF;*/

    IF l_index = 1
    THEN
      l_asm_db_guids := db_target_guid;
    ELSE
      l_asm_db_guids := l_asm_db_guids || '&' || db_target_guid;
    END IF;

    /* Increment the index */
    l_index := l_index + 1;   

  end loop;
  close c1;

  /* return ampersand seperated sids */
  /* return l_asm_db_sids; */
  return l_asm_db_guids;

END;

END;
/

show errors