Rem Rem $Header: get_homes_all_body.sql 22-sep-2005.11:09:33 pdasika Exp $ Rem Rem get_homes_all_body.sql Rem Rem Copyright (c) 2004, 2005, Oracle. All rights reserved. Rem Rem NAME Rem get_homes_all_body.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem pdasika 09/20/05 - Improving get_target_credentials function Rem mningomb 08/30/05 - Rem pdasika 05/22/05 - Four new functions to get data in bulk Rem mningomb 08/25/04 - 10gr2: changes due to aru platform id Rem mningomb 08/18/04 - mningomb_impl Rem mningomb 07/13/04 - Created Rem CREATE OR REPLACE PACKAGE BODY GET_HOMES_ALL IS -- -- Purpose: The package contains util functions/procedures for getting oracle homes list -- -- --------- ------ ------------------------------------------ procedure check_home_platform( p_host_name in varchar2, p_dir_name in varchar2, p_platform_id in varchar2, home_count OUT integer, platform_match_count out integer) IS val integer := 0; platform_id number := 0; BEGIN select aru_platform_util.get_home_aru_platform(home.container_guid) into platform_id from mgmt_inv_container home, mgmt_ecm_snapshot snap where home.container_type = 'O' and home.snapshot_guid = snap.snapshot_guid and snap.snapshot_type = 'host_configuration' and snap.is_current = 'Y' and home.container_location = p_dir_name and snap.target_name = p_host_name and rownum = 1; if (platform_id > 0) then home_count := 1; else home_count := 0; end if; if (platform_id = p_platform_id) then platform_match_count := 1; else platform_match_count := 0; end if; EXCEPTION when NO_DATA_FOUND then home_count := 0; platform_match_count := 0; END; -- Procedure: sunos_aru_id_exists_for_home -- p_exist: -- 'YES' - host/dir is not a valid home, home is not SunOS, aru id exists for SunOS -- 'NO' - host/dir is valid home, SunOS and not aru id procedure sunos_aru_id_exists_for_home( p_host_name in varchar2, p_dir_name in varchar2, p_patch_bit_len in varchar2, p_exist OUT varchar2) IS home_guid mgmt_inv_container.snapshot_guid%type; mapped_aru_id number:=0; aru_id_count integer :=0; bit_len em$ecm_homes_by_aruid.address_size%type; BEGIN p_exist := 'YES'; -- First check if the host/dir is a valid home or not select home.container_guid into home_guid from mgmt_inv_container home, mgmt_ecm_snapshot snap where home.container_type = 'O' and home.snapshot_guid = snap.snapshot_guid and snap.snapshot_type = 'host_configuration' and snap.is_current = 'Y' and snap.target_name = p_host_name and home.container_location = p_dir_name; select count(*) into aru_id_count from mgmt_inv_container_property prop where prop.container_guid = home_guid and prop.property_name = ARU_PLATFORM_UTIL.G_ARU_ID_PROP_NAME; if (aru_id_count = 0 ) then select address_size into bit_len from em$ecm_homes_by_aruid where upper(platform) = 'SUNOS' and home_id = home_guid and rownum = 1; if ( bit_len = '64') then p_exist := 'NO'; end if; end if; EXCEPTION when NO_DATA_FOUND then p_exist := 'YES'; END; FUNCTION get_home_platform_id( p_host_name in varchar2, p_dir_name in varchar2) RETURN number IS platform_id number := 0; BEGIN select aru_platform_util.get_home_aru_platform(home.container_guid) into platform_id from mgmt_inv_container home, mgmt_ecm_snapshot snap where home.container_type = 'O' and home.snapshot_guid = snap.snapshot_guid and snap.snapshot_type = 'host_configuration' and snap.is_current = 'Y' and home.container_location = p_dir_name and snap.target_name = p_host_name and rownum = 1; return platform_id; EXCEPTION WHEN NO_DATA_FOUND then platform_id := 0; RETURN platform_id; END; -- -- Function: get_destination_platform -- Purpose : It takes an array of hosts and an array of directories and returns -- an REF CURSOR which contains the platform id and the platform type -- platform type can be 'ARU' or 'NOT_ARU' -- If the home platform could not be obtained, the host platform is -- returned -- --------- ------ ------------------------------------------ FUNCTION get_destination_platform( p_host_names in VARCHAR2_TABLE, p_home_dirs in VARCHAR2_TABLE) RETURN HOMEDETAILS_REF_CUR IS p_row_index number := 0; p_row_count number := 0; p_dest_platform HOMEDETAILS_REF_CUR; CURSOR dest_plat_cursor(p_host in varchar2,p_dir in varchar2) IS select platform_id,id_type FROM em$ecm_homes_by_aruid WHERE home_location = p_dir and host = p_host and ROWNUM = 1; CURSOR dest_host_plat_cursor(p_host in varchar2) IS select distinct plat.platform_id AS platform_id, 'NO_ARU' AS id_type FROM mgmt_ecm_snapshot snap, mgmt_targets htg, mgmt_target_properties hos, mgmt_hc_os_summary os, mgmt_aru_platforms plat WHERE htg.target_name = p_host and snap.snapshot_type = 'host_configuration' and snap.is_current = 'Y' and snap.target_name = htg.host_name and snap.target_type = 'host' and htg.target_guid = hos.target_guid and hos.property_name = 'OS' and hos.property_value is not null and plat.em_os_name = hos.property_value and plat.em_os_bitlength = replace(os.address_length_in_bits, '-bit','') and os.snapshot_guid = snap.snapshot_guid ; dest_platform_rec dest_plat_cursor%ROWTYPE; curr_dest_rec dest_platform_rec_type ; dest_platforms dest_platform_array := dest_platform_array(); BEGIN -- Look into table 'emdw_trace_data' for logging data -- set v_trace_level=>4 to set debugging --emdw_log.set_trace_level(v_trace_level=>0); p_row_count := p_host_names.count; FOR p_row_index in 1..p_row_count LOOP curr_dest_rec := NULL; OPEN dest_plat_cursor(p_host_names(p_row_index),p_home_dirs(p_row_index)); LOOP FETCH dest_plat_cursor INTO dest_platform_rec; EXIT when dest_plat_cursor%NOTFOUND; curr_dest_rec := dest_platform_rec_type(dest_platform_rec.platform_id, dest_platform_rec.id_type); END LOOP; CLOSE dest_plat_cursor; -- If there is no platform returned for host and directory, fall back to the host OS platform. IF curr_dest_rec IS NULL THEN OPEN dest_host_plat_cursor(p_host_names(p_row_index)); LOOP FETCH dest_host_plat_cursor INTO dest_platform_rec; EXIT when dest_host_plat_cursor%NOTFOUND; curr_dest_rec := dest_platform_rec_type(dest_platform_rec.platform_id, dest_platform_rec.id_type); END LOOP; CLOSE dest_host_plat_cursor; END IF; IF curr_dest_rec IS NULL THEN curr_dest_rec := dest_platform_rec_type(null,null); END IF; dest_platforms.EXTEND; dest_platforms(p_row_index) := curr_dest_rec; END LOOP; OPEN p_dest_platform FOR SELECT platform_id,id_type FROM TABLE(CAST(dest_platforms AS dest_platform_array)); return p_dest_platform; END; -- -- Function: get_target_credentials -- Purpose : The function returns the username and password of the -- type OHUsername and OHPassword pertaining to the Host and OracleHome -- It takes an array of hosts and an array of homes and returns -- an REF CURSOR which contains the correspoinding credentials for the home -- --------- ------ ------------------------------------------ FUNCTION get_target_credentials( p_host_names in VARCHAR2_TABLE, p_home_dirs in VARCHAR2_TABLE, p_curr_em_user in varchar2) RETURN HOMEDETAILS_REF_CUR IS p_row_index number := 0; p_row_count number := 0; p_home_creds_cur HOMEDETAILS_REF_CUR ; p_homes HOME_REC_ARRAY := HOME_REC_ARRAY(); BEGIN FOR p_index in p_host_names.FIRST..p_host_names.LAST loop p_homes.EXTEND; p_homes(p_index) := HOME_REC_TYPE(p_host_names(p_index),p_home_dirs(p_index),p_index); END LOOP; OPEN p_home_creds_cur FOR SELECT decode(creds.uname, null, null, decrypt(creds.uname)) uname, decode(creds.pwd, null, null, decrypt(creds.pwd)) pwd FROM TABLE(CAST(p_homes as HOME_REC_ARRAY)) homes, (SELECT c.credential_value as uname, b.credential_value as pwd,t.target_name,tc.container_location FROM MGMT_TARGETS t, MGMT_CONTAINER_CREDENTIALS tc, MGMT_CREDENTIALS2 c, MGMT_CREDENTIALS2 b WHERE t.target_guid=tc.target_guid and t.target_type = 'host' and tc.user_name= p_curr_em_user and c.credential_guid=b.credential_guid and tc.credential_guid=b.credential_guid and b.credential_set_column = 'OHPassword' and c.credential_set_column = 'OHUsername') creds WHERE creds.target_name(+) = homes.host_name and creds.container_location(+) = homes.home_location order by homes.home_index asc; RETURN p_home_creds_cur; EXCEPTION WHEN NO_DATA_FOUND then NULL; END; -- -- Function: get_target_in_oracle_home -- Purpose : This function return all the the targets of a specific target type -- which run out of this Oracle Home -- --------- ------ ------------------------------------------ FUNCTION get_targets_in_oracle_home( p_host_names in VARCHAR2_TABLE, p_home_names in VARCHAR2_TABLE, p_target_type in VARCHAR2) RETURN HOMEDETAILS_REF_CUR IS p_index number := 1; p_targets varchar_table := varchar_table(); p_home_indices varchar2_table := varchar2_table(); p_current_index number := 1; p_target_count number:= 0; p_output_targets varchar_table := varchar_table(); p_output_targets_cursor HOMEDETAILS_REF_CUR; CURSOR C1(p_homes in HOME_REC_ARRAY)IS SELECT t.target_name as target_name,homes.home_index as home_index FROM mgmt_targets t, mgmt_targets h, mgmt_target_properties p, TABLE(CAST(p_homes as HOME_REC_ARRAY)) homes WHERE h.target_type = 'host' AND h.target_name = homes.host_name AND h.target_name = t.host_name AND t.target_guid = p.target_guid AND t.target_type = p_target_type AND p.property_name = 'OracleHome' AND EXISTS ( SELECT * FROM mgmt_target_properties p2 WHERE t.target_guid = p2.target_guid AND p2.property_name = 'OracleHome' AND p2.property_value = homes.home_location ); p_homes HOME_REC_ARRAY := HOME_REC_ARRAY(); BEGIN FOR p_index in p_host_names.FIRST..p_host_names.LAST loop p_homes.EXTEND; p_homes(p_index) := HOME_REC_TYPE(p_host_names(p_index),p_home_names(p_index),p_index); END LOOP; OPEN C1(p_homes); FETCH C1 BULK COLLECT INTO p_targets,p_home_indices; p_target_count := p_targets.count; CLOSE C1; -- Initializing the output array for p_index in 1..p_target_count loop p_output_targets.EXTEND; p_output_targets(p_index) := NULL; end loop; -- In this loop, the targets which belong to the same oracle home are -- appended as a comma seperated list. FOR p_index IN 1..p_target_count LOOP p_current_index := TO_NUMBER(p_home_indices(p_index)); IF p_output_targets(p_current_index) IS NULL THEN p_output_targets(p_current_index) := p_targets(p_index); ELSE p_output_targets(p_current_index) := p_output_targets(p_current_index) || ',' || p_targets(p_index); END IF; END LOOP; OPEN p_output_targets_cursor FOR SELECT column_value FROM TABLE(CAST(p_output_targets AS varchar_table)); RETURN p_output_targets_cursor ; END; -- Function: get_job_status -- Purpose : The function takes in an array of hosts and homes and -- the execution id of a PatchStandaloneDatabase Job. It returns -- the status of the job on the home -- --------- ------ ------------------------------------------ FUNCTION get_job_status( p_host_names in VARCHAR2_TABLE, p_home_names in VARCHAR2_TABLE, p_execution_id in VARCHAR2) RETURN HOMEDETAILS_REF_CUR IS p_row_index number := 0; p_row_count number := 0; p_job_status HOMEDETAILS_REF_CUR; CURSOR current_job_status_for_home(p_host in varchar2,p_home in varchar2, p_execution_id in varchar2) IS SELECT je.step_status FROM MGMT_JOB_EXECUTION je, MGMT_JOB_PARAMETER pa,MGMT_JOB_PARAMETER pb WHERE je.execution_id = HEXTORAW(p_execution_id) AND je.step_name = 'patchDatabaseOnHome' AND je.EXECUTION_ID = pa.EXECUTION_ID AND je.job_id = pa.JOB_ID AND je.job_id = pb.JOB_ID AND pb.job_id = pa.job_id AND pa.PARAMETER_NAME = 'oraclehome' AND pa.SCALAR_VALUE = p_home AND pb.PARAMETER_NAME = 'hostname' AND pb.SCALAR_VALUE = p_host; CURSOR hist_job_status_for_home(p_host in varchar2,p_home in varchar2,p_execution_id in varchar2) IS SELECT je.step_status FROM MGMT_JOB_HISTORY je, MGMT_JOB_PARAMETER pa,MGMT_JOB_PARAMETER pb WHERE je.execution_id = HEXTORAW(p_execution_id) AND je.step_name = 'patchDatabaseOnHome' AND je.EXECUTION_ID = pa.EXECUTION_ID AND je.job_id = pa.JOB_ID AND je.job_id = pb.JOB_ID AND pb.job_id = pa.job_id AND pa.PARAMETER_NAME = 'oraclehome' AND pa.SCALAR_VALUE = p_home AND pb.PARAMETER_NAME = 'hostname' AND pb.SCALAR_VALUE = p_host; p_status varchar(2); p_status_array varchar2_table := varchar2_table(); BEGIN p_row_count := p_host_names.count; FOR p_row_index IN 1..p_row_count LOOP -- Default value is null p_status := NULL; --Look for the job status in the mgmt_job_execution table OPEN current_job_status_for_home(p_host_names(p_row_index),p_home_names(p_row_index),p_execution_id); LOOP FETCH current_job_status_for_home INTO p_status; EXIT when current_job_status_for_home%NOTFOUND; END LOOP; CLOSE current_job_status_for_home; --If the job has finished execution, the status can be found in the mgmt_job_history table IF p_status IS NULL THEN OPEN hist_job_status_for_home(p_host_names(p_row_index),p_home_names(p_row_index),p_execution_id); LOOP FETCH hist_job_status_for_home INTO p_status; EXIT when hist_job_status_for_home%NOTFOUND; END LOOP; CLOSE hist_job_status_for_home; END IF; p_status_array.EXTEND; p_status_array(p_row_index) := p_status; END LOOP; OPEN p_job_status FOR SELECT * FROM TABLE(CAST(p_status_array AS varchar2_table)); RETURN p_job_status ; END; END; / show errors