Posted on

Execution State Management 1

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:

  1. Saves an XSTORE numeric value of 0
  2. For every row fetched (3 in this case)
    1. Assigns the XSTORE numeric value to a column in the row set
    2. 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.