Table Polymorphism Execution IDs

The Oracle documentation states that an Execution ID is “…a unique execution id that can be used by the PTF to index any cursor execution specific runtime state.” The previous post in about Table Polymorphism demonstrated that each partition of data got its own OPEN, FETCH_ROWS and CLOSE procedure. Let’s see if each one get’s its own execution ID as well.

First up – three amazing rows of demo data!

CREATE TABLE table1
( col1 NUMBER );

BEGIN
  FOR x IN 1..3 LOOP
    INSERT INTO table1
    VALUES(x);
  END LOOP;
END;

And the PTF package.

CREATE OR REPLACE PACKAGE poly_ptf AS

  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
    RETURN DBMS_TF.DESCRIBE_T;
  PROCEDURE open;
  PROCEDURE fetch_rows;
  PROCEDURE close;

END;

And the package body.

CREATE OR REPLACE PACKAGE BODY poly_ptf AS

  /*----------------------------------------------*/
  PROCEDURE display ( p VARCHAR2 ) IS
  /*----------------------------------------------*/
    v_x NUMBER;
  BEGIN
    -- a private procedure to display values and 
    -- get/set an XSTORE value
    DBMS_OUTPUT.PUT_LINE(p);
    IF NOT DBMS_TF.XSTORE_EXISTS('X',
                DBMS_TF.XSTORE_TYPE_NUMBER) THEN
      DBMS_TF.XSTORE_SET('X',1);
    END IF;
    DBMS_TF.XSTORE_GET('X',v_x);
    DBMS_TF.XSTORE_SET('X',v_x + 1);
    -- output the XSTORE value plus the Execution ID
    DBMS_TF.TRACE( msg => v_x,
                   with_id => TRUE);
  END;

  /*----------------------------------------------*/
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
  /*----------------------------------------------*/
    RETURN DBMS_TF.DESCRIBE_T IS
  BEGIN
    RETURN NULL;
  END; 

  /*----------------------------------------------*/
  PROCEDURE open IS
  /*----------------------------------------------*/
    v_x NUMBER;
  BEGIN
    display('OPEN');
  END;

  /*----------------------------------------------*/
  PROCEDURE fetch_rows IS
  /*----------------------------------------------*/
    v_rowset DBMS_TF.ROW_SET_T;
  BEGIN
    display('FETCH_ROWS');
  END;

  /*----------------------------------------------*/
  PROCEDURE close IS
  /*----------------------------------------------*/
  BEGIN
    display('CLOSE');
  END;

END;

And the PTF itself.

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

And a query with results.

SELECT *
  FROM poly_func(table1 PARTITION BY col1 );

COL1
----
   1
   2
   3
3 rows selected.

OPEN
1 <id=6504000000000000>
FETCH_ROWS
2 <id=6504000000000000>
CLOSE
3 <id=6504000000000000>
OPEN
1 <id=6604000000000000>
FETCH_ROWS
2 <id=6604000000000000>
CLOSE
3 <id=6604000000000000>
OPEN
1 <id=6704000000000000>
FETCH_ROWS
2 <id=6704000000000000>
CLOSE
3 <id=6704000000000000>

Each partition of COL1 values got its own execution ID (6504, 6604, 6704).

I’ll continue to expand on this in future posts.

Thanks!