Script to find Whether patches is applied in Oracle EBS

最新推荐文章于 2020-08-20 11:28:44 发布

原创最新推荐文章于 2020-08-20 11:28:44 发布 · 122 阅读

CC 4.0 BY-SA版权

本文介绍了一个PL/SQL脚本，用于检查Oracle应用程序中是否已应用一系列补丁，通过存储补丁号的varray数据类型进行操作。

闲来无事的时候，写个一个PL/SQL脚本，用来查找Oracle Appplications中是否已经apply过一系列patch。在需要确认一堆补丁的状态时，是比较有用的。脚本使用PL/SQL中的varray数据类型存储需要查找的补丁号。
脚本内容如下：

sqlDECLARE
  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;

运行的结果可能如下面所示：

sql7303030 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