Oracle database experiences

Oracle database blog

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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: