MacLochlainns Weblog

Teaching student how to debug a PL/SQL function takes about an hour now. I came up with the following example of simple deterministic function that adds three numbers and trying to understand how PL/SQL implicitly casts data types. The lecture follows a standard Harvard Case Study, which requires the students to suggest next steps. The starting code is:

1
2
3
4
5
6
7
8
9
10

CREATE OR REPLACE
  FUNCTION adding
  ( a  DOUBLE PRECISION
  , b  INTEGER
  , c  DOUBLE PRECISION )
  RETURN INTEGER DETERMINISTIC IS
  BEGIN
    RETURN a + b + c;
END;
/

Then, we use one test case for two scenarios:

SELECT adding(1.25, 2, 1.24) AS "Test Case 1"
,      adding(1.25, 2, 1.26) AS "Test Case 2"
FROM   dual;

It returns:

Test Case 1 Test Case 2
----------- -----------
          4           5

Then, I ask why does that work? Somehow many students can’t envision how it works. Occasionally, a student will say it must implicitly cast the INTEGER to a DOUBLE PRECISION data type and add the numbers as DOUBLE PRECISION values before down-casting it to an INTEGER data type.

Whether I have to explain it or a student volunteers it, the next question is: “How would you build a test case to see if the implicit casting?” Then, I ask them to take 5-minutes and try to see how the runtime behaves inside the function.

At this point in the course, they only know how to use dbms_output.put_line to print content from anonymous blocks. So, I provide them with a modified adding function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

CREATE OR REPLACE
  FUNCTION adding
  ( a  DOUBLE PRECISION
  , b  INTEGER
  , c  DOUBLE PRECISION )
  RETURN INTEGER DETERMINISTIC IS
 
    /* Define a double precision temporary result variable. */ 
    temp_result  NUMBER;
 
    /* Define an integer return variable. */
    temp_return  INTEGER;
 
  BEGIN
    /*
     *  Perform the calculation and assign the value to the temporary
     *  result variable.
     */
    temp_result := a + b + c;
 
    /*
     *  Assign the temporary result variable to the return variable.
     */
   temp_return := temp_result;
 
   /* Return the integer return variable as the function result. */
   RETURN temp_return;
 END;
/

The time limit ensures they spend their time typing the code from the on screen display and limits testing to the dbms_output.put_line attempt. Any more time and one or two of them would start using Google to find an answer.

I introduce the concept of a Black Box as their time expires, and typically use an illustration like the following to explain that by design you can’t see inside runtime operations of functions. Then, I teach them how to do exactly that.

You can test the runtime behaviors and view the variable values of functions by doing these steps:

1. Create a debug table, like

   CREATE TABLE debug ( msg VARCHAR2(200));

   CREATE TABLE debug ( msg VARCHAR2(200));

2. Make the function into an autonomous transaction by:
   • Adding the PRAGMA (or precompiler) instruction in the declaration block.
   • Adding a COMMIT at the end of the execution block.
3. Use an INSERT statement to write descriptive text with the variable values into the debug table.

Here’s the refactored test code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

CREATE OR REPLACE
  FUNCTION adding
  ( a  DOUBLE PRECISION
  , b  INTEGER
  , c  DOUBLE PRECISION )
  RETURN INTEGER DETERMINISTIC IS
 
    /* Define a double precision temporary result variable. */ 
    temp_result  NUMBER;
 
    /* Define an integer return variable. */
    temp_return  INTEGER;
 
    /* Precompiler Instrunction. */
    PRAGMA AUTONOMOUS_TRANSACTION;
 
  BEGIN
    /*
     *  Perform the calculation and assign the value to the temporary
     *  result variable.
     */
    temp_result := a + b + c;
 
    /* Insert the temporary result variable into the debug table. */
    INSERT INTO debug (msg) VALUES ('Temporary Result Value: ['||temp_result||']');
 
    /*
     *  Assign the temporary result variable to the return variable.
     */
   temp_return := temp_result;
 
    /* Insert the temporary result variable into the debug table. */
    INSERT INTO debug (msg) VALUES ('Temporary Return Value: ['||temp_return||']');
 
   /* Commit to ensure the write succeeds in a separate process scope. */
   COMMIT;
 
   /* Return the integer return variable as the function result. */
   RETURN temp_return;
 END;
/

While an experienced PL/SQL developer might ask while not introduce conditional computation, the answer is that’s for another day. Most students need to uptake pieces before assembling pieces and this example is already complex for a newbie.

The same test case works (shown to avoid scrolling up):

SELECT adding(1.25, 2, 1.24) AS "Test Case 1"
,      adding(1.25, 2, 1.26) AS "Test Case 2"
FROM   dual;

It returns:

Test Case 1 Test Case 2
----------- -----------
          4           5

Now, they can see the internal step-by-step values with this query:

COL msg FORMAT A30 HEADING "Internal Variable Auditing"
SELECT msg FROM debug;

It returns:

Internal Variable Auditing
------------------------------
Temporary Result Value: [4.49]
Temporary Return Value: [4]
Temporary Result Value: [4.51]
Temporary Return Value: [5]
 
4 rows selected.

What we learn is that:

• Oracle PL/SQL up-casts the b variable from an integer to a double precision data type before adding the three input variables.
• Oracle PL/SQL down-casts the sum of the three input variables from a double precision data type to an integer by applying traditionally rounding.

I hope this helps those trying to understand implicit casting and discovering how to unhide an opaque function’s operations for debugging purposes.