implestrat.com

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.

This post is another example Compilation State Management with CSTORE values. It loads 10 strings of CSTORE values, attaches them to the cursor and then returns them as part of the eventual query. The difference from previous posts is this one retrieves the CSTORE values in bulk.

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

The package body.

CREATE OR REPLACE PACKAGE BODY poly_pkg AS

  /*----------------------------------------------------*/
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T)
           RETURN DBMS_TF.DESCRIBE_T AS
  /*----------------------------------------------------*/
    v_new_col1 DBMS_TF.COLUMN_METADATA_T;
    v_new_cols DBMS_TF.COLUMNS_NEW_T;
    v_chr_t DBMS_TF.CSTORE_CHR_T;
  BEGIN
    p_tbl.column(1).pass_through := FALSE;
    v_new_col1 := DBMS_TF.COLUMN_METADATA_T(
          type    => DBMS_TF.TYPE_VARCHAR2,
          name    => 'CHR_T',
          max_len => 15 );
    v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col1 );
    --
    -- Load 10 string values into the CSTORE_CHR_T
    -- table and then...
    --
    FOR counter IN 1..10 LOOP
      v_chr_t(counter) := 'CHR ' || counter;
    END LOOP;
    --
    -- ...attach it to the cursor
    ---
    RETURN(DBMS_TF.DESCRIBE_T( row_replication => TRUE,
                               cstore_chr => v_chr_t,
                               new_columns => v_new_cols ));
  END;

  /*----------------------------------------------------*/
  PROCEDURE fetch_rows IS
  /*----------------------------------------------------*/
    v_rowset DBMS_TF.ROW_SET_T;
    v_added DBMS_TF.COLUMN_DATA_T;
    v_chr_t DBMS_TF.CSTORE_CHR_T;
  BEGIN
    --
    -- Retrieve the CSTORE_CHR_T values in bulk
    -- attached during the DESCRIBE call...
    DBMS_TF.CSTORE_GET(v_chr_t);
    IF NVL(v_chr_t.COUNT,0) > 0 THEN
      --
      -- Set the replication factor to the
      -- number of rows in the CSTORE_CHR_T table...
      DBMS_TF.ROW_REPLICATION(v_chr_t.COUNT);
      --
      -- add each STORE_CHR_T row to the rowset...
      FOR counter IN 1..v_chr_t.COUNT LOOP
        v_added.tab_varchar2(counter) := v_chr_t(counter);
      END LOOP;
    ELSE
      DBMS_TF.ROW_REPLICATION(0);
    END IF;
    -- and attach new rowset to the main one
    v_rowset(1) := v_added;
    DBMS_TF.PUT_ROW_SET( rowset => v_rowset);
  END;

END;

The function:

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

The query and results.

SELECT *
  FROM poly_func(dual);
CHR_T
---------------
CHR 1
CHR 2
CHR 3
CHR 4
CHR 5
CHR 6
CHR 7
CHR 8
CHR 9
CHR 10

10 rows selected.

Thanks for reading.