Script to find Whether patches is applied in Oracle EBS
闲来无事的时候,写个一个PL/SQL脚本,用来查找Oracle Appplications中是否已经apply过一系列patch。在需要确认一堆补丁的状态时,是比较有用的。脚本使用PL/SQL中的varray数据类型存储需要查找的补丁号。
脚本内容如下:
DECLARE
V_COUNT NUMBER := 0;
V_PATCH_NUM VARCHAR2(20);
TYPE PATCH_NUMBER_TYPE IS VARRAY(10) OF VARCHAR2(20);
PATCH_NUMBER PATCH_NUMBER_TYPE := PATCH_NUMBER_TYPE('7303030',
'9062910',
'8919489',
'9868229',
'10163753',
'11071569',
'9738085',
'9852070',
'12686610',
'6400501');
BEGIN
FOR I IN 1 .. PATCH_NUMBER.COUNT LOOP
BEGIN
SELECT distinct 'APPLIED'
INTO V_PATCH_NUM
FROM AD_BUGS
WHERE BUG_NUMBER = PATCH_NUMBER(I);
DBMS_OUTPUT.PUT_LINE(PATCH_NUMBER(I) || ' is ' || V_PATCH_NUM);
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_PATCH_NUM := 'NOT APPLIED';
DBMS_OUTPUT.PUT_LINE(PATCH_NUMBER(I) || ' is ' || V_PATCH_NUM);
END;
END LOOP;
END;
运行的结果可能如下面所示:
7303030 is NOT APPLIED
9062910 is NOT APPLIED
8919489 is NOT APPLIED
9868229 is NOT APPLIED
10163753 is NOT APPLIED
11071569 is NOT APPLIED
9738085 is NOT APPLIED
9852070 is NOT APPLIED
12686610 is APPLIED
6400501 is NOT APPLIED
posted on 2016-07-27 23:21 Maxwell_Yang 阅读(83) 评论(0) 编辑 收藏 举报