Modyfing, testing and debugging PL/SQL packages – Part III – Big packages

Scope

Following the previous post we will continue our investigations concerning the modifications of a big package.
Debugging a big package can be a challenging task. Of course you could use a debugging program, but in our case we have to modify some expressions in order to get the expected results. For this purpose we will use instead a custom tracing procedure.

Custom tracing procedure

You will find below a custom tracing procedure that we will use:

-- =======================================================================
--  Procedure     : my_tracing
--  Description   : Custom tracing program
--  Parameters    : in_message, in_unit, in_line, in_sub_unit, in_type 
--  Author        : Arnaud Fargues
--  Creation      : 17.12.2017
--  Modifications : 07.01.2018
-- =======================================================================
PROCEDURE my_tracing ( 
   in_message  IN VARCHAR2,
   in_unit     IN VARCHAR2 DEFAULT NULL,
   in_line     IN NUMBER   DEFAULT NULL,
   in_sub_unit IN VARCHAR2 DEFAULT NULL,
   in_type     IN CHAR     DEFAULT 'I')
AS
   l_message VARCHAR2(255);
BEGIN
   IF NVL($$is_tracing,FALSE)
   THEN
      IF in_unit IS NOT NULL
      THEN
         l_message := '{' || in_unit;
         IF in_sub_unit IS NOT NULL
         THEN
            l_message := l_message || '/' || in_sub_unit || '}';
         ELSE
            l_message := l_message || '}';
         END IF;
      ELSIF in_sub_unit IS NOT NULL
      THEN
         l_message := '{' || in_sub_unit || '}';
      ELSE
         l_message := '{}';
      END IF;
      DBMS_OUTPUT.PUT_LINE('(' || in_type || ')' || in_line || l_message || ':' || in_message);
   END IF;
END my_tracing;

A typical call of this function is:

...
my_tracing('condition1',$$PLSQL_UNIT,$$PLSQL_LINE,'function51');
...

You will find a description of the different parameters below:

• in_message : message that we want to display,
• in_unit : PL/SQL current unit provided by the inquiry directive $$PLSQL_UNIT,
• in_line : source line number called by this procedure provided by the inquiry directive $$PLSQL_LINE,
• in_sub_unit : sub PL/SQL unit,
• in_type : information type – (I)nfo, (W)arning, (E)rror.

Remarks:

• We will use the inquiry directive $$is_tracing in order to trace or not the package,
• We have decided for this case to include the procedure my_tracing in the BIG_PACKAGE1 package body, but this procedure could be integrated in a general purpose package.

You will find below the modified package corresponding to the “Fourth approach – Conditional compilation”:

CREATE OR REPLACE PACKAGE BODY DEV3.BIG_PACKAGE1 AS

-- =======================================================================
--  Procedure     : my_tracing
--  Description   : Custom tracing program
--  Parameters    : in_message, in_unit, in_line, in_sub_unit, in_type 
--  Author        : Arnaud Fargues
--  Creation      : 17.12.2017
--  Modifications : 07.01.2018
-- =======================================================================
PROCEDURE my_tracing ( 
   in_message  IN VARCHAR2,
   in_unit     IN VARCHAR2 DEFAULT NULL,
   in_line     IN NUMBER   DEFAULT NULL,
   in_sub_unit IN VARCHAR2 DEFAULT NULL,
   in_type     IN CHAR     DEFAULT 'I')
AS
   l_message VARCHAR2(255);
BEGIN
   IF NVL($$is_tracing,FALSE)
   THEN
      IF in_unit IS NOT NULL
      THEN
         l_message := '{' || in_unit;
         IF in_sub_unit IS NOT NULL
         THEN
            l_message := l_message || '/' || in_sub_unit || '}';
         ELSE
            l_message := l_message || '}';
         END IF;
      ELSIF in_sub_unit IS NOT NULL
      THEN
         l_message := '{' || in_sub_unit || '}';
      ELSE
         l_message := '{}';
      END IF;
      DBMS_OUTPUT.PUT_LINE('(' || in_type || ')' || in_line || l_message || ':' || in_message);
   END IF;
END my_tracing;
...
-- =======================================================================
-- function : function51 lvl:1
-- =======================================================================
FUNCTION function51(in_param NUMBER) RETURN NUMBER AS
l_GogRtbjjixylNzMdLPb NUMBER;
BEGIN
l_GogRtbjjixylNzMdLPb  := in_param;
IF in_param = 9878 AND $$function51_condition1 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7684;
   my_tracing('condition1',$$PLSQL_UNIT,$$PLSQL_LINE,'function51');
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 448;
END IF;
l_GogRtbjjixylNzMdLPb  := function14(l_GogRtbjjixylNzMdLPb);
IF in_param = 5554
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 8352;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 3003;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 458 AND $$function51_condition2 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7434;
   my_tracing('condition2',$$PLSQL_UNIT,$$PLSQL_LINE,'function51');
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 2579;
END IF;
l_GogRtbjjixylNzMdLPb  := function3(l_GogRtbjjixylNzMdLPb);
IF in_param = 8905 AND $$function51_condition3 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7326;
   my_tracing('condition3',$$PLSQL_UNIT,$$PLSQL_LINE,'function51');
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 6456;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 9569 AND $$function51_condition4 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 1804;
   my_tracing('condition4',$$PLSQL_UNIT,$$PLSQL_LINE,'function51');
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 4303;
END IF;
l_GogRtbjjixylNzMdLPb  := function29(l_GogRtbjjixylNzMdLPb);
...
RETURN l_GARQUWfazCmqmeKNsNRQ;
END function175;
END BIG_PACKAGE1 ;
/

We will set the different inquiry directives and then recompile the package body:

DEV3@pdb1> ALTER SESSION SET PLSQL_CCFLAGS = 'is_tracing:TRUE,function51_condition1:FALSE,function51_condition2:TRUE,function51_condition3:FALSE,function51_condition4:TRUE';

Session altered.

DEV3@pdb1> ALTER PACKAGE DEV3.BIG_PACKAGE1 COMPILE BODY;

Package body altered.

We will then execute the function51 as shown below:

DEV3@pdb1> SET SERVEROUTPUT ON
DEV3@pdb1> DECLARE
  2     l_return NUMBER;
  3  BEGIN
  4
  5     l_return := DEV3.BIG_PACKAGE1.FUNCTION51(458);
  6
  7     DBMS_OUTPUT.PUT_LINE('l_return:' || TO_CHAR(l_return));
  8
  9  END;
 10  /
(I)18119{BIG_PACKAGE1/function51}:condition2
l_return:-515621871

PL/SQL procedure successfully completed.

DEV3@pdb1> DECLARE
  2     l_return NUMBER;
  3  BEGIN
  4
  5     l_return := DEV3.BIG_PACKAGE1.FUNCTION51(9569);
  6
  7     DBMS_OUTPUT.PUT_LINE('l_return:' || TO_CHAR(l_return));
  8
  9  END;
 10  /
(I)18135{BIG_PACKAGE1/function51}:condition4
l_return:-515616666

PL/SQL procedure successfully completed.

Update 18.02.2018

The procedure my_tracing is usefull nevertheless the call parameters are not convenient because you have to indicate each time the parameters in_unit ($$PLSQL_UNIT), in_line ($$PLSQL_LINE) and in_sub_unit.

Since the Oracle 12cR1 release there is a new PL/SQL package UTL_CALL_STACK which is an enhancement of the DBMS_UTILITY.FORMAT_CALL_STACK package. In order to replace the parameters in_unit,in_line and in_sub_unit we will use the functions UTL_CALL_STACK.UNIT_LINE and UTL_CALL_STACK.SUBPROGRAM.

You will find below the modified my_tracing procedure:

-- =======================================================================
--  Procedure     : my_tracing
--  Description   : Custom tracing program
--  Parameters    : in_message, in_unit, in_line, in_sub_unit, in_type 
--  Author        : Arnaud Fargues
--  Creation      : 17.12.2017
--  Modifications : 07.01.2018
--                  11.02.2018 Remove in_line, in_unit and in_sub_unit parameters
-- =======================================================================
PROCEDURE my_tracing ( 
   in_message  IN VARCHAR2,
   in_type     IN CHAR     DEFAULT 'I')
AS
   l_message VARCHAR2(255);
   l_line    NUMBER;
   l_unit    VARCHAR2(255);
BEGIN
   IF NVL($$is_tracing,FALSE)
   THEN
      l_line := UTL_CALL_STACK.UNIT_LINE(2);
      l_unit := UTL_CALL_STACK.CONCATENATE_SUBPROGRAM(UTL_CALL_STACK.SUBPROGRAM(2));
      l_message := '{' || l_unit || '}';
      DBMS_OUTPUT.PUT_LINE('(' || in_type || ')' || l_line || l_message || ':' || in_message);
   END IF;
END my_tracing;

Remarks:

• Each function UTL_CALL_STACK.UNIT_LINE and UTL_CALL_STACK.SUBPROGRAM use a parameter which is the depth level in the call stack. The first level represents the my_tracing procedure level, the second level represents the level of the unit that have called the my_tracing procedure. In our case we are interested by the level 2,
• The function UTL_CALL_STACK.CONCATENATE_SUBPROGRAM returns the called path of the different units e.g. unit1.unit2.unit3.

You will find below the modified package:

CREATE OR REPLACE PACKAGE BODY DEV3.BIG_PACKAGE1 AS

-- =======================================================================
--  Procedure     : my_tracing
--  Description   : Custom tracing program
--  Parameters    : in_message, in_unit, in_line, in_sub_unit, in_type 
--  Author        : Arnaud Fargues
--  Creation      : 17.12.2017
--  Modifications : 07.01.2018
--                  11.02.2018 Remove in_line, in_unit and in_sub_unit parameters
-- =======================================================================
PROCEDURE my_tracing ( 
   in_message  IN VARCHAR2,
   in_type     IN CHAR     DEFAULT 'I')
AS
   l_message VARCHAR2(255);
   l_line    NUMBER;
   l_unit    VARCHAR2(255);
BEGIN
   IF NVL($$is_tracing,FALSE)
   THEN
      l_line := UTL_CALL_STACK.UNIT_LINE(2);
      l_unit := UTL_CALL_STACK.CONCATENATE_SUBPROGRAM(UTL_CALL_STACK.SUBPROGRAM(2));
      l_message := '{' || l_unit || '}';
      DBMS_OUTPUT.PUT_LINE('(' || in_type || ')' || l_line || l_message || ':' || in_message);
   END IF;
END my_tracing;
... 
-- =======================================================================
-- function : function51 lvl:1
-- =======================================================================
FUNCTION function51(in_param NUMBER) RETURN NUMBER AS
l_GogRtbjjixylNzMdLPb NUMBER;
BEGIN
l_GogRtbjjixylNzMdLPb  := in_param;
IF in_param = 9878 AND $$function51_condition1 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7684;
   my_tracing('condition1');
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 448;
END IF;
l_GogRtbjjixylNzMdLPb  := function14(l_GogRtbjjixylNzMdLPb);
IF in_param = 5554
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 8352;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 3003;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 458 AND $$function51_condition2 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7434;
   my_tracing('condition2');
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 2579;
END IF;
l_GogRtbjjixylNzMdLPb  := function3(l_GogRtbjjixylNzMdLPb);
IF in_param = 8905 AND $$function51_condition3 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7326;
   my_tracing('condition3');
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 6456;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 9569 AND $$function51_condition4 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 1804;
   my_tracing('condition4');
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 4303;
END IF;
l_GogRtbjjixylNzMdLPb  := function29(l_GogRtbjjixylNzMdLPb);
...
RETURN l_GARQUWfazCmqmeKNsNRQ;
END function175;
END BIG_PACKAGE1 ;
/

We will set the different inquiry directives and then recompile the package body:

DEV3@pdb1> ALTER SESSION SET PLSQL_CCFLAGS = 'is_tracing:TRUE,function51_condition1:FALSE,function51_condition2:TRUE,function51_condition3:FALSE,function51_condition4:TRUE';

Session altered.

DEV3@pdb1> ALTER PACKAGE DEV3.BIG_PACKAGE1 COMPILE BODY;

Package body altered.

We will then execute then function51 as shown below:

DEV3@pdb1> SET SERVEROUTPUT ON
DEV3@pdb1>
DEV3@pdb1> DECLARE
  2     l_return NUMBER;
  3  BEGIN
  4
  5     l_return := DEV3.BIG_PACKAGE1.FUNCTION51(458);
  6
  7     DBMS_OUTPUT.PUT_LINE('l_return:' || TO_CHAR(l_return));
  8
  9  END;
 10  /
(I)18107{BIG_PACKAGE1.FUNCTION51}:condition2
l_return:-515621871

PL/SQL procedure successfully completed.

DEV3@pdb1> SET SERVEROUTPUT ON
DEV3@pdb1>
DEV3@pdb1> DECLARE
  2     l_return NUMBER;
  3  BEGIN
  4
  5     l_return := DEV3.BIG_PACKAGE1.FUNCTION51(9569);
  6
  7     DBMS_OUTPUT.PUT_LINE('l_return:' || TO_CHAR(l_return));
  8
  9  END;
 10  /
(I)18123{BIG_PACKAGE1.FUNCTION51}:condition4
l_return:-515616666

PL/SQL procedure successfully completed.

Modyfing, testing and debugging PL/SQL packages – Part II – Big packages

Scope

Following the previous post we will continue our investigations concerning the modifications of a big package.

Third approach – Associative array

This approach is similar to the second one, except that the name of the conditions can be more verbose. We will use an associative array of boolean in order to store the conditions. We must add an “initialization” procedure to set up the different conditions that will be executed in the initialization part of the package body.
You will find below the modified package:

CREATE OR REPLACE PACKAGE BODY DEV3.BIG_PACKAGE1 AS

   TYPE param_ct IS TABLE OF BOOLEAN INDEX BY VARCHAR2(250);

   l_param_type param_ct;
...
-- =======================================================================
-- function : function51 lvl:1
-- =======================================================================
FUNCTION function51(in_param NUMBER) RETURN NUMBER AS
l_GogRtbjjixylNzMdLPb NUMBER;
BEGIN
l_GogRtbjjixylNzMdLPb  := in_param;
IF in_param = 9878 AND l_param_type('function51_condition1') -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7684;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 448;
END IF;
l_GogRtbjjixylNzMdLPb  := function14(l_GogRtbjjixylNzMdLPb);
IF in_param = 5554
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 8352;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 3003;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 458 AND l_param_type('function51_condition2') -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7434;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 2579;
END IF;
l_GogRtbjjixylNzMdLPb  := function3(l_GogRtbjjixylNzMdLPb);
IF in_param = 8905 AND l_param_type('function51_condition3') -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7326;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 6456;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 9569 AND l_param_type('function51_condition4') -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 1804;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 4303;
END IF;
l_GogRtbjjixylNzMdLPb  := function29(l_GogRtbjjixylNzMdLPb);
...
RETURN l_GARQUWfazCmqmeKNsNRQ;
END function175;

PROCEDURE init_param
AS
BEGIN

   --
   -- function51
   --
   l_param_type('function51_condition1') := FALSE;
   l_param_type('function51_condition2') := TRUE;
   l_param_type('function51_condition3') := FALSE;
   l_param_type('function51_condition4') := TRUE;

   --
   -- function63
   --
   l_param_type('function63_condition1') := TRUE;
   l_param_type('function63_condition2') := FALSE;
   l_param_type('function63_condition3') := FALSE;
   l_param_type('function63_condition4') := TRUE;

   --
   -- function88
   --
   l_param_type('function88_condition1') := FALSE;
   l_param_type('function88_condition2') := TRUE;
   l_param_type('function88_condition3') := FALSE;
   l_param_type('function88_condition4') := FALSE;

   --
   -- function114
   --
   l_param_type('function114_condition1') := FALSE;
   l_param_type('function114_condition2') := TRUE;
   l_param_type('function114_condition3') := TRUE;
   l_param_type('function114_condition4') := TRUE;

   --
   -- function152
   --
   l_param_type('function152_condition1') := FALSE;
   l_param_type('function152_condition2') := FALSE;
   l_param_type('function152_condition3') := FALSE;
   l_param_type('function152_condition4') := TRUE;

   --
   -- function166
   --
   l_param_type('function166_condition1') := FALSE;
   l_param_type('function166_condition2') := TRUE;
   l_param_type('function166_condition3') := FALSE;
   l_param_type('function166_condition4') := TRUE;

END init_param;

BEGIN
   init_param;

END BIG_PACKAGE1 ;
/

Fourth approach – Conditional compilation

This approach is also similar to the second one, except that we will use inquiry directives e.g. $$function51_condition1 to set the different conditions. Those conditions do not need to be declared and to be initialized in the package body. We will set up the values using the command “ALTER SESSION SET PLSQL_CCFLAGS = …”.

Remark: The value of an inquiry directive can be a BOOLEAN or a PLS_INTEGER.

You will find below the modified package:

CREATE OR REPLACE PACKAGE BODY DEV3.BIG_PACKAGE1 AS
-- lvl:3
FUNCTION function6(in_param NUMBER) RETURN NUMBER;
FUNCTION function7(in_param NUMBER) RETURN NUMBER;
...
-- =======================================================================
-- function : function51 lvl:1
-- =======================================================================
FUNCTION function51(in_param NUMBER) RETURN NUMBER AS
l_GogRtbjjixylNzMdLPb NUMBER;
BEGIN
l_GogRtbjjixylNzMdLPb  := in_param;
IF in_param = 9878 AND $$function51_condition1 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7684;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 448;
END IF;
l_GogRtbjjixylNzMdLPb  := function14(l_GogRtbjjixylNzMdLPb);
IF in_param = 5554
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 8352;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 3003;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 458 AND $$function51_condition2 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7434;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 2579;
END IF;
l_GogRtbjjixylNzMdLPb  := function3(l_GogRtbjjixylNzMdLPb);
IF in_param = 8905 AND $$function51_condition3 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7326;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 6456;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 9569 AND $$function51_condition4 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 1804;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 4303;
END IF;
l_GogRtbjjixylNzMdLPb  := function29(l_GogRtbjjixylNzMdLPb);
...
RETURN l_GARQUWfazCmqmeKNsNRQ;
END function175;
END BIG_PACKAGE1 ;
/

At this point the values of the different inquiry directives are equal to NULL. We will set up the values as follow:

DEV3@pdb1> ALTER SESSION SET PLSQL_CCFLAGS = 'function51_condition1:FALSE, function51_condition2:TRUE, function51_condition3:FALSE, function51_condition4:TRUE';

Session altered.

and then we have to compile the package body in order to be taken into account:

DEV3@pdb1> ALTER PACKAGE DEV3.BIG_PACKAGE1 COMPILE BODY;

Package body altered.

Remarks:

• The package has to be compiled every time the values of the different inquiry directives change,
• The number of inquiry directives is limited as shown in the following example.

DEV3@pdb1> ALTER SESSION SET PLSQL_CCFLAGS = 'function51_condition1:FALSE, function51_condition2:TRUE, function51_condition3:FALSE, function51_condition4:TRUE, function51_condition1:FALSE, function51_condition2:TRUE, function51_condition3:FALSE, function51_condition4:TRUE, function51_condition1:FALS
E, function51_condition2:TRUE, function51_condition3:FALSE, function51_condition4:TRUE';
ERROR:
ORA-32021: parameter value longer than 255 characters

Modifying, testing and debugging PL/SQL packages – Part I – Big packages

Scope

From time to time you have to modify what I call a “big PL/SQL package” e.g. a package with more than 20 000 source code lines which is not a good practise. I had to modify such a package where some conditions have to be adjusted for a subset of functions. In this specific case, the package could not be transformed in order to “loose weigth”.

Set up

For the demonstration purposes, I have generated a big package with 175 functions all containing a variable number of IF THEN ELSE statements. Each function is linked to a level from 1 to 3. The level 1 is the top level. A function whose level is N can only call function whose level is N+1.

The functions to be modified are the following:

• function51,
• function63,
• function88,
• function114,
• function152,
• function166.

What I have not indicated in the generated package is that some code must be added.

Remarks:

• The variable names have been randomly generated,
• The different IF THEN ELSE statements have been randomly generated,
• The number of IF THEN ELSE statements have been also randomly generated.

You will find below an extract of this package:

CREATE OR REPLACE PACKAGE DEV3.BIG_PACKAGE1 AS
--
FUNCTION function1(in_param NUMBER)
RETURN NUMBER;
--
FUNCTION function9(in_param NUMBER)
RETURN NUMBER;
--
FUNCTION function12(in_param NUMBER)
RETURN NUMBER;
--
FUNCTION function20(in_param NUMBER)
RETURN NUMBER;
--
FUNCTION function23(in_param NUMBER)
RETURN NUMBER;
--
FUNCTION function30(in_param NUMBER)
RETURN NUMBER;
...
--
FUNCTION function172(in_param NUMBER)
RETURN NUMBER;
END BIG_PACKAGE1;
/
CREATE OR REPLACE PACKAGE BODY DEV3.BIG_PACKAGE1 AS
-- lvl:3
FUNCTION function6(in_param NUMBER) RETURN NUMBER;
FUNCTION function7(in_param NUMBER) RETURN NUMBER;
...
-- =======================================================================
-- function : function51 lvl:1
-- =======================================================================
FUNCTION function51(in_param NUMBER) RETURN NUMBER AS
l_GogRtbjjixylNzMdLPb NUMBER;
BEGIN
l_GogRtbjjixylNzMdLPb  := in_param;
IF in_param = 9878
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7684;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 448;
END IF;
l_GogRtbjjixylNzMdLPb  := function14(l_GogRtbjjixylNzMdLPb);
IF in_param = 5554
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 8352;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 3003;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 458
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7434;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 2579;
END IF;
l_GogRtbjjixylNzMdLPb  := function3(l_GogRtbjjixylNzMdLPb);
IF in_param = 8905
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7326;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 6456;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 9569
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 1804;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 4303;
END IF;
l_GogRtbjjixylNzMdLPb  := function29(l_GogRtbjjixylNzMdLPb);
...
END IF;
RETURN l_GARQUWfazCmqmeKNsNRQ;
END function175;
END BIG_PACKAGE1 ;
/

and below the number of lines:

First approach

The first approach is to:

• Step1: Modify directly the conditions of the different functions,
• Step2: Test the package,
• Step3: Go to Step1 if the expected result is not correct.

Nevertheless, there are some drawbacks:

• Step1 is not convenient because you have to locate in the package the conditions to be modified which is not easy in a “big package”,
• If you are using a PL/SQL editor it can take a long time to navigate through the code.

Second approach

In this approach I will used boolean constants that will enable or not some conditions. Thoses constants are defined in the begining of the package body. Each functions to be modified has a set of constants that control the conditions behavior.
You will find below the definitions of the constants and the code of a function with thoses constants:

CREATE OR REPLACE PACKAGE BODY DEV3.BIG_PACKAGE1 AS

   --
   -- function51
   --
   co_function51_condition1 CONSTANT BOOLEAN := FALSE;
   co_function51_condition2 CONSTANT BOOLEAN := TRUE;
   co_function51_condition3 CONSTANT BOOLEAN := FALSE;
   co_function51_condition4 CONSTANT BOOLEAN := TRUE;

   --
   -- function63
   --
   co_function63_condition1 CONSTANT BOOLEAN := TRUE;
   co_function63_condition2 CONSTANT BOOLEAN := FALSE;
   co_function63_condition3 CONSTANT BOOLEAN := FALSE;
   co_function63_condition4 CONSTANT BOOLEAN := TRUE;

   --
   -- function88
   --
   co_function88_condition1 CONSTANT BOOLEAN := FALSE;
   co_function88_condition2 CONSTANT BOOLEAN := TRUE;
   co_function88_condition3 CONSTANT BOOLEAN := FALSE;
   co_function88_condition4 CONSTANT BOOLEAN := FALSE;

   --
   -- function114
   --
   co_function114_condition1 CONSTANT BOOLEAN := FALSE;
   co_function114_condition2 CONSTANT BOOLEAN := TRUE;
   co_function114_condition3 CONSTANT BOOLEAN := TRUE;
   co_function114_condition4 CONSTANT BOOLEAN := TRUE;

   --
   -- function152
   --
   co_function152_condition1 CONSTANT BOOLEAN := FALSE;
   co_function152_condition2 CONSTANT BOOLEAN := FALSE;
   co_function152_condition3 CONSTANT BOOLEAN := FALSE;
   co_function152_condition4 CONSTANT BOOLEAN := TRUE;

   --
   -- function166
   --
   co_function166_condition1 CONSTANT BOOLEAN := FALSE;
   co_function166_condition2 CONSTANT BOOLEAN := TRUE;
   co_function166_condition3 CONSTANT BOOLEAN := FALSE;
   co_function166_condition4 CONSTANT BOOLEAN := TRUE;


-- lvl:3
FUNCTION function6(in_param NUMBER) RETURN NUMBER;
FUNCTION function7(in_param NUMBER) RETURN NUMBER;
...
-- =======================================================================
-- function : function51 lvl:1
-- =======================================================================
FUNCTION function51(in_param NUMBER) RETURN NUMBER AS
l_GogRtbjjixylNzMdLPb NUMBER;
BEGIN
l_GogRtbjjixylNzMdLPb  := in_param;
IF in_param = 9878 AND co_function51_condition1 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7684;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 448;
END IF;
l_GogRtbjjixylNzMdLPb  := function14(l_GogRtbjjixylNzMdLPb);
IF in_param = 5554
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 8352;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 3003;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 458 AND co_function51_condition2 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7434;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 2579;
END IF;
l_GogRtbjjixylNzMdLPb  := function3(l_GogRtbjjixylNzMdLPb);
IF in_param = 8905 AND co_function51_condition3 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7326;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 6456;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 9569 AND co_function51_condition4 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 1804;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 4303;
END IF;
l_GogRtbjjixylNzMdLPb  := function29(l_GogRtbjjixylNzMdLPb);
...
RETURN l_GARQUWfazCmqmeKNsNRQ;
END function175;
END BIG_PACKAGE1 ;
/

Remarks:

• Of course you must at the very beginning modify the code to add the constants, but once in place, every time you modify it, you simply need to change the constants at the beginning of the package body,
• As you can imagine, the final result of a function is not sufficient to check if the modifications are correct. We will see in a future post how we can display the intermediate results.