Are PTF Added Columns Visible – Part 1

The previous post on Polymorphic Table Functions (PTF’s) demonstrated adding a column. That is a very powerful and flexible option. So flexible that it made me wonder if the added column would be visible outside of the PTF.

So at the end of the post I created a view based on the PTF and it included the added column. Does this mean Oracle executed the PTF while creating the view?

Let’s investigate…

I’ll change the PTF package to add a column if the number of records in the underlying table is even. This is done with two changes:

Change 1 – the DESCRIBE function:

SELECT COUNT(*)
  INTO v_count
  FROM table1;
-- add another_col1 if number of rows is even
IF MOD(v_count,2) = 0 THEN
  RETURN DBMS_TF.DESCRIBE_T ( new_columns => v_new_cols );
ELSE
  RETURN NULL;
END IF;

Change 2 – the FETCH_ROWS function

SELECT COUNT(*)
  INTO v_count
  FROM table1;
-- populate another_col1 if number of rows is even 
IF MOD(v_count,2) = 0 THEN
  DBMS_TF.GET_ROW_SET(v_rowset,v_rowcount);
  FOR counter IN 1..v_rowcount LOOP
    v_another_col(counter) := 1;
  END LOOP; -- every row in the rowset
  DBMS_TF.PUT_COL( columnid => 1,
                   collection => v_another_col);
END IF;

And now some code to see what happens…

-- verify the number of records in the table
SELECT COUNT(*)
  FROM table1;
COUNT(*)
--------
       2

-- verify the view structure
SQL> DESC who_knows
Name         Type
------------ --------------
COL1         NUMBER
COL2         NUMBER
ANOTHER_COL1 VARCHAR2(4000)

-- query the view
SELECT *
  FROM who_knows;
COL1 COL2 ANOTHER_COL1
---- ---- ------------
19     99 1
19     98 1

-- make the rowcount odd
-- will hilarity ensue?
DELETE table1 WHERE rownum = 1;
1 row deleted.

-- query the PTF...
-- the added column is still there but it is NULL
-- so the DESCRIBE function still links the column
-- but the FETCH_ROWS function does not populate it
SELECT *
  FROM poly_func(table1);
COL1 COL2 ANOTHER_COL1
---- ---- ------------
19     98

-- query the view which is still valid
-- because the PTF sees the added column
SELECT *
  FROM who_knows;
COL1 COL2 ANOTHER_COL1
---- ---- ------------
19     98

-- is the column still in the view definition? Yes...
DESC who_knows
Name         Type
------------ --------------
COL1         NUMBER
COL2         NUMBER
ANOTHER_COL1 VARCHAR2(4000)

-- will commiting the changes make a difference?
COMMIT;
Commit complete.

-- Nope
SELECT *
  FROM poly_func(table1);
COL1 COL2 ANOTHER_COL1
---- ---- ------------
  19   98

-- the column is still in the view and NULL
SELECT *
  FROM who_knows;
COL1 COL2 ANOTHER_COL1
---- ---- ------------
19     98

-- recompile the PTF package with the ODD rowcount
-- which should remove the added column...
-- ...or will it?
ALTER PACKAGE poly_pkg COMPILE BODY;
Package body altered.

-- Yes! The column no longer exists so the view
-- is broken
SELECT *
  FROM who_knows;
FROM who_knows
*
ERROR at line 2:
ORA-04063: view "D.WHO_KNOWS" has errors

-- Its so broken I just cant describe it...
DESC who_knows
ERROR:
ORA-24372: invalid object for describe
 
-- Here is the underlying query....
SELECT text
  FROM user_views
 WHERE view_name = 'WHO_KNOWS';
TEXT
-----------------------------------
SELECT "COL1","COL2","ANOTHER_COL1"
FROM poly_func(table1)

-- and the underyling query does not work
SELECT "COL1","COL2","ANOTHER_COL1"
  FROM poly_func(table1);
SELECT "COL1","COL2","ANOTHER_COL1"
*
ERROR at line 1:
ORA-00904: "ANOTHER_COL1": invalid identifier

-- and the column is gone from the PTF
SELECT *
  FROM poly_func(table1);
COL1 COL2
---- ----
  19   98

Hmm, I don’t think this will be the end of this topic. I’ll cover more detail in future posts.

Thanks for reading!