Script to find Whether patches is applied in Oracle EBS

花菜土豆粉發表於2014-12-12
APPOracle

閒來無事的時候，寫個一個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

相關文章