PTF’s For Totals 3 – Session Retention

This post progresses the PTF Totalling series with Cross PTF Retention (CPTFR). Note – I just made that up. It stores values in session memory for later retrieval. This post deals with the storing and a subsequent post will deal with the retrieving.

I’ll start with a package to maintain the totals in a PL/SQL Index-By-Table. The name of the column being totalled will be the index like this:

v_total('COL1') = 60
v_total('COL2') = 600

Here is the package header:

CREATE OR REPLACE PACKAGE totals AS

  PROCEDURE blank_totals;
  PROCEDURE add_total ( p_totalname VARCHAR2,
                        p_totalval NUMBER );
  PROCEDURE show_totals;

END;

And the package body:

CREATE OR REPLACE PACKAGE BODY totals AS

  TYPE total_t IS TABLE OF NUMBER
    INDEX BY VARCHAR2(100);
  v_total total_t;

  PROCEDURE blank_totals IS
  BEGIN
    -- not sure what to do here yet
    NULL;
  END;

  PROCEDURE add_total ( p_totalname VARCHAR2,
                        p_totalval NUMBER ) IS
  BEGIN
   -- if the total denoted by P_TOTALNAME is already
   -- there then add P_TOTALVAL to it otherwise
   -- start anew
   IF v_total.EXISTS(p_totalname) THEN
     v_total(p_totalname) := v_total(p_totalname) + p_totalval;
   ELSE
     v_total(p_totalname) := p_totalval;
   END IF;
  END;

  PROCEDURE show_totals IS
    v_element VARCHAR2(100);
  BEGIN
    v_element := v_total.FIRST;
    -- loop through every total and display it
    -- !! Thanks Bill !!
    LOOP
      EXIT WHEN v_element IS NULL;
      DBMS_OUTPUT.PUT_LINE(v_total(v_element));
      v_element := v_total.NEXT(v_element);
    END LOOP;
  END;

END;

Then add an OPEN procedure to the PTF to blank the totals.

PROCEDURE open IS
BEGIN
  totals.blank_totals;
END;

And a CLOSE procedure to display them.

PROCEDURE close IS
  v_env DBMS_TF.ENV_T;
  v_get_cols DBMS_TF.TABLE_METADATA_T;
  v_number NUMBER;
BEGIN
  v_env := DBMS_TF.GET_ENV;
  v_get_cols := v_env.get_columns;
  FOR col_counter IN 1..v_get_cols.COUNT LOOP
    IF DBMS_TF.XSTORE_EXISTS(v_get_cols(col_counter).name,
           DBMS_TF.XSTORE_TYPE_NUMBER) THEN
      DBMS_TF.XSTORE_GET(v_get_cols(col_counter).name,
           v_number);
      totals.add_total(v_get_cols(col_counter).name,
           v_number);
    END IF;
  END LOOP;
END;

And now to query the PTF.

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

And then show the totals.

EXEC totals.show_totals
60
600

Pretty straightforward. In the next post I’ll show how to display them in the query results.

Thanks for reading!

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!