Category: Table Functions

This post is another example Compilation State Management with CSTORE values. It loads 10 strings of CSTORE values, attaches them to the cursor and then returns them as part of the eventual query. The difference from previous posts is this one retrieves the CSTORE values in bulk.

CREATE OR REPLACE PACKAGE poly_pkg AS
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T)
     RETURN DBMS_TF.DESCRIBE_T;
  PROCEDURE fetch_rows;
END;

The package body.

CREATE OR REPLACE PACKAGE BODY poly_pkg AS

  /*----------------------------------------------------*/
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T)
           RETURN DBMS_TF.DESCRIBE_T AS
  /*----------------------------------------------------*/
    v_new_col1 DBMS_TF.COLUMN_METADATA_T;
    v_new_cols DBMS_TF.COLUMNS_NEW_T;
    v_chr_t DBMS_TF.CSTORE_CHR_T;
  BEGIN
    p_tbl.column(1).pass_through := FALSE;
    v_new_col1 := DBMS_TF.COLUMN_METADATA_T(
          type    => DBMS_TF.TYPE_VARCHAR2,
          name    => 'CHR_T',
          max_len => 15 );
    v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col1 );
    --
    -- Load 10 string values into the CSTORE_CHR_T
    -- table and then...
    --
    FOR counter IN 1..10 LOOP
      v_chr_t(counter) := 'CHR ' || counter;
    END LOOP;
    --
    -- ...attach it to the cursor
    ---
    RETURN(DBMS_TF.DESCRIBE_T( row_replication => TRUE,
                               cstore_chr => v_chr_t,
                               new_columns => v_new_cols ));
  END;

  /*----------------------------------------------------*/
  PROCEDURE fetch_rows IS
  /*----------------------------------------------------*/
    v_rowset DBMS_TF.ROW_SET_T;
    v_added DBMS_TF.COLUMN_DATA_T;
    v_chr_t DBMS_TF.CSTORE_CHR_T;
  BEGIN
    --
    -- Retrieve the CSTORE_CHR_T values in bulk
    -- attached during the DESCRIBE call...
    DBMS_TF.CSTORE_GET(v_chr_t);
    IF NVL(v_chr_t.COUNT,0) > 0 THEN
      --
      -- Set the replication factor to the
      -- number of rows in the CSTORE_CHR_T table...
      DBMS_TF.ROW_REPLICATION(v_chr_t.COUNT);
      --
      -- add each STORE_CHR_T row to the rowset...
      FOR counter IN 1..v_chr_t.COUNT LOOP
        v_added.tab_varchar2(counter) := v_chr_t(counter);
      END LOOP;
    ELSE
      DBMS_TF.ROW_REPLICATION(0);
    END IF;
    -- and attach new rowset to the main one
    v_rowset(1) := v_added;
    DBMS_TF.PUT_ROW_SET( rowset => v_rowset);
  END;

END;

The function:

CREATE OR REPLACE FUNCTION poly_func( p_tbl IN TABLE )
  RETURN TABLE PIPELINED
  ROW POLYMORPHIC USING poly_pkg;

The query and results.

SELECT *
  FROM poly_func(dual);
CHR_T
---------------
CHR 1
CHR 2
CHR 3
CHR 4
CHR 5
CHR 6
CHR 7
CHR 8
CHR 9
CHR 10

10 rows selected.

Thanks for reading.

Just how long is forever? In the case of the “one minute CSTORE cursor” from the previous post it’s as long as the cursor remains parsed. A full discussion of cursor parsing and retention is well beyond the scope of this article so we’ll just define it as forever.

As a matter of fact I kept trying the query for 6 straight days while the database was up! Well, that’s not true – I took an hour off for a delicious dinner. That’s not true either – the dinner was not delicious.

Here are the results 6 days later!

-- That cursor still has the same OPEN date
-- so the error is still raised
SELECT *
  FROM poly_func(table1)
SELECT *
*
ERROR at line 1:
ORA-20000: Cursor is too old
ORA-06512: at "D.POLY_PKG", line 34
OPEN 20190203 15463
NOW  20190209 133517

-- A new WHERE clause creates a new cursor so that
-- works...for one minute at least.
SELECT *
  FROM poly_func(table1)
 WHERE col1 = 19;
      COL1       COL2       COL3
---------- ---------- ----------
        19         99          9
        19         98          8
DESCRIBE 09-FEB-19
OPEN 20190209 133553
NOW  20190209 133553

-- The original still fails
SELECT *
  FROM poly_func(table1);
SELECT *
*
ERROR at line 1:
ORA-20000: Cursor is too old
ORA-06512: at "D.POLY_PKG", line 34
OPEN 20190203 15463
NOW  20190209 133604

And for the sake of clarity I re-ran the XSTORE cursor on ten thousand rows. Here are the abbreviated results confirming the XSTORE values increasing with each rowset but always starting at 11 because the XSTORE values clean out after the PTF executes.

SELECT *
  FROM poly_func(table1);

      COL1       COL2       COL3
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
...
      9999       9999       9999
     10000      10000      10000
10000 rows selected.
11
12
13
14
15
16
17
18
19
20

SELECT *
  FROM poly_func(table1);

      COL1       COL2       COL3
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
...
      9999       9999       9999
     10000      10000      10000
10000 rows selected.
11
12
13
14
15
16
17
18
19
20

WITH x AS ( SELECT *
              FROM poly_func(table1) )
SELECT *
  FROM poly_func(x);

      COL1       COL2       COL3
---------- ---------- ----------
         1          1          1
         2          2          2
...
      9999       9999       9999
     10000      10000      10000
10000 rows selected.
11
11
12
12
13
13
14
14
15
15
16
16
17
17
18
18
19
19
20
20

Thanks for reading!

This post continues the discussion of XSTORE values with a demonstration of their life span and compares it to Compile State Management – aka CSTORE – values.

Here’s a simple FETCH_ROWS procedure to demonstrate the life span of XSTORE values.

PROCEDURE fetch_rows IS
  xstore_number NUMBER := 0;
BEGIN
  -- if any values are defined for the X1 key
  -- then retrieve the numeric one and increment
  -- it by 1
  -- if none are defined then add a numeric value
  -- of 11
  IF DBMS_TF.XSTORE_EXISTS('X1') THEN
    DBMS_TF.XSTORE_GET('X1',xstore_number);
    DBMS_TF.XSTORE_SET('X1',xstore_number + 1);
  ELSE
    DBMS_TF.XSTORE_SET('X1',11);
  END IF;
  DBMS_TF.XSTORE_GET('X1',xstore_number);
  DBMS_OUTPUT.PUT_LINE(xstore_number);
END;

And some queries:

-- query 1
SELECT *
  FROM poly_func(table1);
COL1
----
  19
  19
11

-- query 2
SELECT *
  FROM poly_func(table1);
COL1
----
  19
  19
11

-- query 3
WITH x AS ( SELECT *
              FROM poly_func(table1) )
SELECT *
  FROM poly_func(x);
COL1
----
  19
  19
11
11

Query 1 ran as expected – the X1 XSTORE did not exist so its numeric value was set to 11 and displayed. Query 2 also found that X1 did not exist because it was a whole new execution of the PTF.

The result of Query 3 shows that even WITH clauses retain their own distinct XSTORE because they are separate executions.

Lets compare that with Compile State Execution (CSTORE) values. Here is some example code to introduce CSTORE workings.

FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
  RETURN DBMS_TF.DESCRIBE_T AS
  v_dat DBMS_TF.CSTORE_DAT_T;
BEGIN
  --
  -- Save the current date with the cursor in a compile
  -- state variable (CSTORE) named PARSE_DATE which is
  -- attached directly to cursor
  --
  v_dat('PARSE DATE') := SYSDATE;
  DBMS_OUTPUT.PUT_LINE('DESCRIBE ' || 
                        v_dat('PARSE DATE'));
  RETURN DBMS_TF.DESCRIBE_T( cstore_dat => v_dat );
END;

PROCEDURE open IS
  v_parse_date DATE;
BEGIN
  DBMS_TF.CSTORE_GET('PARSE DATE',v_parse_date);
  -- output cursor PARSE_DATE and current date
  DBMS_OUTPUT.PUT_LINE('OPEN ' || 
       TO_CHAR(v_parse_date,'YYYYMMDD HH24MISS'));
  DBMS_OUTPUT.PUT_LINE('NOW  ' || 
       TO_CHAR(SYSDATE ,'YYYYMMDD HH24MISS'));
  -- if cursor was parsed more than 1 minute ago
  -- then fail
  IF v_parse_date < sysdate - ( 1 / 24 / 60 ) THEN
    RAISE_APPLICATION_ERROR(-20000,
                 'Cursor is too old');
  END IF;
END;

The important line to note is this:

RETURN DBMS_TF.DESCRIBE_T( cstore_dat => v_dat );

The DESCRIBE function returns the CSTORE values as attributes of the parsed cursor. Thus they stay with that cursor forever. Let’s run some queries to verify that.

-- Query 1 (works fine)
SELECT *
  FROM poly_func(table1);
COL1
----
  19
  19
DESCRIBE 03-FEB-19
OPEN 20190203 154635
NOW  20190203 154635

-- Query 2 (10 seconds after Query 1 works fine)
SELECT *
  FROM poly_func(table1)
COL1
----
  19
  19
OPEN 20190203 154635
NOW  20190203 154645

-- Query 3 (1 minute and 13 seconds after Query 1)
-- fails because we arbitrarily deemed the cursor
-  to be too old
SELECT *
  FROM poly_func(table1)
SELECT *
*
ERROR at line 1:
ORA-20000: Cursor is too old
ORA-06512: at "D.POLY_PKG", line 34
OPEN 20190203 154635
NOW  20190203 154748

The CSTORE value of PARSE DATE stayed with the cursor through each execution. After 1 minute the query will not run again.

The next few articles will discuss State Management (CSTORE and XSTORE) with examples that are more realistic than this one.

Thanks for reading.