This post in the Polymorphic Table Function (PTF) is important for two reasons:
- 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?
- 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!