XSTORE And CSTORE 2

Just how long is forever? In the case of the “one minute CSTORE cursor” from the previous post it’s as long as the cursor remains parsed. A full discussion of cursor parsing and retention is well beyond the scope of this article so we’ll just define it as forever.

As a matter of fact I kept trying the query for 6 straight days while the database was up! Well, that’s not true – I took an hour off for a delicious dinner. That’s not true either – the dinner was not delicious.

Here are the results 6 days later!

-- That cursor still has the same OPEN date
-- so the error is still raised
SELECT *
  FROM poly_func(table1)
SELECT *
*
ERROR at line 1:
ORA-20000: Cursor is too old
ORA-06512: at "D.POLY_PKG", line 34
OPEN 20190203 15463
NOW  20190209 133517

-- A new WHERE clause creates a new cursor so that
-- works...for one minute at least.
SELECT *
  FROM poly_func(table1)
 WHERE col1 = 19;
      COL1       COL2       COL3
---------- ---------- ----------
        19         99          9
        19         98          8
DESCRIBE 09-FEB-19
OPEN 20190209 133553
NOW  20190209 133553

-- The original still fails
SELECT *
  FROM poly_func(table1);
SELECT *
*
ERROR at line 1:
ORA-20000: Cursor is too old
ORA-06512: at "D.POLY_PKG", line 34
OPEN 20190203 15463
NOW  20190209 133604

And for the sake of clarity I re-ran the XSTORE cursor on ten thousand rows. Here are the abbreviated results confirming the XSTORE values increasing with each rowset but always starting at 11 because the XSTORE values clean out after the PTF executes.

SELECT *
  FROM poly_func(table1);

      COL1       COL2       COL3
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
...
      9999       9999       9999
     10000      10000      10000
10000 rows selected.
11
12
13
14
15
16
17
18
19
20

SELECT *
  FROM poly_func(table1);

      COL1       COL2       COL3
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
...
      9999       9999       9999
     10000      10000      10000
10000 rows selected.
11
12
13
14
15
16
17
18
19
20
WITH x AS ( SELECT *
              FROM poly_func(table1) )
SELECT *
  FROM poly_func(x);

      COL1       COL2       COL3
---------- ---------- ----------
         1          1          1
         2          2          2
...
      9999       9999       9999
     10000      10000      10000
10000 rows selected.
11
11
12
12
13
13
14
14
15
15
16
16
17
17
18
18
19
19
20
20

Thanks for reading!

XSTORE And CSTORE

This post continues the discussion of XSTORE values with a demonstration of their life span and compares it to Compile State Management – aka CSTORE – values.

Here’s a simple FETCH_ROWS procedure to demonstrate the life span of XSTORE values.

PROCEDURE fetch_rows IS
  xstore_number NUMBER := 0;
BEGIN
  -- if any values are defined for the X1 key
  -- then retrieve the numeric one and increment
  -- it by 1
  -- if none are defined then add a numeric value
  -- of 11
  IF DBMS_TF.XSTORE_EXISTS('X1') THEN
    DBMS_TF.XSTORE_GET('X1',xstore_number);
    DBMS_TF.XSTORE_SET('X1',xstore_number + 1);
  ELSE
    DBMS_TF.XSTORE_SET('X1',11);
  END IF;
  DBMS_TF.XSTORE_GET('X1',xstore_number);
  DBMS_OUTPUT.PUT_LINE(xstore_number);
END;

And some queries:

-- query 1
SELECT *
  FROM poly_func(table1);
COL1
----
  19
  19
11

-- query 2
SELECT *
  FROM poly_func(table1);
COL1
----
  19
  19
11

-- query 3
WITH x AS ( SELECT *
              FROM poly_func(table1) )
SELECT *
  FROM poly_func(x);
COL1
----
  19
  19
11
11

Query 1 ran as expected – the X1 XSTORE did not exist so its numeric value was set to 11 and displayed. Query 2 also found that X1 did not exist because it was a whole new execution of the PTF.

The result of Query 3 shows that even WITH clauses retain their own distinct XSTORE because they are separate executions.

Lets compare that with Compile State Execution (CSTORE) values. Here is some example code to introduce CSTORE workings.

FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
  RETURN DBMS_TF.DESCRIBE_T AS
  v_dat DBMS_TF.CSTORE_DAT_T;
BEGIN
  --
  -- Save the current date with the cursor in a compile
  -- state variable (CSTORE) named PARSE_DATE which is
  -- attached directly to cursor
  --
  v_dat('PARSE DATE') := SYSDATE;
  DBMS_OUTPUT.PUT_LINE('DESCRIBE ' || 
                        v_dat('PARSE DATE'));
  RETURN DBMS_TF.DESCRIBE_T( cstore_dat => v_dat );
END;

PROCEDURE open IS
  v_parse_date DATE;
BEGIN
  DBMS_TF.CSTORE_GET('PARSE DATE',v_parse_date);
  -- output cursor PARSE_DATE and current date
  DBMS_OUTPUT.PUT_LINE('OPEN ' || 
       TO_CHAR(v_parse_date,'YYYYMMDD HH24MISS'));
  DBMS_OUTPUT.PUT_LINE('NOW  ' || 
       TO_CHAR(SYSDATE ,'YYYYMMDD HH24MISS'));
  -- if cursor was parsed more than 1 minute ago
  -- then fail
  IF v_parse_date < sysdate - ( 1 / 24 / 60 ) THEN
    RAISE_APPLICATION_ERROR(-20000,
                 'Cursor is too old');
  END IF;
END;

The important line to note is this:

RETURN DBMS_TF.DESCRIBE_T( cstore_dat => v_dat );

The DESCRIBE function returns the CSTORE values as attributes of the parsed cursor. Thus they stay with that cursor forever. Let’s run some queries to verify that.

-- Query 1 (works fine)
SELECT *
  FROM poly_func(table1);
COL1
----
  19
  19
DESCRIBE 03-FEB-19
OPEN 20190203 154635
NOW  20190203 154635

-- Query 2 (10 seconds after Query 1 works fine)
SELECT *
  FROM poly_func(table1)
COL1
----
  19
  19
OPEN 20190203 154635
NOW  20190203 154645

-- Query 3 (1 minute and 13 seconds after Query 1)
-- fails because we arbitrarily deemed the cursor
-  to be too old
SELECT *
  FROM poly_func(table1)
SELECT *
*
ERROR at line 1:
ORA-20000: Cursor is too old
ORA-06512: at "D.POLY_PKG", line 34
OPEN 20190203 154635
NOW  20190203 154748

The CSTORE value of PARSE DATE stayed with the cursor through each execution. After 1 minute the query will not run again.

The next few articles will discuss State Management (CSTORE and XSTORE) with examples that are more realistic than this one.

Thanks for reading.

Execution State Management 2

This post continues on PTF Execution State Management with further details on adding, retrieving and removing XSTORE KEY-VALUE pairs.

Each XSTORE allows up to 4 assigned values separated by datatype:

  • One VARCHAR2 value
  • One NUMBER value
  • One DATE value
  • One BOOLEAN value

The values are manipulated using the DBMS_TF package in the OPEN, FETCH_ROWS or CLOSE procedures as shown in these examples:

PROCEDURE open IS

  xstore_number NUMBER;
  xstore_vc2 VARCHAR2(30);
  xstore_bl BOOLEAN := FALSE;
  xstore_dt DATE;

BEGIN

  DBMS_OUTPUT.PUT_LINE('----- OPEN -----');

  -- Assign values to the key of X1
  -- One numeric value, one varchar2 value, one boolean
  -- and one date value - all for the key named X1
  DBMS_TF.XSTORE_SET('X1',1);
  DBMS_TF.XSTORE_SET('X1','A');
  DBMS_TF.XSTORE_SET('X1',TRUE);
  DBMS_TF.XSTORE_SET('X1',SYSDATE);

  -- retrieve each of the XSTORE values just assigned
  DBMS_TF.XSTORE_GET('X1',xstore_number);
  DBMS_TF.XSTORE_GET('X1',xstore_vc2);
  DBMS_TF.XSTORE_GET('X1',xstore_bl);
  DBMS_TF.XSTORE_GET('X1',xstore_dt);

  -- output the retrieved values
  DBMS_OUTPUT.PUT_LINE(xstore_number);
  DBMS_OUTPUT.PUT_LINE(xstore_vc2);
  IF xstore_bl THEN
    DBMS_OUTPUT.PUT_LINE('TRUE');
  END IF;
  DBMS_OUTPUT.PUT_LINE(xstore_dt);

END;

PROCEDURE fetch_rows IS
BEGIN

  DBMS_OUTPUT.PUT_LINE('----- FETCH -----');

  -- if any values of any type exist for the X1
  -- key then say so
  IF DBMS_TF.XSTORE_EXISTS('X1') THEN
    DBMS_OUTPUT.PUT_LINE('X1 EXISTS');
  END IF;

  -- if a numeric value exists for the X1 key then
  -- zeggen it
  IF DBMS_TF.XSTORE_EXISTS('X1',DBMS_TF.XSTORE_TYPE_NUMBER)
    THEN
      DBMS_OUTPUT.PUT_LINE('X1 NUMBER EXISTS');
  END IF;

END;

PROCEDURE close IS
BEGIN

  DBMS_OUTPUT.PUT_LINE('----- CLOSE -----');

  -- remove the numeric value for the X1 key and then
  -- check if it still exists - surprisingly it does not
  DBMS_TF.XSTORE_REMOVE('X1',DBMS_TF.XSTORE_TYPE_NUMBER);
  IF 
 NOT DBMS_TF.XSTORE_EXISTS('X1',DBMS_TF.XSTORE_TYPE_NUMBER)
  THEN
    DBMS_OUTPUT.PUT_LINE('X1 NUMBER NO LONGER EXISTS');
  END IF;

  -- does the A1 key still have a VARCHAR2 value?
  IF DBMS_TF.XSTORE_EXISTS('X1',DBMS_TF.XSTORE_TYPE_VARCHAR2)
  THEN
    DBMS_OUTPUT.PUT_LINE('X1 VC2 EXISTS');
  END IF;

  -- Clear all KEYS and ALL values from the XSTORE
  DBMS_TF.XSTORE_CLEAR;
  IF NOT DBMS_TF.XSTORE_EXISTS('X1') THEN
    DBMS_OUTPUT.PUT_LINE('X1 NO LONGER EXISTS');
  END IF;

END;

And the much awaited output.

SELECT *
  FROM poly_func(table1);
COL1
----------
2018
2019
2020

----- OPEN -----
1
A
TRUE
27-JAN-19
----- FETCH -----
X1 EXISTS
X1 NUMBER EXISTS
----- CLOSE -----
X1 NUMBER NO LONGER EXISTS
X1 VC2 EXISTS
X1 NO LONGER EXISTS

XSTORE calls are not available in the DESCRIBE function because it’s outside of the Execution State of the PTF. Thus this code…

FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
         RETURN DBMS_TF.DESCRIBE_T AS
BEGIN
  DBMS_TF.XSTORE_SET('X1',1);
  RETURN NULL;
END;

..fails with this error.

ORA-62562: The API XStore_Set can be called only during
execution time of a polymorphic table function.

Thanks for reading.