implestrat.com

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!

All of the previous posts in this series relied on hand written code to group values together. Polymorphic Table Functions offer a way to do the grouping automatically. Its referred to as TABLE POLYMORPHISM and here is an introductory post.

First up – some demo data.

CREATE TABLE table1
( col1 NUMBER );

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

And a familiar 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 an equally familiar package body.

CREATE OR REPLACE PACKAGE BODY poly_ptf AS

  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
    RETURN DBMS_TF.DESCRIBE_T IS
  BEGIN
    FOR counter IN 1..p_tbl.column.count LOOP
      p_tbl.column(counter).for_read := TRUE;
    END LOOP;
    RETURN NULL;
  END;

  PROCEDURE open IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('open');
  END;

  PROCEDURE fetch_rows IS
    v_rowset DBMS_TF.ROW_SET_T;
  BEGIN
    DBMS_TF.GET_ROW_SET(v_rowset);
    DBMS_OUTPUT.PUT_LINE('fetch rows');
    DBMS_TF.TRACE(v_rowset);
  END;

  PROCEDURE close IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('close');
  END;

END;

And the function. Note that TABLE is specified instead of ROW. This means that rows passed into the PTF can be processed in groups. Notice that I said they can be – this is not a requirement, it is also not the default.

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

And now for the query. Notice that PARTITION BY is specified. This means that rows passed in will be grouped by their col1 values. So what does grouping mean? Check the results below.

SELECT *
  FROM poly_func(table1 PARTITION BY col1);

COL1
----
   1
   2
   3

open
fetch rows
.... [1] = {"COL1":1}
close
open
fetch rows
.... [1] = {"COL1":2}
close
open
fetch rows
.... [1] = {"COL1":3}
close

The OPEN, FETCH_ROWS and CLOSE procedures are executed separately (partitioned) for each of the values in COL1.

I’ll continue exploring this behaviour in future posts.

Thanks for reading!

This post progresses the PTF Totalling series with Cross PTF Retention (CPTFR). Note – I just made that up. It stores values in session memory for later retrieval. This post deals with the storing and a subsequent post will deal with the retrieving.

I’ll start with a package to maintain the totals in a PL/SQL Index-By-Table. The name of the column being totalled will be the index like this:

v_total('COL1') = 60
v_total('COL2') = 600

Here is the package header:

CREATE OR REPLACE PACKAGE totals AS

  PROCEDURE blank_totals;
  PROCEDURE add_total ( p_totalname VARCHAR2,
                        p_totalval NUMBER );
  PROCEDURE show_totals;

END;

And the package body:

CREATE OR REPLACE PACKAGE BODY totals AS

  TYPE total_t IS TABLE OF NUMBER
    INDEX BY VARCHAR2(100);
  v_total total_t;

  PROCEDURE blank_totals IS
  BEGIN
    -- not sure what to do here yet
    NULL;
  END;

  PROCEDURE add_total ( p_totalname VARCHAR2,
                        p_totalval NUMBER ) IS
  BEGIN
   -- if the total denoted by P_TOTALNAME is already
   -- there then add P_TOTALVAL to it otherwise
   -- start anew
   IF v_total.EXISTS(p_totalname) THEN
     v_total(p_totalname) := v_total(p_totalname) + p_totalval;
   ELSE
     v_total(p_totalname) := p_totalval;
   END IF;
  END;

  PROCEDURE show_totals IS
    v_element VARCHAR2(100);
  BEGIN
    v_element := v_total.FIRST;
    -- loop through every total and display it
    -- !! Thanks Bill !!
    LOOP
      EXIT WHEN v_element IS NULL;
      DBMS_OUTPUT.PUT_LINE(v_total(v_element));
      v_element := v_total.NEXT(v_element);
    END LOOP;
  END;

END;

Then add an OPEN procedure to the PTF to blank the totals.

PROCEDURE open IS
BEGIN
  totals.blank_totals;
END;

And a CLOSE procedure to display them.

PROCEDURE close IS
  v_env DBMS_TF.ENV_T;
  v_get_cols DBMS_TF.TABLE_METADATA_T;
  v_number NUMBER;
BEGIN
  v_env := DBMS_TF.GET_ENV;
  v_get_cols := v_env.get_columns;
  FOR col_counter IN 1..v_get_cols.COUNT LOOP
    IF DBMS_TF.XSTORE_EXISTS(v_get_cols(col_counter).name,
           DBMS_TF.XSTORE_TYPE_NUMBER) THEN
      DBMS_TF.XSTORE_GET(v_get_cols(col_counter).name,
           v_number);
      totals.add_total(v_get_cols(col_counter).name,
           v_number);
    END IF;
  END LOOP;
END;

And now to query the PTF.

SELECT *
  FROM ptf_poly_func(ptf_table); 
COL1 COL2
---- ----
  10  100
  20  200
  30  300
"COL1" total is 60
"COL2" total is 600

And then show the totals.

EXEC totals.show_totals
60
600

Pretty straightforward. In the next post I’ll show how to display them in the query results.

Thanks for reading!