PTF’s For Totals 1

This post begins a series on “close to real world” uses for Polymorphic Table Functions (PTF’s). It will include some of the material covered in previous posts and introduce some new concepts and gotcha’s to watch for.

The objective of the code I’ll use is to produce a total for all numeric columns it queries. The starting point is pretty straightforward so I’ll dive right in.

-- a demo table with 2 numeric columns
-- and 3 records
CREATE TABLE ptf_table
( col1 NUMBER,
  col2 NUMBER );

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

The package header with a DESCRIBE function, FETCH_ROWS and CLOSE procedure. The key points for each one are:

  • DESCRIBE
    • Make sure all columns are read (FOR_READ=TRUE)
  • FETCH_ROWS
    • Assembles the totals as follows:
      • Get the row set
        • For every column in the rowset
          • For every row in the rowset
            • Check if an XSTORE exists for the column. If not then create one set to zero
            • Get the XSTORE for the column and add the corresponding value from the row to it
            • Save the XSTORE value for the column
  • CLOSE
    • Output the XSTORE values using DBMS_OUTPUT.

XSTORE values are used because they exists throughout a PTF execution. I’ll create one per column using the column name as the key – so the ptf_table used in this example will create two XSTORE values, COL1 and COL2.

CREATE OR REPLACE PACKAGE ptf_poly_pkg AS

  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
           RETURN DBMS_TF.DESCRIBE_T;
  PROCEDURE fetch_rows;
  PROCEDURE close;

END;

And the package body.

CREATE OR REPLACE PACKAGE BODY ptf_poly_pkg AS

  /*------------------------------------------------*/
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
           RETURN DBMS_TF.DESCRIBE_T AS
  /*------------------------------------------------*/
  BEGIN
    -- make sure that all columns are read
    FOR counter IN 1..p_tbl.column.count LOOP
      p_tbl.column(counter).for_read := TRUE;
    END LOOP;
    RETURN NULL;
  END;

  /*------------------------------------------------*/ 
  PROCEDURE fetch_rows IS
  /*------------------------------------------------*/ 
    v_env      DBMS_TF.ENV_T;
    v_get_cols DBMS_TF.TABLE_METADATA_T;
    v_row_set  DBMS_TF.ROW_SET_T;
    v_number   NUMBER;
  BEGIN

    v_env := DBMS_TF.GET_ENV;
    v_get_cols := v_env.get_columns;

    DBMS_TF.GET_ROW_SET(v_row_set);

    -- for every get column...
    FOR col_counter IN 1..v_get_cols.COUNT LOOP

      -- for every row...
      FOR row_counter IN 1..v_env.row_count LOOP

        -- create the XSTORE if it does not
        -- already exist
        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;

        -- retrieve the XSTORE, add the fetched
        -- value to it and then re-save it
        DBMS_TF.XSTORE_GET(v_get_cols(col_counter).name,
                           v_number);
        v_number := v_number + 
             v_row_set(col_counter).tab_number(row_counter);
        DBMS_TF.XSTORE_SET(v_get_cols(col_counter).name,
                    v_number);

      END LOOP; -- every row

    END LOOP; -- every get column

  END;

  /*------------------------------------------------*/ 
  PROCEDURE close IS
  /*------------------------------------------------*/ 
    v_env      DBMS_TF.ENV_T;
    v_get_cols DBMS_TF.TABLE_METADATA_T;
    v_number   NUMBER;
  BEGIN
    --
    -- output the XSTORE values
    --
    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);
         DBMS_OUTPUT.PUT_LINE(v_get_cols(col_counter).name ||
              ' total is ' || v_number);
      END IF;
    END LOOP;
  END;

END;

And the function.

CREATE OR REPLACE FUNCTION ptf_poly_func(p_tbl IN TABLE )
    RETURN TABLE PIPELINED
    ROW POLYMORPHIC USING ptf_poly_pkg;

And the results.

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

Switch up the column order.

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

Query 1 column only.

SELECT col2
  FROM ptf_poly_func(ptf_table); 
COL2
----
 100
 200
 300 
"COL1" total is 60
"COL2" total is 600

Note the totals for both columns are calculated no matter what. I’ll work through that, find a better way to return the totals and explain some pitfalls in the next few posts.

Thanks for reading.