Oracle database experiences

Oracle database blog

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

Leave a comment Posted by on January 11, 2018

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.
PL/SQL, Programming , , , , , , , , ,

Leave a comment