This post begins the discussion of PTF Execution State Management. That’s a fancy way of describing the state of an executing PTF – specifically the OPEN, FETCH_ROWS and CLOSE procedures. And the state is not really managed – its simply recorded in KEY-VALUE (think JSON) pairs called XSTORE’s.
Here is a simple example in a FETCH_ROWS procedure that does the following:
- Saves an XSTORE numeric value of 0
- For every row fetched (3 in this case)
- Assigns the XSTORE numeric value to a column in the row set
- Increments the XSTORE numeric value
PROCEDURE fetch_rows IS v_row_set DBMS_TF.ROW_SET_T; v_xstore_col DBMS_TF.COLUMN_DATA_T; v_env DBMS_TF.ENV_T; v_xstore NUMBER; BEGIN -- if nothing exists for key ABC then -- add a numeric value of zero IF NOT DBMS_TF.XSTORE_EXISTS('ABC') THEN DBMS_TF.XSTORE_SET('ABC',0); END IF; v_env := DBMS_TF.GET_ENV; -- for every row... FOR counter IN 1..v_env.row_count LOOP -- get the value for the ABC numeric key and add it -- to the rowset DBMS_TF.XSTORE_GET('ABC',v_xstore); v_xstore_col.tab_varchar2(counter) := v_xstore; -- increment the ABC numeric value by 1 DBMS_TF.XSTORE_SET('ABC',v_xstore + 1); END LOOP; -- every row -- kram the local rowset to the main one v_row_set(1) := v_xstore_col; DBMS_TF.PUT_ROW_SET(v_row_set); END;
And here is the output.
SELECT * FROM poly_func(table1); XSTORE --------------- 0 1 2
The next few posts will delve into XSTORE values more.
Thanks for reading.