Oracle

將儲存過程從 Sybase 遷移到具有臨時表的 Oracle

  • November 4, 2021

我正在使用 Oracle Developer 工具將一些儲存過程從 Sybase 遷移到 Oracle。該工具遷移了幾乎 70%-80% 的儲存過程，除了在儲存過程中創建和使用臨時表的少數情況。

這是一個例子：

現有的sybase儲存過程：

CREATE GLOBAL TEMPORARY TABLE tt_RptRCRsn
(
 BatchNo CHAR(12) ,
 RecNo NUMBER ,
 CONTRACT CHAR(9) ,
 DateNotified CHAR(10) ,
 Reason CHAR(20) ,
 LOGIN CHAR(7) ,
 CheckNo CHAR(20) ,
 Amount NUMBER(10,2) ,
 Resolution CHAR(20) ,
 ResolvedDt CHAR(10) ,
 Comment_ VARCHAR2(100) ,
 CW CHAR(1) 
);
/ 

CREATE OR REPLACE PROCEDURE RptRCByRsn
(
 v_BegDT IN DATE DEFAULT NULL ,
 v_EndDt IN DATE DEFAULT NULL ,
 v_C IN CHAR DEFAULT NULL ,
 v_W IN CHAR DEFAULT NULL ,
 v_Res IN CHAR DEFAULT NULL ,
 v_A IN CHAR DEFAULT NULL ,
 v_I IN CHAR DEFAULT NULL ,
 v_E IN CHAR DEFAULT NULL ,
 v_B IN CHAR DEFAULT NULL ,
 v_U IN CHAR DEFAULT NULL ,
 v_M IN CHAR DEFAULT NULL ,
 v_N IN CHAR DEFAULT NULL ,
 cv_1 OUT SYS_REFCURSOR
)
AS

BEGIN
 IF v_Res = 'Y' THEN
     -- Grab all resolved returned checks
     INSERT INTO tt_RptRCRsn
       ( SELECT cw.BatchNo ,
                cw.RecNo ,
                cw.CONTRACT ,
                UTILS.CONVERT_TO_CHAR(NotifyDt,10,p_style=>101) ,
                rcr.Reason ,
                rc.LOGIN ,
                cw.CheckNo ,
                cw.Amount ,
                rcres.Resolution ,
                UTILS.CONVERT_TO_CHAR(rc.ResolvedDt,10,p_style=>101) ,
                ' ' ,
                bs.SourceCode 
         FROM ReturnChk rc,
              ChecksWires cw,
              BatchSummary bs,
              RCReason rcr,
              RCResolution rcres
           WHERE RCStatusCd = 'R'
                   AND rc.BatchNo = cw.BatchNo
                   AND rc.RecNo = cw.RecNo
                   AND rc.ReasonCd = rcr.ReasonCd
                   AND rc.ResCd = rcres.ResCd
                   AND rc.NotifyDt >= v_BegDt
                   AND rc.NotifyDt <= v_EndDt
                   AND bs.SourceCode IN ( v_C,v_W )

                   AND cw.TransCode IN ( v_A,v_I,v_E,v_B,v_U,v_M,v_N )

                   AND cw.BatchNo = bs.BatchNo );
  ELSE

     INSERT INTO tt_RptRCRsn
       ( SELECT cw.BatchNo ,
                cw.RecNo ,
                cw.CONTRACT ,
                UTILS.CONVERT_TO_CHAR(NotifyDt,10,p_style=>101) ,
                rcr.Reason ,
                rc.LOGIN ,
                cw.CheckNo ,
                cw.Amount ,
                rcres.Resolution ,
                CASE 
                     WHEN rc.ResolvedDt = '01/01/1900' THEN ' '
                ELSE UTILS.CONVERT_TO_CHAR(rc.ResolvedDt,10,p_style=>101)
                   END col  ,
                ' ' ,
                bs.SourceCode 
         FROM ReturnChk rc,
              ChecksWires cw,
              BatchSummary bs,
              RCReason rcr,
              RCResolution rcres
           WHERE RCStatusCd <> 'R'
                   AND rc.BatchNo = cw.BatchNo
                   AND rc.RecNo = cw.RecNo
                   AND rc.ReasonCd = rcr.ReasonCd
                   AND rc.ResCd = rcres.ResCd
                   AND rc.NotifyDt >= v_BegDt
                   AND rc.NotifyDt <= v_EndDt
                   AND bs.SourceCode IN ( v_C,v_W )

                   AND cw.TransCode IN ( v_A,v_I,v_E,v_B,v_U,v_M,v_N )

                   AND cw.BatchNo = bs.BatchNo );
  END IF;
  --Get most recent Returned Check comment 
  UPDATE tt_RptRCRsn
     SET Comment_ = ( SELECT COMMENTS.Comment_ 
                      FROM Comments 
                        WHERE COMMENTS.BatchNo = tt_RptRCRsn.BatchNo
                                AND COMMENTS.RecNo = tt_RptRCRsn.RecNo
                                AND COMMENTS.ComCode = 'R'
                                AND COMMENTS.ComNo = ( SELECT MAX(COMMENTS.ComNo) 
                                                       FROM Comments 
                                                         WHERE COMMENTS.BatchNo = tt_RptRCRsn.BatchNo
                                                                 AND COMMENTS.RecNo = tt_RptRCRsn.RecNo
                                                                 AND COMMENTS.ComCode = 'R' ) );
  OPEN  cv_1 FOR
     SELECT BatchNo ,
            RecNo ,
            CONTRACT ,
            DateNotified ,
            Reason ,
            LOGIN ,
            CheckNo ,
            Amount ,
            Resolution ,
            ResolvedDt ,
            COMMENT_ ,
            CW 
       FROM tt_RptRCRsn  ;
END;

遷移的 oracle 儲存過程：

/*Global Temporary Tables:1 *//* Translation Extracted DDL For Required Objects*/
CREATE GLOBAL TEMPORARY TABLE tt_RptRCRsn
(
 BatchNo CHAR(12) ,
 RecNo NUMBER ,
 CONTRACT CHAR(9) ,
 DateNotified CHAR(10) ,
 Reason CHAR(20) ,
 LOGIN CHAR(7) ,
 CheckNo CHAR(20) ,
 Amount NUMBER(10,2) ,
 Resolution CHAR(20) ,
 ResolvedDt CHAR(10) ,
 Comment_ VARCHAR2(100) ,
 CW CHAR(1) 
);
/



CREATE OR REPLACE PROCEDURE RptRCByRsn
(
 v_BegDT IN DATE DEFAULT NULL ,
 v_EndDt IN DATE DEFAULT NULL ,
 v_C IN CHAR DEFAULT NULL ,
 v_W IN CHAR DEFAULT NULL ,
 v_Res IN CHAR DEFAULT NULL ,
 v_A IN CHAR DEFAULT NULL ,
 v_I IN CHAR DEFAULT NULL ,
 v_E IN CHAR DEFAULT NULL ,
 v_B IN CHAR DEFAULT NULL ,
 v_U IN CHAR DEFAULT NULL ,
 v_M IN CHAR DEFAULT NULL ,
 v_N IN CHAR DEFAULT NULL ,
 cv_1 OUT SYS_REFCURSOR
)
AS

BEGIN
  /*
  12/22/03  MRJ  This SP is used to select the data for theReturned Checks by Reason Report.
  04/12/06  MRJ  ASE 15 Upgrade - Changed @BeginDt and @EndDt from char(10) to smalldatetime.
  05/02/08  SHA  Updated to identify NAV business in reporting.
  */
  IF v_Res = 'Y' THEN
     -- Grab all resolved returned checks
     INSERT INTO tt_RptRCRsn
       ( SELECT cw.BatchNo ,
                cw.RecNo ,
                cw.CONTRACT ,
                UTILS.CONVERT_TO_CHAR(NotifyDt,10,p_style=>101) ,
                rcr.Reason ,
                rc.LOGIN ,
                cw.CheckNo ,
                cw.Amount ,
                rcres.Resolution ,
                UTILS.CONVERT_TO_CHAR(rc.ResolvedDt,10,p_style=>101) ,
                ' ' ,
                bs.SourceCode 
         FROM ReturnChk rc,
              ChecksWires cw,
              BatchSummary bs,
              RCReason rcr,
              RCResolution rcres
           WHERE RCStatusCd = 'R'
                   AND rc.BatchNo = cw.BatchNo
                   AND rc.RecNo = cw.RecNo
                   AND rc.ReasonCd = rcr.ReasonCd
                   AND rc.ResCd = rcres.ResCd
                   AND rc.NotifyDt >= v_BegDt
                   AND rc.NotifyDt <= v_EndDt
                   AND bs.SourceCode IN ( v_C,v_W )

                   AND cw.TransCode IN ( v_A,v_I,v_E,v_B,v_U,v_M,v_N )

                   AND cw.BatchNo = bs.BatchNo );
  ELSE-- Grab all unresolved returned checks

     INSERT INTO tt_RptRCRsn
       ( SELECT cw.BatchNo ,
                cw.RecNo ,
                cw.CONTRACT ,
                UTILS.CONVERT_TO_CHAR(NotifyDt,10,p_style=>101) ,
                rcr.Reason ,
                rc.LOGIN ,
                cw.CheckNo ,
                cw.Amount ,
                rcres.Resolution ,
                CASE 
                     WHEN rc.ResolvedDt = '01/01/1900' THEN ' '
                ELSE UTILS.CONVERT_TO_CHAR(rc.ResolvedDt,10,p_style=>101)
                   END col  ,
                ' ' ,
                bs.SourceCode 
         FROM ReturnChk rc,
              ChecksWires cw,
              BatchSummary bs,
              RCReason rcr,
              RCResolution rcres
           WHERE RCStatusCd <> 'R'
                   AND rc.BatchNo = cw.BatchNo
                   AND rc.RecNo = cw.RecNo
                   AND rc.ReasonCd = rcr.ReasonCd
                   AND rc.ResCd = rcres.ResCd
                   AND rc.NotifyDt >= v_BegDt
                   AND rc.NotifyDt <= v_EndDt
                   AND bs.SourceCode IN ( v_C,v_W )

                   AND cw.TransCode IN ( v_A,v_I,v_E,v_B,v_U,v_M,v_N )

                   AND cw.BatchNo = bs.BatchNo );
  END IF;
  --Get most recent Returned Check comment 
  UPDATE tt_RptRCRsn
     SET Comment_ = ( SELECT COMMENTS.Comment_ 
                      FROM Comments 
                        WHERE COMMENTS.BatchNo = tt_RptRCRsn.BatchNo
                                AND COMMENTS.RecNo = tt_RptRCRsn.RecNo
                                AND COMMENTS.ComCode = 'R'
                                AND COMMENTS.ComNo = ( SELECT MAX(COMMENTS.ComNo) 
                                                       FROM Comments 
                                                         WHERE COMMENTS.BatchNo = tt_RptRCRsn.BatchNo
                                                                 AND COMMENTS.RecNo = tt_RptRCRsn.RecNo
                                                                 AND COMMENTS.ComCode = 'R' ) );
  OPEN  cv_1 FOR
     SELECT BatchNo ,
            RecNo ,
            CONTRACT ,
            DateNotified ,
            Reason ,
            LOGIN ,
            CheckNo ,
            Amount ,
            Resolution ,
            ResolvedDt ,
            Comment_ ,
            CW 
       FROM tt_RptRCRsn  ;
END;

遷移的儲存過程編譯時出現錯誤 - 即使創建了全域臨時表，“表或視圖不存在”。遷移過程找不到全域臨時表“tt_RptRCRsn”。

我嘗試為這個儲存過程授予所有權限，但它仍然沒有編譯，

我不想對儲存過程進行重大修改，因為這是使遷移的程式碼（oracle 過程）與現有程式碼（Sybase 過程）盡可能相似的要求

有人可以建議我如何實現這一目標的解決方案嗎？

請注意，這兩個命令將在目前架構中執行，這通常是您登錄的架構。

CREATE GLOBAL TEMPORARY TABLE tt_RptRCRsn
CREATE OR REPLACE PROCEDURE RptRCByRsn

這 6 個表必須存在於同一個模式中

ReturnChk
支票匯票
批次總結
RC原因
RC解析度
註釋

此外，UTILS.CONVERT_TO_CHAR 必須存在。

檢查您是否無意中在其他模式（如 SYSTEM）中創建了 tt_RptRCRsn 和 RptRCByRsn，並檢查表 ReturnChk 等是否也在正確的模式中創建。

我還有這些其他建議：

  1. 將數據類型 DATE 用於包含日期的輸入參數和表列，而不是字元列。
  2. 考慮使用 VARCHAR2 而不是 CHAR。
  3. 使用者更長、更有意義的參數名稱，而不是 v_A、v_I、v_E 等。
  4. 請注意，如果您使用的是 sqlplus，那麼“;” 後跟“/”將導致您的創建表命令執行兩次。輸出將如下所示：
表已創建。

創建全域臨時表 tt_RptRCRsn
*
第 1 行的錯誤：
ORA-00955: 名稱已被現有對象使用

如果您使用的是 sqlplus，則使用“;” 或“/”用於創建表，但不能同時使用兩者。

您的 Sybase 儲存過程實際上等於您的 Oracle 儲存過程！您可能做了錯誤的複制/粘貼。

引用自：https://dba.stackexchange.com/questions/71418

相關問答

Oracle

表不存在

  • December 5, 2013
檢視問答
Oracle

性能問題：PL/SQL

  • July 28, 2022
檢視問答
Oracle

如何在使用者連接後正好 8 小時終止 Oracle DB 中的使用者會話？

  • June 22, 2022
檢視問答
Oracle

努力提高此程式碼的效率

  • May 5, 2022
檢視問答
Oracle

將 SDO_GEOMETRY 線頂點提取到嵌套表列中（在查詢結果集中）

  • March 26, 2022
檢視問答
Oracle

將參數傳遞給由 DBMS_SCHEDULER 執行的過程

  • March 22, 2022
檢視問答