Category: Table Functions

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!

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!

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.