Migrating stored procedure from Sybase to Oracle having temporary tables

Question

I am migrating a few stored procedures from Sybase to Oracle using Oracle Developer tool. The tool migrates alomost 70%-80% of the stored procedures except a few cases where there is a temporary table created and used inside a stored procedures.

Here is an example:

Existing sybase stored procedure:

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;

Migrated oracle stored procedure:

/*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;

The migrated stored procedure compiles with an error - 'Table or view does not exist' even though global temporary table was created. The migrated proceure is not able to find the global temporary table 'tt_RptRCRsn'.

I tried giving grant all to this stored procedure but still its not compiling,

I dont want to make major modifications to the stored procedure as this is the requirement to keep the migrated code (oracle procedures) as similar as possible to the existing code (Sybase procedures)

Can someone please suggest a solution on how do I achieve this?

Brian Fitzgerald · Accepted Answer · 2014-07-15 01:02:33Z

Beware that the two commands will be run in the current schema, which is usually the schema that you logged in to.

CREATE GLOBAL TEMPORARY TABLE tt_RptRCRsn
CREATE OR REPLACE PROCEDURE RptRCByRsn

These 6 tables must exist in the same schema

ReturnChk
ChecksWires 
BatchSummary 
RCReason 
RCResolution 
Comments

Also, UTILS.CONVERT_TO_CHAR must exist.

Check that you did not inadvertently create tt_RptRCRsn and RptRCByRsn in some other schema, like SYSTEM, and check that tables ReturnChk, etc. also got created in the correct schema.

I have these other suggestions:

Use data type DATE for input parameters and table columns that contain dates, not character columns.
Consider using VARCHAR2 instead of CHAR.
User longer, more meaningful parameter names, instead of v_A, v_I, v_E, etc.
Beware that if you are using sqlplus, then ";" followed by "/" will cause your create table command to be run twice. The output would look like this:

Table created.

CREATE GLOBAL TEMPORARY TABLE tt_RptRCRsn
                              *
ERROR at line 1:
ORA-00955: name is already used by an existing object

If you are using sqlplus, then use either ";" or "/" for create table, but not both.

Guillaume · Accepted Answer · 2021-11-04 12:45:14Z

0

Your Sybase stored procedure is actually equal to your Oracle stored procedure ! You might have done a wrong copy/paste.

answered Nov 4, 2021 at 12:45

Guillaume

1

Add a comment |

Stack Exchange Network

Migrating stored procedure from Sybase to Oracle having temporary tables

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
oracle
plsql
temporary-tables
or ask your own question.

Hot Network Questions

Migrating stored procedure from Sybase to Oracle having temporary tables

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged oracleplsqltemporary-tables or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
oracle
plsql
temporary-tables
or ask your own question.