PTF’s For Totals 2 – Hidden Errors

The previous post introduced PTF code to calculate totals for numeric columns. It’s pretty straightforward but has two flaws lurking within that I’ll cover in this post.

The first flaw produces incorrect results even though all of the code executes while the other produces incorrect results and causes code to be skipped.

First I’ll query from the PTF to remind you of the display including the totals.

SELECT *
 FROM ptf_poly_func(ptf_table);
COL1 COL2
---- ----
  10  100
  20  200
  30  300
"COL1" total is 60
"COL2" total is 600

And query from the underlying table.

SELECT *
  FROM ptf_table;
COL1 COL2
---- ----
  10  100
  20  200
  30  300

Incorrect Result – Full Code Execution

This is easy to demonstrate with NULL values. I’ll set one of the col2 values to NULL.

UPDATE ptf_table
SET col2 = NULL
WHERE col1 = 20;

And select from the PTF again.

SELECT *
  FROM ptf_poly_func(ptf_table);
COL1 COL2
---- ----
  10  100
  20
  30  300
"COL1" total is 60
"COL2" total is

Notice the label for the COL2 total is there but the total is not.  That’s because if a NULL value is added to another value the result is NULL. So when the second record is processed it adds NULL to the total which makes it NULL. Then 300 is added to NULL which makes it even NULL’er. All of the code in the function executes fine though.

The fix is to change this line:

v_number := v_number
      + v_row_set(col_counter).tab_number(row_counter);

To this:

v_number := v_number 
  + NVL(v_row_set(col_counter).tab_number(row_counter),0);

This ensures a value of zero is returned instead of a NULL.

Incorrect Results – Missed Code

This is best demonstrated by changing the datatype of COL1 to DATE.

CREATE TABLE ptf_table
( col1 DATE,
  col2 NUMBER );

BEGIN
  FOR x IN 1..3 LOOP
    INSERT INTO ptf_table
    VALUES(SYSDATE + x,x * 100);
  END LOOP;
END;

Now query the PTF.

SELECT *
  FROM ptf_poly_func(ptf_table);
COL1      COL2
--------- ----
03-APR-19  100
04-APR-19  200
05-APR-19  300
"COL1" total is 0

Both the label for the COL2 total and the total itself are not there. This occurs because an un-handled exception was thrown in the FETCH_ROWS procedure. The exception was not trapped so the PTF returned the results it had up to that point. So where is the exception – let’s step through the code to find out. We know the XSTORE for COL1 was set to zero so these lines of code executed:

IF NOT DBMS_TF.XSTORE_EXISTS(v_get_cols(col_counter).name)
  THEN
    DBMS_TF.XSTORE_SET(v_get_cols(col_counter).name,0);
END IF;
DBMS_TF.XSTORE_GET(v_get_cols(col_counter).name,v_number);

Let’s add en exception handler to the next line to see if it is the problem.

BEGIN
  v_number := v_number + 
     v_row_set(col_counter).tab_number(row_counter),0);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20000,sqlcode);
END;

This does raise the exception.

ERROR at line 1:
ORA-20000: 100
ORA-06512: at "D.QZ_POLY_PKG", line 60

The most troublesome part of this error is that every line of code after it in the original code did not execute even though a result was returned.

So what is wrong with this line of code?

-- Thankfully the problem code identified itself by
-- turning bold red! 
v_number := v_number + 
   v_row_set(col_counter).tab_number(row_counter);

COL1 is DATE so its values will be in the TAB_DATE structure not TAB_NUMBER. So accessing the empty TAB_NUMBER structure resulted in a NO_DATA_FOUND exception

The fix is to check if number values exist like this:

-- if nothing exists in TAB_NUMBER then this is not
-- a NUMBER field so do not add to a total
IF v_row_set(col_counter).tab_number.EXISTS(row_counter)
   THEN v_number := v_number
   + NVL(v_row_set(col_counter).tab_number(row_counter),0);
END IF;

Be very careful of this when coding, testing and debugging PTF’s.

Thanks for reading!