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.

CSTORE, XSTORE, Packages And Sessions 3

In the Oracle documentation about Compilation State Management I found this rather stern passage:

The PTF author should not attempt to use the session state (such as PL/SQL package global variables) to store any compilation state. Problems can arise because in a given session all cursors using the PTF will share that state, and other sessions executing the PTF cursor will not see the original compilation state.

In the last two posts in this series I demonstrated that session state refers to current values within an Oracle session. The best example of this is package global variables. I also demonstrated that compilation state is how things looked when the DESCRIBE function was first executed. This remains steadfast across any session that uses the PTF.

Here is a simple example using a global package variable.

-- a very simple package with a single
-- public variable stored in SESSION STATE
CREATE OR REPLACE PACKAGE other_pkg AS
  v_other_number NUMBER := 1;
END;

And a DESCRIBE function checking the public variable in the package. If its value is greater than 1 then an error is raised. At this point the value is 1 so that becomes part of the Compilation State.

The OPEN procedure displays the value of the public variable.

FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
  RETURN DBMS_TF.DESCRIBE_T AS
BEGIN
  IF other_pkg.v_other_number > 1 THEN
    RAISE_APPLICATION_ERROR(-20000,'Other > 1');
  ELSE
   RETURN NULL;
  END IF;
END;

PROCEDURE open IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Other = ' ||
                        other_pkg.v_other_number);
END;

I’ve left out the rest of the PTF code for the sake of simplicity. Here’s what executions produce.

-- first query returns rows and display the
-- public variable as 1
SELECT *
  FROM qz_poly_func(qz_table);
COL1       COL2
---------- ----------
        10        100
        20        200
        30        300
Other = 1
 
-- set the public variable to 99 which is
-- greater than 1 so PTF should fail now?
BEGIN
  other_pkg.v_other_number := 99;
END;

-- NO. The compilation state still sees the
-- value as 1
SELECT *
  FROM qz_poly_func(qz_table);
COL1       COL2
---------- ----------
        10        100
        20        200
        30        300
Other = 99

The compilation state will not change until a parse forces the DESCRIBE function to execute throwing the error I hard coded into the PTF.

SELECT *
  FROM qz_poly_func(qz_table)
FROM qz_poly_func(qz_table)
*
ERROR at line 2:
ORA-62565: The Describe method failed with error(s).
ORA-20000: Other > 1
ORA-06512: at "D.QZ_POLY_PKG", line 14
ORA-06512: at line 22

In a future set of posts I’ll delve further into what causes a PTF parse/describe to happen.

Thanks for reading!

CSTORE, XSTORE, Packages And Sessions 2

The previous post began a discussion of how CSTORE, XSTORE and package values behave within an Oracle session. As a starting point it only included the DESCRIBE function of the PTF which only executes when the cursor is parsed. Thus it only displays values for the very first PTF run and then never again as the cursor is reused.

This post expands things to include the OPEN, FETCH_ROWS and CLOSE procedures which execute for each and every PTF run. The code for the OPEN, FETCH_ROWS and CLOSE procedures is the same – here it is:

PROCEDURE open IS

  v_num_t DBMS_TF.CSTORE_NUM_T;
  v_xstore_number NUMBER;

BEGIN

  -- show the values at the start of the call for the:
  --    1) package - session specific values
  --    2) CSTORE  - cursor specific values
  --    3) XSTORE  - PTF execution specific values
  --
  show_value(DBMS_TF.OPEN,'PACKAGE',other_pkg.other_number);
  DBMS_TF.CSTORE_GET(v_num_t);
  show_value(DBMS_TF.OPEN,'CSTORE ',v_num_t(1));

  DBMS_TF.XSTORE_GET('XSTORE',v_xstore_number);
  show_value(DBMS_TF.OPEN,'XSTORE ',v_xstore_number);

  -- increment that values by 1 
  set_or_increment(DBMS_TF.OPEN);

END;

And here is the display from the very first run in the very first session.

DESCRIBE   PACKAGE 1
DESCRIBE   CSTORE  1
DESCRIBE   XSTORE  N/A
OPEN       PACKAGE 2
OPEN       CSTORE  1
OPEN       XSTORE
FETCH_ROWS PACKAGE 3
FETCH_ROWS CSTORE  1
FETCH_ROWS XSTORE  1
CLOSE      PACKAGE 4
CLOSE      CSTORE  1
CLOSE      XSTORE  2

And the explanation:

  • DESCRIBE
    • PACKAGE = 1 because the package is initialized in the session
    • CSTORE = 1 because the cursor was parsed
    • XSTORE = N/A because it is not available in the DESCRIBE function
  • OPEN
    • PACKAGE = 2 because it was incremented by prior DESCRIBE
    • CSTORE = 1 because it was set in the DESCRIBE
    • XSTORE is empty because it has not been set yet
  • FETCH_ROWS
    • PACKAGE = 3 because it was incremented by prior OPEN
    • CSTORE = 1 because it was set in the DESCRIBE
    • XSTORE = 1 because it was set in the OPEN
  • CLOSE
    • PACKAGE = 4 because it was incremented by prior FETCH_ROWS
    • CSTORE = 1 (still) because it was set in the DESCRIBE
    • XSTORE = 3 because it was incremented in the prior FETCH_ROWS

The second execution shows this result:

OPEN       PACKAGE 5
OPEN       CSTORE  1
OPEN       XSTORE
FETCH_ROWS PACKAGE 6
FETCH_ROWS CSTORE  1
FETCH_ROWS XSTORE  1
CLOSE      PACKAGE 7
CLOSE      CSTORE  1
CLOSE      XSTORE  2

No parse was required so no DESCRIBE values are shown. The package values continue to increment because they are session specific and the XSTORE values start again at ’empty’ because a new PTF execution occurs.

In another Oracle session the first PTF call produces these results:

OPEN       PACKAGE 1
OPEN       CSTORE  1
OPEN       XSTORE
FETCH_ROWS PACKAGE 2
FETCH_ROWS CSTORE  1
FETCH_ROWS XSTORE  1
CLOSE      PACKAGE 3
CLOSE      CSTORE  1
CLOSE      XSTORE  2

No parse – so no DESCRIBE. The package values start at 1 because this session gets its own compiled version of the package. The XSTORE values start at ’empty’ because its a brand new PTF execution.

One last point – if the underlying table had more than 1,024 rows then the FETCH_ROWS portion of the results would display like this:

FETCH_ROWS PACKAGE 3
FETCH_ROWS CSTORE  1
FETCH_ROWS XSTORE  1
FETCH_ROWS PACKAGE 4
FETCH_ROWS CSTORE  1
FETCH_ROWS XSTORE  2
FETCH_ROWS PACKAGE 5
FETCH_ROWS CSTORE  1
FETCH_ROWS XSTORE  3

The package values increment as do the XSTORE values. The poor CSTORE values remain statish because they are geheftet to the cursor.

To summarize:

  • Package values are Oracle session specific
  • CSTORE values are PTF cursor specific
  • XSTORE values are PTF execution specific

Thanks for reading!