CSTORE, XSTORE, Packages And Sessions

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.