Rem Rem $Header: storage_ui_host_pkgbody.sql 04-may-2007.08:14:38 yozhang Exp $ Rem Rem storage_ui_host_pkgbody.sql Rem Rem Copyright (c) 2004, 2007, Oracle. All rights reserved. Rem Rem NAME Rem storage_ui_host_pkgbody.sql Rem Rem DESCRIPTION Rem package for retrieving host storage ui data Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem yozhang 05/04/07 - Fix backport bug: 6029650 Rem mnihalan 08/18/06 - fix backport 5475298 Rem sreddy 07/29/05 - fix cartesian product in Rem get_info_for_storage_refresh Rem sreddy 07/23/05 - Use mgmt_storage_report_ui_targets.ecm_snapshot_id Rem in queries to restrict data from mgmt_v_host_* Rem views to requested {target_name, target_type}. Rem sreddy 07/23/05 - Fix contant names to match Rem latest storage_ui_common_pkg definition Rem sreddy 07/23/05 - Fix get_host_disks_storage() to populate Rem mgmt_storage_reports_ui_targets first, else Rem we can get stale or no data. Rem sreddy 07/23/05 - Fix get_info_for_storage_refresh() to restrict Rem oracle_database targets to those not having Rem rac_database association. It is unnecessary Rem to refresh snapshot for db targets with rac Rem association. Rem sreddy 07/23/05 - Remove get_disk_resource_types() - it's dead code Rem sreddy 07/23/05 - Remove dead code from get_host_db_storage() Rem sreddy 07/23/05 - populate mgmt_storage_reports_ui_targets first Rem in get_host_asm_storage(), rather than at end. Rem sreddy 07/23/05 - Remove unnecessary parameters from Rem get_disks_storage_data() definition Rem rmenon 05/05/05 - fix for bug 4350833 Rem rmenon 02/21/05 - Rem rmenon 02/16/05 - added timezone retrieval (bug 4186450) Rem rmenon 02/08/05 - workaround against PL/SQL bug # Rem wherein if you fetch Rem from a refcursor returned by a method Rem which itself gets it from another method Rem you get "ORA-06504: PL/SQL: Rem Return types of Result Set Rem variables or query do not match" Rem rmenon 02/03/05 - Rem rmenon 12/16/04 - invoke procedure to insert Rem target info in the procedure Rem get_info_for_storage_refresh. Rem rmenon 11/29/04 - Rem rmenon 11/03/04 - consolidated storage error logic Rem rmenon 10/13/04 - renamed file/package name. Rem rmenon 09/08/04 - history code major cleanup in terms Rem of views refactoring. Rem rmenon 08/26/04 - Rem rmenon 08/18/04 - hosts sharing resources UI; code Rem cleanup - revamped comment style Rem wherever possible. Rem rmenon 08/16/04 - refresh functionality; Rem errors page functionality Rem host storage history functionality; Rem code cleanup Rem ajdsouza 08/09/04 - Rem rmenon 08/02/04 - rmenon_storage_refresh_error_page_checkin Rem rmenon 08/02/04 - created new file in storage component; Rem added code for refresh logic; Rem integration of host detail page Rem with real data; error page. Rem rmenon 06/30/04 - Rem rmenon 06/28/04 - rmenon_storage_disks_tab_ui Rem rmenon 06/10/04 - Created Rem DEFINE DB_TARGET_TYPE = oracle_database DEFINE HOST_TARGET_TYPE = host DEFINE RAC_DB_TARGET_TYPE = rac_database create or replace package body storage_ui_host_pkg as /*-------------------------------------------------------------*/ /*--- forward declaration of private procedures/functions -----*/ /*-------------------------------------------------------------*/ FUNCTION get_disks_storage_data (p_resource_type IN VARCHAR2) RETURN SYS_REFCURSOR; PROCEDURE get_vol_storage_data ( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2 DEFAULT mgmt_global.G_HOST_TARGET_TYPE, p_resource_type IN VARCHAR2, p_prod_vendor_comb_count IN OUT number, p_prod_vendor_combination IN OUT SYS_REFCURSOR, p_vol_list_cursor IN OUT SYS_REFCURSOR ); FUNCTION get_resource_types ( p_storage_layer IN VARCHAR2 ) RETURN SYS_REFCURSOR; /*-------------------------------------------------------------*/ /*---------- definition of public procedures/functions --------*/ /*-------------------------------------------------------------*/ /*------------------------------------------------------ Name: get_info_for_storage_refresh Package: storage_ui_host_pkg Purpose: gets the information needed to complete an on-demand refresh of the storage data of a host. IN Parameters: p_target_name - the host name p_target_type - the target type. we pass it only in the hope that the same function could perhaps be used for non-host target types OUT Parameters: p_info_for_storage_refresh - ref cursor containing information required to refresh host storage data. -------------------------------------------------------*/ PROCEDURE get_info_for_storage_refresh ( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2 DEFAULT mgmt_global.G_HOST_TARGET_TYPE, p_info_for_storage_refresh IN OUT SYS_REFCURSOR ) is BEGIN OPEN p_info_for_storage_refresh for /* The order column is to make sure that we select associated targets on the host first. This is because this list is used by the middle tier to refresh host storage snapshots and the storage snapshots of databases on a host should be refreshed before the host non-db storage data since the later depends on the former for calculations. */ SELECT host_name, target_name, target_type, emd_url from ( SELECT 1 order_col, host_name, target_name, target_type, emd_url FROM mgmt_targets WHERE host_name = p_target_name AND target_type in (MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE, MGMT_GLOBAL.G_ASM_TARGET_TYPE) union all SELECT 2 order_col, host_name, target_name, target_type, emd_url FROM mgmt_targets t WHERE host_name = p_target_name AND target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE AND NOT EXISTS (SELECT member_target_guid FROM MGMT_TARGET_MEMBERSHIPS WHERE member_target_guid = t.target_guid AND composite_target_type = MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE) union all SELECT 3 order_col, p_target_name host_name, target_name, target_type, emd_url FROM mgmt_targets WHERE target_name = p_target_name and target_type = mgmt_global.G_HOST_TARGET_TYPE ); END; /*------------------------------------------------------ Name: get_host_disks_storage Package: storage_ui_host_pkg Purpose: gets storage data to render the storage information for the local disks on a host. IN Parameters: p_target_name - the target name (host name) p_target_type - the target type. we pass it only in the hope that the same function could perhaps be used for non-host target types p_resource_type - resource type(e.g. 'Disk', 'Partition' etc.) For selecting all resources set this to '%'. OUT Parameters: p_disks_storage_data - storage data of disks on the host p_resource_types - distinct resource types for disks - e.g. 'Disk', 'Partition' etc. Implementation Notes: -------------------------------------------------------*/ PROCEDURE get_host_disks_storage ( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2 DEFAULT mgmt_global.G_HOST_TARGET_TYPE, p_resource_type IN VARCHAR2, p_disks_storage_data IN OUT SYS_REFCURSOR, p_resource_types IN OUT SYS_REFCURSOR ) is BEGIN storage_ui_common_pkg.insert_targets_for_ui_queries ( p_target_name => p_target_name, p_target_type => p_target_type ); p_disks_storage_data := get_disks_storage_data (p_resource_type => p_resource_type); p_resource_types := get_resource_types ( p_storage_layer => storage_ui_common_pkg.G_OS_DISK ); END; ---------------------------------------------------------- -- Name: get_host_volumes_storage -- -- Package: storage_ui_host_pkg -- -- Purpose: -- gets storage data to render the storage information for -- the volumes on a host. -- -- IN Parameters: -- p_target_name - the target name (host name) -- p_target_type - the target type. -- p_resource_type - resource type(e.g. 'Disk', 'Partition' -- etc.) For selecting all resources set this to '%'. -- OUT Parameters: -- p_prod_vendor_comb_count - number of rows that have distinct -- vendor and product combinations -- p_prod_vendor_combination - number of distinct product and -- vendor combinations. This info -- is used to render the UI in -- different ways if there is only -- one vendor product versus many. -- p_vol_storage_data - storage data of volumes on the host -- p_resource_types - distinct resource types for volumes - -- e.g. 'Volume', 'Plex' etc. --------------------------------------------------------- PROCEDURE get_host_volumes_storage ( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2 DEFAULT mgmt_global.G_HOST_TARGET_TYPE, p_resource_type IN VARCHAR2, p_prod_vendor_comb_count IN OUT number, p_prod_vendor_combination IN OUT SYS_REFCURSOR, p_vol_storage_data IN OUT SYS_REFCURSOR, p_resource_types IN OUT SYS_REFCURSOR ) is l_prod_vendor_combination int; BEGIN storage_ui_common_pkg.insert_targets_for_ui_queries ( p_target_name => p_target_name, p_target_type => p_target_type ); OPEN p_prod_vendor_combination for SELECT distinct vendor, product FROM mgmt_v_host_vol_list v, mgmt_storage_report_ui_targets uit WHERE uit.ecm_snapshot_id = v.ecm_snapshot_id AND resource_type like p_resource_type; SELECT count(*) into p_prod_vendor_comb_count from ( SELECT distinct vendor, product FROM mgmt_v_host_vol_list v, mgmt_storage_report_ui_targets uit WHERE uit.ecm_snapshot_id = v.ecm_snapshot_id AND resource_type like p_resource_type ); IF( p_prod_vendor_comb_count > 1 ) then OPEN p_vol_storage_data for SELECT resource_name, resource_type, disk_group, os_path, vendor, product, shared_count, storage_ui_util_pkg.convert_into_storage_unit( storage_size, storage_ui_util_pkg.GB) storage_size, storage_ui_util_pkg.convert_into_storage_unit( allocated, storage_ui_util_pkg.GB) allocated, storage_ui_util_pkg.convert_into_storage_unit( unallocated, storage_ui_util_pkg.GB) unallocated, configuration, global_unique_id FROM mgmt_v_host_vol_list v, mgmt_storage_report_ui_targets uit WHERE uit.ecm_snapshot_id = v.ecm_snapshot_id AND resource_type like p_resource_type; else OPEN p_vol_storage_data for SELECT resource_name, resource_type, disk_group, os_path, shared_count, storage_ui_util_pkg.convert_into_storage_unit( storage_size, storage_ui_util_pkg.GB) storage_size, storage_ui_util_pkg.convert_into_storage_unit( allocated, storage_ui_util_pkg.GB) allocated, storage_ui_util_pkg.convert_into_storage_unit( unallocated, storage_ui_util_pkg.GB) unallocated, configuration, global_unique_id FROM mgmt_v_host_vol_list v, mgmt_storage_report_ui_targets uit WHERE uit.ecm_snapshot_id = v.ecm_snapshot_id AND resource_type like p_resource_type; END IF; /* Due to PL/SQL bug 4173732 I had to comment out this PROCEDURE and copy the code directly above. get_vol_storage_data ( p_target_name => p_target_name, p_target_type => p_target_type, p_resource_type => p_resource_type, p_prod_vendor_comb_count => p_prod_vendor_comb_count, p_prod_vendor_combination => p_prod_vendor_combination, p_vol_list_cursor => p_vol_storage_data ); */ p_resource_types := get_resource_types ( p_Storage_layer => storage_ui_common_pkg.G_VOLUME_MANAGER ); END; ---------------------------------------------------------- -- Name: get_host_fs_storage -- -- Package: storage_ui_host_pkg -- -- Purpose: -- gets storage details of file systems (local and network) -- on a host. -- -- IN Parameters: -- p_target_name - the target name -- p_target_type - the target type. -- p_resource_type - resource type(e.g. 'ufs', 'nfs' etc.) -- For selecting all resources set this to '%'. -- -- OUT Parameters: -- p_fs_storage_data - storage data of fs on the host -- p_resource_types - distinct resource types for fs - -- e.g. 'ufs', 'nfs' etc. --------------------------------------------------------- PROCEDURE get_host_fs_storage ( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2 DEFAULT mgmt_global.G_HOST_TARGET_TYPE, p_resource_type IN VARCHAR2, p_fs_storage_data IN OUT SYS_REFCURSOR, p_resource_types IN OUT SYS_REFCURSOR ) is BEGIN storage_ui_common_pkg.insert_targets_for_ui_queries ( p_target_name => p_target_name, p_target_type => p_target_type ); OPEN p_fs_storage_data for SELECT resource_name, resource_type, mount_point, storage_ui_util_pkg.convert_into_storage_unit( storage_size, storage_ui_util_pkg.GB) storage_size, storage_ui_util_pkg.convert_into_storage_unit( used, storage_ui_util_pkg.GB) used, shared_count, writeable, global_unique_id FROM mgmt_v_host_fs_list fs, mgmt_storage_report_ui_targets uit WHERE uit.ecm_snapshot_id = fs.ecm_snapshot_id AND resource_type like p_resource_type; OPEN p_resource_types for SELECT distinct resource_type FROM mgmt_v_host_fs_list fs, mgmt_storage_report_ui_targets uit WHERE uit.ecm_snapshot_id = fs.ecm_snapshot_id; END get_host_fs_storage; ---------------------------------------------------------- -- Name: get_host_db_storage -- -- Package: storage_ui_host_pkg -- -- Purpose: -- gets storage details of databases (non rac and rac) -- on a host. -- -- IN Parameters: -- p_target_name - the target name -- p_target_type - the target type. -- p_storage_unit - the storage unit -- -- OUT Parameters: -- p_nonrac_db_count - count of how many non rac -- dbs exist on the host. Based on this -- and p_rac_db_count, one of the three -- cursors is populated. -- p_rac_db_count - count of how many rac -- dbs exist on the host. Based on this -- count and p_nonrac_db_count, one of the -- three cursors is populated. -- p_db_storage_data - This cursor contains one of the following: -- * returns less information if one type of -- database(s) exist -- * returns more information if both types of -- databases exist --------------------------------------------------------- PROCEDURE get_host_db_storage ( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2 DEFAULT mgmt_global.G_HOST_TARGET_TYPE, p_storage_unit IN VARCHAR2, p_nonrac_db_count IN OUT int, p_rac_db_count IN OUT int, p_db_storage_data IN OUT SYS_REFCURSOR ) is l_db_target_type mgmt_targets.target_type%type; BEGIN IF( emdw_log.P_IS_INFO_SET ) then emdw_log.info( 'get_host_db_storage(s):Enter', G_MODULE_NAME ); END IF; storage_ui_common_pkg.insert_targets_for_ui_queries ( p_target_name => p_target_name, p_target_type => p_target_type ); SELECT sum(decode( target_type, mgmt_global.G_DATABASE_TARGET_TYPE, 1, 0) ) nonrac_db_count, sum(decode( target_type, mgmt_global.G_RAC_DATABASE_TARGET_TYPE, 1, 0) ) rac_db_count into p_nonrac_db_count, p_rac_db_count FROM mgmt_v_host_dbs; IF( emdw_log.P_IS_DEBUG_SET ) then emdw_log.debug( 'get_host_db_storage(s): ' || 'number of rac databases : '|| p_rac_db_count ||',' || 'number of non RAC databases: '|| p_nonrac_db_count, G_MODULE_NAME ); END IF; p_nonrac_db_count := nvl( p_nonrac_db_count, 0 ); p_rac_db_count := nvl( p_rac_db_count, 0 ); IF( p_nonrac_db_count > 0 and p_rac_db_count > 0 ) then -- both rac and non rac databases exist IF( emdw_log.P_IS_DEBUG_SET ) then emdw_log.debug( 'get_host_db_storage(s): ' || 'case 1: both RAC and non RAC databases exist', G_MODULE_NAME ); END IF; OPEN p_db_storage_data for SELECT a.target_name, b.type_display_name, a.allocated as storage_size, a.used, a.target_type from ( SELECT target_type, target_name, storage_ui_util_pkg.convert_into_storage_unit( sum( allocated ), p_storage_unit ) allocated, storage_ui_util_pkg.convert_into_storage_unit( sum( used ), p_storage_unit ) used FROM mgmt_v_db_alloc_used group by target_type, target_name )a, mgmt_target_types b WHERE a.target_type = b.target_type; else OPEN p_db_storage_data for SELECT target_name, storage_ui_util_pkg.convert_into_storage_unit( sum( allocated ), p_storage_unit ) as storage_size, storage_ui_util_pkg.convert_into_storage_unit( sum( used ), p_storage_unit ) as used FROM mgmt_v_db_alloc_used group by target_name; END IF; END get_host_db_storage; ---------------------------------------------------------- -- Name: get_host_asm_storage -- -- Package: storage_ui_host_pkg -- -- Purpose: -- gets storage details of ASM on a host. -- -- IN Parameters: -- p_target_name - the target name -- p_target_type - the target type. -- p_storage_unit - the storage unit -- -- OUT Parameters: -- p_asm_storage_data - cursor containing asm storage -- data. --------------------------------------------------------- PROCEDURE get_host_asm_storage ( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2 DEFAULT mgmt_global.G_HOST_TARGET_TYPE, p_storage_unit IN VARCHAR2, p_asm_storage_data IN OUT SYS_REFCURSOR ) is BEGIN IF( emdw_log.P_IS_INFO_SET ) then emdw_log.info( 'get_host_asm_storage(s):Enter', G_MODULE_NAME ); END IF; storage_ui_common_pkg.insert_targets_for_ui_queries ( p_target_name => p_target_name, p_target_type => p_target_type ); OPEN p_asm_storage_data for SELECT a.target_name, storage_ui_util_pkg.convert_into_storage_unit( asm_size, p_storage_unit ) as storage_size, storage_ui_util_pkg.convert_into_storage_unit( asm_allocated, p_storage_unit ) as allocated, storage_ui_util_pkg.convert_into_storage_unit( asm_unallocated, p_storage_unit ) as unallocated, storage_ui_util_pkg.convert_into_storage_unit( (asm_size-(asm_allocated+asm_unallocated)), p_storage_unit ) as overhead from ( SELECT dgu.target_name, (sum( dgu.total_mb ))*1024*1024 as asm_size, (sum( dgu.usable_file_mb ))*1024*1024 as asm_unallocated FROM mgmt_v_asm_dg_usage dgu group by dgu.target_name ) a, ( SELECT dba.target_name, (sum( dba.total_mb )) as asm_allocated FROM mgmt_v_asm_db_dg_usage dba group by dba.target_name ) b WHERE a.target_name = b.target_name; IF( emdw_log.P_IS_INFO_SET ) then emdw_log.info( 'get_host_asm_storage(s):Exit Normal', G_MODULE_NAME ); END IF; END get_host_asm_storage; -------------------------------------------------------- -- Name: get_hosts_sharing_resource -- -- Package: storage_ui_host_pkg -- -- Purpose: -- gets list of hosts sharing a given resource. -- The resource could be disks, volumes etc. -- -- IN Parameters: -- p_target_name - the host name -- p_target_type - the target type. we pass it only in the -- hope that the same function could perhaps be used -- for non-host target types -- p_global_unique_id - the global unique id of the -- resource. -- -- OUT Parameters: -- p_target_timezone_region - target timezone region -- p_collection_timestamp - latest collection timestamp -- of history data. -- p_hosts_sharing_resource - list of hosts sharing -- -- Implementation Notes: ----------------------------------------------------------- PROCEDURE get_hosts_sharing_resource ( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2 DEFAULT mgmt_global.G_HOST_TARGET_TYPE, p_global_unique_id IN VARCHAR2, p_target_timezone_region IN OUT VARCHAR2, p_collection_timestamp IN OUT date, p_hosts_sharing_resource IN OUT SYS_REFCURSOR ) is BEGIN -- get target time zone region info - used in UI for -- displaying timestamps p_target_timezone_region := storage_ui_common_pkg.get_target_timezone_region ( p_target_name => p_target_name, p_target_type => p_target_type ); storage_ui_common_pkg.insert_targets_for_ui_queries ( p_target_name => p_target_name, p_target_type => p_target_type ); p_collection_timestamp := storage_ui_common_pkg.get_collection_timestamp ( p_target_name => p_target_name, p_target_type => p_target_type ); OPEN p_hosts_sharing_resource for SELECT distinct target_name FROM mgmt_v_storage_report_data WHERE global_unique_id = p_global_unique_id; END get_hosts_sharing_resource; /*-------------------------------------------------------------*/ /*--------- definition of private procedures/functions --------*/ /*-------------------------------------------------------------*/ -------------------------------------------------------- -- Name: get_disks_storage_data -- -- Package: storage_ui_host_pkg -- -- Purpose: -- gets storage data to render the storage information for -- the local disks on a host. -- -- IN Parameters: -- p_target_name - the target name (host name) -- p_target_type - the target type. we pass it only in the -- hope that the same function could perhaps be used -- for non-host target types -- p_resource_type - resource type(e.g. 'Disk', 'Partition' -- etc.) For selecting all resources set this to '%'. -- -- Returns: -- ------- -- a ref cursor containing relevant data -- -- Implementation Notes: -- --------------------------------------------------------- FUNCTION get_disks_storage_data ( p_resource_type IN VARCHAR2 ) RETURN SYS_REFCURSOR is l_result_cursor SYS_REFCURSOR; BEGIN /* It is easiest to understand the select statement if you start from the innermost select and work your way out. */ OPEN l_result_cursor for /* carry over the storage data - and use the storage unit to convert the storage data into appropriate unit for display in UI. */ SELECT resource_name, resource_type, device_type, vendor, shared_count, storage_ui_util_pkg.convert_into_storage_unit( storage_size, storage_ui_unit) storage_size, storage_ui_util_pkg.convert_into_storage_unit( allocated, storage_ui_unit) allocated, storage_ui_util_pkg.convert_into_storage_unit( unallocated, storage_ui_unit) unallocated, storage_ui_unit disks_storage_ui_unit, global_unique_id from ( /* carry over the storage data - use the data gotten so far to calculate the storage unit to be used. Note that the scalar subquery is used to select the storage unit so that the function get_ui_storage_unit() is executed only once since its inputs are invariants. If you directly select the storage unit by executing the funtion outside the scalar subquery as in: select used, free, unallocated, overhead, storage_ui_util_pkg.get_ui_storage_unit( min_all, max_all) storage_ui_unit ... the function will be executed for every row in the query. */ SELECT resource_name, resource_type, device_type, vendor, shared_count, storage_size, allocated, unallocated, ( SELECT storage_ui_util_pkg.get_ui_storage_unit( min_all, max_all) from dual ) storage_ui_unit, global_unique_id from ( /* get the storage data - along with the minimum and maximums of each of the storage data numbers. */ SELECT resource_name, resource_type, device_type, vendor, shared_count, storage_size, allocated, unallocated, max(greatest(allocated, unallocated)) over() max_all, min(least(allocated, unallocated)) over() min_all, global_unique_id FROM mgmt_v_host_disk_list dl, mgmt_storage_report_ui_targets uit WHERE uit.ecm_snapshot_id = dl.ecm_snapshot_id AND resource_type like p_resource_type ) ); RETURN l_result_cursor; END get_disks_storage_data; -------------------------------------------------------- -- Name: get_vol_storage_data -- -- Package: storage_ui_host_pkg -- -- Purpose: -- gets storage data to render the storage information for -- the volumes on a host. -- -- IN Parameters: -- p_target_name - the target name (host name) -- p_target_type - the target type. -- p_resource_type - resource type(e.g. 'Volume', 'Plex' -- etc.) For selecting all resources set this to '%'. -- -- OUT Parameters: -- ------- -- p_vol_list_cursor - a ref cursor containing relevant data -- p_prod_vendor_comb_count - number of rows that have distinct -- vendor and product combinations -- p_prod_vendor_combination - rows containing distinct -- vendor and product combinations -- -- Implementation Notes: -- --------------------------------------------------------- PROCEDURE get_vol_storage_data ( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2 DEFAULT mgmt_global.G_HOST_TARGET_TYPE, p_resource_type IN VARCHAR2, p_prod_vendor_comb_count IN OUT number, p_prod_vendor_combination IN OUT SYS_REFCURSOR, p_vol_list_cursor IN OUT SYS_REFCURSOR ) is BEGIN OPEN p_prod_vendor_combination for SELECT distinct vendor, product FROM mgmt_v_host_vol_list v, mgmt_storage_report_ui_targets uit WHERE uit.ecm_snapshot_id = v.ecm_snapshot_id AND resource_type like p_resource_type; SELECT count(*) into p_prod_vendor_comb_count from ( SELECT distinct vendor, product FROM mgmt_v_host_vol_list v, mgmt_storage_report_ui_targets uit WHERE uit.ecm_snapshot_id = v.ecm_snapshot_id AND resource_type like p_resource_type ); --p_prod_vendor_comb_count := 2; IF( p_prod_vendor_comb_count > 1 ) then OPEN p_vol_list_cursor for SELECT resource_name, resource_type, disk_group, os_path, vendor, product, shared_count, storage_ui_util_pkg.convert_into_storage_unit( allocated, storage_ui_util_pkg.GB) allocated, storage_ui_util_pkg.convert_into_storage_unit( unallocated, storage_ui_util_pkg.GB) unallocated, configuration, global_unique_id FROM mgmt_v_host_vol_list v, mgmt_storage_report_ui_targets uit WHERE uit.ecm_snapshot_id = v.ecm_snapshot_id AND resource_type like p_resource_type; else OPEN p_vol_list_cursor for SELECT resource_name, resource_type, disk_group, os_path, shared_count, storage_ui_util_pkg.convert_into_storage_unit( allocated, storage_ui_util_pkg.GB) allocated, storage_ui_util_pkg.convert_into_storage_unit( unallocated, storage_ui_util_pkg.GB) unallocated, configuration, global_unique_id FROM mgmt_v_host_vol_list v, mgmt_storage_report_ui_targets uit WHERE uit.ecm_snapshot_id = v.ecm_snapshot_id AND resource_type like p_resource_type; END IF; END get_vol_storage_data; -------------------------------------------------------- -- Name: get_resource_types -- -- Package: storage_ui_host_pkg -- -- Purpose: -- gets distinct resource types for a given storage layer of a host -- -- IN Parameters: -- p_target_name - the target name (host name) -- p_target_type - the target type. -- -- Returns: -- ------- -- a ref cursor containing relevant data -- -- Implementation Notes: -- ------------------------------------------------------- FUNCTION get_resource_types ( p_storage_layer IN VARCHAR2 ) RETURN SYS_REFCURSOR is l_result_cursor SYS_REFCURSOR; BEGIN OPEN l_result_cursor for SELECT DISTINCT entity_type as resource_type FROM mgmt_v_storage_report_data data, mgmt_storage_report_ui_targets uit WHERE uit.ecm_snapshot_id IS NOT NULL AND uit.ecm_snapshot_id = data.ecm_snapshot_id AND data.storage_layer = p_storage_layer; RETURN l_result_cursor; END get_resource_types; END; / show errors;