Useful Oracle Apps (EBS) Patch Queries

1. Query to find the time taken for a patch to be applied.


col name for a15
col orig_patch_name for a15
col runtime for a30
SELECT NAME,
ORIG_PATCH_NAME,
to_char(START_DATE,'DD-MON-YYYY::HH24:MI:SS') START_DATE,
to_char(END_DATE,'DD-MON-YYYY::HH24:MI:SS') END_DATE,
TO_CHAR(FLOOR((end_date - start_date) * 24)) || ' hrs ' ||
TO_CHAR(FLOOR((end_date - start_date) * (24 * 60)) - (FLOOR((end_date - start_date) * 24) * 60)) || ' min ' ||
TO_CHAR(FLOOR((end_date - start_date) * (24 * 60 * 60)) - (FLOOR((end_date - start_date) * 24 * 60) * 60)) || ' sec '
RUNTIME,
SUCCESS_FLAG
FROM applsys.ad_patch_runs
JOIN applsys.ad_patch_drivers
USING (patch_driver_id)
JOIN applsys.ad_appl_tops
USING (appl_top_id)
where orig_patch_name like '%&patch_number%'
ORDER BY ad_patch_runs.creation_date DESC;

2. Note 364439.1 argues that checking ad_bugs for applied patches might not always be the good idea to check whether a given patch or a list of patches are applied. Instead it lists the below pl/sql procedure that can be used.

a. Query to find whether a list of patches were applied or not.

set serveroutput on
DECLARE
TYPE p_patch_array_type is varray(10) of varchar2(10);
--
p_patchlist p_patch_array_type;
p_appltop_name varchar2(50);
p_patch_status varchar2(15);
p_appl_top_id number;
--
CURSOR alist IS
select appl_top_id, name
from ad_appl_tops;
--
procedure println(msg in varchar2)
is
begin
dbms_output.enable;
dbms_output.put_line(msg);
end;
--
BEGIN
open alist;
--
p_patchlist:= p_patch_array_type('3240000','3460000','4204335','4125550','3942483','4733943');
--
LOOP
FETCH alist INTO p_appl_top_id,p_appltop_name;
EXIT WHEN alist%NOTFOUND;
--
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name || ':');
for i in 1..p_patchlist.count
loop
p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,p_patchlist(i));
println('..Patch ' || p_patchlist(i) || ' was ' || p_patch_status);
end loop;
END if;
println('.');
END LOOP;
--
close alist;
END;
/


b. Query to find whether a single patch was applied or not -- My customisation!

set serveroutput on;
DECLARE
p_appltop_name varchar2(50);
p_patch_status varchar2(15);
p_appl_top_id number;
--
cursor atoplist is
select appl_top_id, name from ad_appl_tops;
--
procedure println(msg in varchar2)
is
begin
dbms_output.enable;
dbms_output.put_line(msg);
end;
--
BEGIN
open atoplist;
--
LOOP
FETCH atoplist INTO p_appl_top_id,p_appltop_name;
EXIT WHEN atoplist%NOTFOUND;
--
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name || ':');
p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,&bug);
println('..Patch ' || &bug || ' was ' || p_patch_status);
END if;
println('.');
END LOOP;
--
close atoplist;
END;
/