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!

Leave a Reply

Your email address will not be published. Required fields are marked *

1 + 3 =