PTF Pass Through In Depth

This post in the Polymorphic Table Function (PTF) is important for two reasons:

  1. Its the last one where I type out the full name – from now on they will be referred to as PTF’s. I have petitioned the SQL Standards group to do the same. They have not replied. Maybe I have an old e-mail address for them?
  2. It dives into the very important concept of Pass Through.

Each column being processed by a PTF has a Pass Through boolean attribute determining if it gets passed through to its calling function. If the attribute is true then values for the column are passed through. If the attribute is FALSE then values for the column are not passed through.

Below are some examples to explain further. First up – here is data.

-- One table with 3 columns...
DESCRIBE table1
Name   Type
------ ----------
COL1   NUMBER
COL2   NUMBER
COL3   NUMBER

-- And two records of data
SELECT *
  FROM table1;
COL1       COL2       COL3
---------- ---------- ----------
        19         99          9
        19         98          8

And now for the first demo PTF. The important things to note are:

  • The DESCRIBE function outputs all of the columns passed in
  • The FETCH_ROWS procedure outputs the execution id and rowset
  • The default value of TRUE for PASS THROUGH is used for all columns
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;
/

CREATE OR REPLACE PACKAGE BODY poly_pkg AS

  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
    RETURN DBMS_TF.DESCRIBE_T AS
  BEGIN
    DBMS_TF.TRACE( msg => 'DESC');
    FOR counter IN 1..p_tbl.column.count LOOP
      DBMS_OUTPUT.PUT_LINE(
          p_tbl.column(counter).description.name);
    END LOOP;
    RETURN NULL;
  END;

  PROCEDURE fetch_rows IS
    v_rowset DBMS_TF.ROW_SET_T;
  BEGIN
    DBMS_TF.TRACE( msg     => 'FETCH',
                   with_id => TRUE );
    DBMS_TF.GET_ROW_SET(v_rowset);
    DBMS_TF.TRACE( rowset => v_rowset );
  END;

END;
/

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

And here is the query.

-- This nested query will execute the PTF
-- twice - first for the WITH clause and then for the
-- main query 
WITH x AS ( SELECT * 
              FROM poly_func(table1) )
SELECT * 
  FROM poly_func(x);

And here are the results.

DESC
"COL1"
"COL2"
"COL3"
DESC
"COL1"
"COL2"
"COL3"
FETCH <id=0B00000000000000>
.... [1] = {}
.... [2] = {}
FETCH <id=0C00000000000000>
.... [1] = {}
.... [2] = {}

Note that all 3 column names were displayed by the two DESCRIBE runs. Also note the separate execution ID’s in the FETCH. All columns from the WITH clause were passed through to the main query and all columns from the main query were passed through as results.

To demonstrate things further I’ll change the PASS THROUGH attribute in the PTF below. I’ll set it to FALSE for the first column passed to each DESCRIBE.

FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
  RETURN DBMS_TF.DESCRIBE_T AS
BEGIN
  DBMS_TF.TRACE( msg => 'DESC');
  FOR counter IN 1..p_tbl.column.count LOOP
    DBMS_OUTPUT.PUT_LINE(
        p_tbl.column(counter).description.name);
  END LOOP;
  /*!!!!! PASS_THROUGH = FALSE for first column !!!!!*/
  p_tbl.column(1).pass_through := FALSE;
  RETURN NULL;
END;

And run the nested query again.

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

And here are the results.

COL3
------
     9
     8
DESC
"COL1"
"COL2"
"COL3"
DESC
"COL2"
"COL3"
FETCH <id=0F00000000000000>
.... [1] = {}
.... [2] = {}
FETCH <id=1000000000000000>
.... [1] = {}
.... [2] = {}

Note that:

  • Only COL3 was returned outright
  • The first DESCRIBE saw all 3 columns
  • The second describe did not see COL1

Here’s why – the first DESCRIBE was passed all three columns and then set the first column (COL1) to not pass through. Thus the second DESCRIBE only saw COL2 and COL3. It also set it’s first column (COL2) to not pass through. Thus poor lonely COL3 was the only one left to display.

I’ll set the FOR READ attribute to the PTF below to make things more clear.

FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
  RETURN DBMS_TF.DESCRIBE_T AS
BEGIN
  DBMS_TF.TRACE( msg => 'DESC');
  FOR counter IN 1..p_tbl.column.count LOOP
    DBMS_OUTPUT.PUT_LINE(
        p_tbl.column(counter).description.name);
    /*!!!! All columns FOR_READ = TRUE !!!!!*/
    p_tbl.column(counter).for_read := TRUE;
  END LOOP;
  p_tbl.column(1).pass_through := FALSE;
  RETURN NULL;
END;

Now the columns and values from the rowset are shown.

DESC
"COL1"
"COL2"
"COL3"
DESC
"COL2"
"COL3"
FETCH <id=1100000000000000>
.... [1] = {"COL1":19, "COL2":99, "COL3":9}
.... [2] = {"COL1":19, "COL2":98, "COL3":8}
FETCH <id=1200000000000000>
.... [1] = {"COL2":99, "COL3":9}
.... [2] = {"COL2":98, "COL3":8}

The first rowset contains 3 columns and the second contains 2. This shows the values were queried but were not passed through.

I’ll close out this post with some pitfalls to be aware of when using the PASS_THROUGH.

-- This query works fine because the column count
-- passed through goes from 2 (col2 and col3) down
-- to 1 (just col3)
WITH x AS
( SELECT col2, col3
    FROM poly_func(table1) )
SELECT *
  FROM poly_func(x);
COL3
----
   9
   8
 
-- But this query fails because the column count goes from
-- 1 (col2) down to zero
WITH x AS
( SELECT col2
    FROM poly_func(table1) )
SELECT *
  FROM poly_func(x);
FROM poly_func(x)
*
ERROR at line 5:
ORA-30732: table contains no user-visible columns

-- This query fails because the col1 column
-- is removed by the WITH clause query thus it is
-- no longer resolvable by the SELECT * of the main
-- query
WITH x AS
( SELECT col1
    FROM poly_func(table1) )
SELECT *
  FROM poly_func(x);
( SELECT col1
*
ERROR at line 2:
ORA-00904: "COL1": invalid identifier

I’ll discuss this in more detail in later posts.

Thanks for reading!

PTF Tracing Part 3

Polymorphic Table Functions (PTF’s) have tracing functions customised to interrogate object types such as fetched rowsets. Here is an example:

PROCEDURE fetch_rows IS
  v_rowset DBMS_TF.ROW_SET_T;
  v_rowcount PLS_INTEGER;
  v_another_col DBMS_TF.TAB_VARCHAR2_T;
BEGIN
  DBMS_TF.GET_ROW_SET(v_rowset,v_rowcount);
  DBMS_TF.TRACE( rowset => v_rowset );
END;

And here are the results for a rowset containing 2 rows of 2 columns. Looks like somethings is missing though…

.... [1] = {}
.... [2] = {}

Where are the column values in the rows? It turns out it is not the fault of the trace functions – the solution is to flag the columns as for_read in the DESCRIBE like this:

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;

Now the column values show up.

.... [1] = {"COL1":19, "COL2":99}
.... [2] = {"COL1":19, "COL2":98}

Thanks for reading!

PTF Tracing Part 2

Polymorphic Table Functions (PTF’s) tracing can be added to the DESCRIBE, OPEN, FETCH_ROWS and CLOSE portions of the package.

FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T)
  RETURN DBMS_TF.DESCRIBE_T IS
BEGIN
  DBMS_TF.TRACE( msg       => 'Hello From Describe',
                 separator => 'DESC',
                 prefix    => 'DPREFIX');
  RETURN NULL;
END;

PROCEDURE open IS
BEGIN
  DBMS_TF.TRACE( msg       => 'Hello From Open',
                 with_id   => TRUE,
                 separator => 'OPEN ',
                 prefix    => 'OPPREFIX' );
END;

PROCEDURE fetch_rows IS
BEGIN
  DBMS_TF.TRACE( msg       => 'Hello From Fetch',
                 with_id   => TRUE,
                 separator => 'FETCH ',
                 prefix    => 'FPREFIX' );
END;

PROCEDURE close IS
BEGIN
  DBMS_TF.TRACE( msg       => 'Hello From Close',
                 with_id   => TRUE,
                 separator => 'CLOSE ',
                 prefix    => 'CPREFIX' );
END;

And here is the output from a run.

DPREFIXHello From Describe
DESCDESCDESCDESCDESCDESCDESCDESCDESCDESCDESCDESCDESC
OPPREFIXHello From Open <id=0500000000000000>
OPEN OPEN OPEN OPEN OPEN OPEN OPEN OPEN OPEN OPEN OP
FPREFIXHello From Fetch <id=0500000000000000>
FETCH FETCH FETCH FETCH FETCH FETCH FETCH FETCH FETC
CPREFIXHello From Close <id=0500000000000000>
CLOSE CLOSE CLOSE CLOSE CLOSE CLOSE CLOSE CLOSE CLOS

The trace function is acutely aware of the object types PTF’s use. For example the argument (table, view or query) can be interrogated with a singe call to the trace function as shown here:

FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T)
  RETURN DBMS_TF.DESCRIBE_T IS
BEGIN
  DBMS_TF.TRACE( msg       => 'Hello From Describe',
                 separator => 'DESC',
                 prefix    => 'DPREFIX');
  DBMS_TF.TRACE ( tab => p_tbl );
  RETURN NULL;
END;

And here are the results.

DPREFIXHello From Describe
DESCDESCDESCDESCDESCDESCDESCDESCDESCDESCDESCDESCDESC
Schema Name:........."D"
Package Name:........."POLY_PKG"
PTF Name:........."POLY_FUNC"
........tab.column[1] =
pass through column
NOT a for read column
__________Type:...............NUMBER
__________Max Length:.........22
__________Name:..............."COL1"
__________Name Length:........6
__________Precision:..........0
__________Scale:..............-127
........tab.column[2] =
pass through column
NOT a for read column
__________Type:...............NUMBER
__________Max Length:.........22
__________Name:..............."COL2"
__________Name Length:........6
__________Precision:..........0
__________Scale:..............-127

OPPREFIXHello From Open <id=0600000000000000>
OPEN OPEN OPEN OPEN OPEN OPEN OPEN OPEN OPEN OPEN OP
FPREFIXHello From Fetch <id=0600000000000000>
FETCH FETCH FETCH FETCH FETCH FETCH FETCH FETCH FETC
CPREFIXHello From Close <id=0600000000000000>
CLOSE CLOSE CLOSE CLOSE CLOSE CLOSE CLOSE CLOSE CLOS

Thanks for reading!