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!

Leave a Comment Cancel reply