Oracle database experiences

Oracle database blog

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

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: