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.