implestrat.com

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!

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!

Recent posts in this series discussed Compile State Management (CSTORE) and Execution State Management (XSTORE) in Polymorphic Table Functions (PTF’s). The XSTORE and CSTORE values are managed by the DBMS_TF package. This is a good time to compare them to values stored in other packages – specifically how they behave within an Oracle session.

I’ll start by creating a simple package with one single value.

CREATE OR REPLACE PACKAGE other_pkg IS
  other_number NUMBER := 1;
END;

Next up is the PTF package.

CREATE OR REPLACE PACKAGE poly_pkg AS
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
     RETURN DBMS_TF.DESCRIBE_T;
END;

And the package body.

CREATE OR REPLACE PACKAGE BODY poly_pkg AS

  /*---------------------------------------------*/
  PROCEDURE show_value ( p_source VARCHAR2,
                         p_type   VARCHAR2,
                         p_string VARCHAR2 ) IS
  /*---------------------------------------------*/
  BEGIN
    -- this procedure displays 3 values it is passed
    DBMS_OUTPUT.PUT_LINE( p_source || ' ' ||
                          p_type || ' ' ||
                          p_string);
  END;

  /*-------------------------------------------------*/
  PROCEDURE set_or_increment ( p_source VARCHAR2 ) IS
  /*-------------------------------------------------*/
    v_num_t DBMS_TF.CSTORE_NUM_T;
    v_xstore_number NUMBER;
  BEGIN

    -- This procedure increments values
    -- if they exist.
    -- If they dont exist then it sets them to 1
    -- and then increments them
    other_pkg.other_number := other_pkg.other_number + 1;

    -- if called by an execution procedure then...
    IF p_source IN ( DBMS_TF.OPEN,
                     DBMS_TF.CLOSE,
                     DBMS_TF.FETCH_ROWS ) THEN

      -- get the CSTORE numeric value and increment it
      -- thats all we can do here because CSTORE values
      -- can only be saved in a DESCRIBE
      DBMS_TF.CSTORE_GET(v_num_t);
      v_num_t(1) := v_num_t(1) + 1;

      -- if the XSTORE value does not exist then
      -- set it to 1
      IF NOT DBMS_TF.XSTORE_EXISTS('XSTORE') THEN
        DBMS_TF.XSTORE_SET('XSTORE',1);
      END IF;
  
      -- get, increment and set the XSTORE value
      DBMS_TF.XSTORE_GET('XSTORE',v_xstore_number);
      v_xstore_number := v_xstore_number + 1;
      DBMS_TF.XSTORE_SET('XSTORE',v_xstore_number);

   END IF;

  END;

  /*-------------------------------------------------*/
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
     RETURN DBMS_TF.DESCRIBE_T AS
  /*-------------------------------------------------*/
    v_num_t DBMS_TF.CSTORE_NUM_T;
  BEGIN
    v_num_t(1) := 1;
    -- display the values
    show_value('DESCRIBE','PACKAGE',other_pkg.other_number);
    show_value('DESCRIBE','CSTORE ',v_num_t(1));
    -- XSTORE values are not available in the DESCRIBE
    show_value('DESCRIBE','XSTORE ','N/A');
    set_or_increment('DESCRIBE');
    RETURN(DBMS_TF.DESCRIBE_T( cstore_num => v_num_t ));
  END;

END;

And the function.

CREATE OR REPLACE FUNCTION poly_func(p_tbl IN TABLE )
  RETURN TABLE PIPELINED
  ROW POLYMORPHIC USING poly_pkg;

And then run queries in Oracle session 1.

-- first query
SELECT *
  FROM poly_func(table1);

      COL1       COL2       COL3
---------- ---------- ----------
        19         99          9
        19         98          8

DESCRIBE PACKAGE 1
DESCRIBE CSTORE 1
DESCRIBE XSTORE N/A

-- second query
SELECT *
  FROM poly_func(table1);

      COL1       COL2       COL3
---------- ---------- ----------
        19         99          9
        19         98          8

No values are displayed for the second query because it re-uses the parsed query from the first execution.

And now in another Oracle session execute queries:

-- Query one
SELECT *
  FROM poly_func(table1);

      COL1       COL2       COL3
---------- ---------- ----------
        19         99          9
        19         98          8

-- Query two
SELECT *
  FROM poly_func(table1);

      COL1       COL2       COL3
---------- ---------- ----------
        19         99          9
        19         98          8

No values are displayed for the second query because it re-uses the parsed query from the first execution in session 1.

In the next post I’ll instrument the OPEN, FETCH_ROWS and CLOSE procedures as well.

OPEN, FETCH_ROWS and CLOSE Method Names Constants

The code above used these constants to ensure it executed within an OPEN, FETCH_ROWS or CLOSE call. Here is a simple demonstration of how they display.

BEGIN
  DBMS_OUTPUT.PUT_LINE(DBMS_TF.OPEN);
  DBMS_OUTPUT.PUT_LINE(DBMS_TF.CLOSE);
 DBMS_OUTPUT.PUT_LINE(DBMS_TF.FETCH_ROWS);
END;

OPEN
CLOSE
FETCH_ROWS

Thanks for reading.