implestrat.com

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.

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.

The previous 2 posts demonstrated options for row replication at the PTF or row set level. This post demonstrates the third option – setting replication at the row level.

First up is some demo data.

CREATE TABLE table1
( col1 NUMBER NOT NULL PRIMARY KEY );

INSERT INTO table1
VALUES(2018);

INSERT INTO table1
VALUES(2019);

INSERT INTO table1
VALUES(2020);

Most of code (package, function, etc) for this post is borrowed from the prior post except for the FETCH_ROWS procedure. Here’s the first version of it introducing the TAB_NATURALN_T datatype to store replication values for each row in the data set. In this case we set the replication as follows:

• 1 for the first row (2018)
• 2 for the second row (2019)
• 0 for the third row (2020)

PROCEDURE fetch_rows IS
  v_row_set DBMS_TF.ROW_SET_T;
  v_rep_tab DBMS_TF.TAB_NATURALN_T;
BEGIN
  v_rep_tab(1) := 1;
  v_rep_tab(2) := 2;
  v_rep_tab(3) := 0;
  DBMS_TF.GET_ROW_SET( rowset => v_row_set );
  DBMS_TF.PUT_ROW_SET( rowset => v_row_set,
                       replication_factor => v_rep_tab );
END;

And here are the results.

SELECT *
  FROM poly_func(table1);
COL1
----
2018
2019
2019
3 rows selected.

2018 replicated one time, 2019 replicated two times and 2020 replicated zero times.

Next up is:

• 1 for the first row (2018)
• 2 for the second row (2019)
• no value for the third row (2020)

PROCEDURE fetch_rows IS
  v_row_set DBMS_TF.ROW_SET_T;
  v_rep_tab DBMS_TF.TAB_NATURALN_T;
BEGIN
  v_rep_tab(1) := 1;
  v_rep_tab(2) := 2;
  DBMS_TF.GET_ROW_SET( rowset => v_row_set );
  DBMS_TF.PUT_ROW_SET( rowset => v_row_set,
                       replication_factor => v_rep_tab );
END;

And the results:

SELECT *
  FROM poly_func(table1);
COL1
----
2018
2019
2019
2020
4 rows selected.

2018 replicated ones time, 2019 replicated two times and 2020 replicated one time.

And here is where things get interesting:

• 1 for the first row (2018)
•  no value for the second row (2019)
• 3 for the third row (2020)

PROCEDURE fetch_rows IS
  v_row_set DBMS_TF.ROW_SET_T;
  v_rep_tab DBMS_TF.TAB_NATURALN_T;
BEGIN
  v_rep_tab(1) := 1;
  v_rep_tab(3) := 3;
  DBMS_TF.GET_ROW_SET( rowset => v_row_set );
  DBMS_TF.PUT_ROW_SET( rowset => v_row_set,
                       replication_factor => v_rep_tab );
END;

And the surprising results…

SELECT *
  FROM poly_func(table1);
COL1
----
2018
2019
2020
3 rows selected.

It appears that a gap in the entries in the natural table causes the replication factor to be ignored altogether producing one of each row. I’m not sure if that is a bug or the intended behaviour.

Thanks for reading!