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.