Package/Procedure behavior changes between 8.0.5 and 8.1.7

From: Brian Vickery <brian.vickery_at_attws.com>
Date: Mon, 06 Aug 2001 22:33:50 GMT
Message-ID: <iXEb7.17286$c8.4469817@news1.denver1.co.home.com>

We had a package/procedure that worked correctly in 8.0.5. We upgraded to 8.1.7, and now the result set is incorrect. I have tried to include a partial piece of code. We think the issue is with the optimizer...and that the FUNC_COUNTER call is getting executed before the ORDER BY puts the result set in the correct order. If the ORDER BY does not happen, then the FUNC_COUNTER may frequently reset the counters back to zero...because it will come across the region, MTA or activity changing more frequently.

Rather than....

Region MTA Activity

1             1          1
1             1          2
1             2          1
1             2          2
2             1          1

We may get...

Region MTA Activity

1             1          1
1             2          1                    <- See where this one is out
of order
1             2          2
1             1          2
2             1          1

Thanks for the help...this got critical quick because it is a popular report.

Brian Vickery

Script excerpt:

CREATE OR REPLACE PACKAGE BODY PACK_FYACT AS

• Package Variables ln_jancount number := 0 ; ln_febcount number := 0 ; ln_marcount number := 0 ; lvc_region varchar2(40) := 'DBV'; lvc_mta varchar2(50) := 'DBV'; lvc_activity varchar2(25) := 'DBV';
• Function that returns the serial number of the search ring
• with a list (for a region, mta and activity) FUNCTION FUNC_COUNTER ( avc_region varchar2, avc_mta varchar2, avc_activity varchar2,adt_fcst date ) RETURN number IS BEGIN IF lvc_region <> avc_region OR lvc_mta <> avc_mta OR lvc_activity <> avc_activity THEN ln_jancount := 0 ; ln_febcount := 0 ; ln_marcount := 0 ; lvc_region := avc_region; lvc_mta := avc_mta; lvc_activity := avc_activity; END IF;

    IF TO_CHAR(adt_fcst,'mm') = '01' THEN    ln_jancount := ln_jancount + 1;
   RETURN ln_jancount;
  ELSIF TO_CHAR(adt_fcst,'mm') = '02' THEN    ln_febcount := ln_febcount + 1;
   RETURN ln_febcount;
    ELSIF TO_CHAR(adt_fcst,'mm') = '03' THEN    ln_marcount := ln_marcount + 1;
   RETURN ln_marcount;
    END IF;  END FUNC_COUNTER;

• Procedure for the Fiscal Year Activity Status Report PROCEDURE PROC_FYACT ( avc_user IN varchar2,avc_fiscalyr IN varchar2, avc_activity IN varchar2, rc in out rc_type) as ln_count number; BEGIN
  • Store the activites passed in an array variable ln_count := FUNC_PARSE(avc_activity);
    • Initialize the package Variables lvc_region := 'DBV'; lvc_mta := 'DBV'; lvc_activity := 'DBV';

         OPEN rc FOR SELECT CP_REGION.REGION_DESC,
     CP_MTA.MTA_NAME,
     CP_PROJECT.SEARCH_RING,

FUNC_COUNTER(CP_REGION.REGION_DESC,CP_MTA.MTA_NAME,SCHED_ACTIVITY_CODE,FOREC AST_DATE) SR_COUNTER,
     '(ACT '||CP_PROJ_SCHED_ACTIVITY.SCHED_ACTIVITY_CODE||')' SCHED_ACTIVITY_CODE,

     CP_PROJ_SCHED_ACTIVITY.SCHED_ACTIVITY_DESC SCHED_ACTIVITY_DESC,
     SYSDATE,
     TO_CHAR(ACTUAL_DATE,'Q') ACTQTR,
     DECODE(TO_CHAR(PLAN_DATE,'MON YYYY'),'JAN '||avc_fiscalyr,1,0) JANPLAN,
     DECODE(TO_CHAR(PLAN_DATE,'MON YYYY'),'FEB '||avc_fiscalyr,1,0) FEBPLAN,
     DECODE(TO_CHAR(PLAN_DATE,'MON YYYY'),'MAR '||avc_fiscalyr,1,0) MARPLAN,
     DECODE(TO_CHAR(FORECAST_DATE,'MON YYYY'),'JAN

'||avc_fiscalyr,TO_CHAR(PLAN_DATE,'MON')||' - '||NVL(SEARCH_RING,'NULL'),'') JANSR,
     DECODE(TO_CHAR(FORECAST_DATE,'MON YYYY'),'FEB '||avc_fiscalyr,TO_CHAR(PLAN_DATE,'MON')||' - '||NVL(SEARCH_RING,'NULL'),'') FEBSR,
     DECODE(TO_CHAR(FORECAST_DATE,'MON YYYY'),'MAR '||avc_fiscalyr,TO_CHAR(PLAN_DATE,'MON')||' - '||NVL(SEARCH_RING,'NULL'),'') MARSR
    FROM I_USER_PROJ_FILTER_PROJS,

     CP_PROJECT,
     CP_PROJ_SCHED_ACTIVITY,
     CP_MTA,
     CP_REGION
   WHERE ( I_USER_PROJ_FILTER_PROJS.PROJ_NO = CP_PROJECT.PROJ_NO )
     AND (CP_PROJECT.PROJ_NO = CP_PROJ_SCHED_ACTIVITY.PROJ_NO)
     AND  ( CP_PROJECT.REGION_ID = CP_REGION.REGION_ID )
     AND  ( CP_PROJECT.MTA_ID = CP_MTA.MTA_ID )
     AND ( I_USER_PROJ_FILTER_PROJS.USERID = avc_user)
     AND (CP_PROJ_SCHED_ACTIVITY.SCHED_ACTIVITY_CODE IN (SELECT
FUNC_ACT(RPT_INDEX)
               FROM I_RPT_PLSQL
               WHERE RPT_INDEX <= ln_count))

   ORDER BY 1,2,5,4;  END PROC_FYACT;
END PACK_FYACT; Received on Mon Aug 06 2001 - 17:33:50 CDT