implestrat.com

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!

 

One of the most interesting things Polymorphic Table Functions (PTF’s) can do is adding columns to a result set. For example a table with two columns can be passed into the PTF and more that two can come out.

The added columns have to be known during the SQL parse operation so they must be defined in the DESCRIBE function of the PTF. The metadata (name, datatype, length, etc) for the new columns is defined using the aptly named NEW_COLUMNS object.

The data for the added columns is created in two steps within the FETCH_ROWS procedure. Step one creates data in a collection and step two adds the collection to the current rowset. For example if a rowset has 3 rows then the collection for each new column can have up to 3 records as well. Adding the collection of values to the rowset aligns the rows in the collection with the rows in the rowset.

Here’s a simple example that adds a column named ANOTHER_COL1 and populates it with 1’s.

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
    -- metadata for column to add
    v_new_col DBMS_TF.COLUMN_METADATA_T;
    -- table of columns to add
    v_new_cols DBMS_TF.COLUMNS_NEW_T;
  BEGIN
    -- define metadata for column named ANOTHER_COL1
    -- that will default to a datatype of varchar2 with
    -- a length of 4000
    v_new_col := DBMS_TF.COLUMN_METADATA_T( 
                      name => 'ANOTHER_COL1' );
    -- add ANOTHER_COL1 to the list of columns new columns
    v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col);
    -- Instead of returning NULL we will RETURN a specific
    -- DESCRIBE_T that adds new columns
    RETURN DBMS_TF.DESCRIBE_T ( new_columns => v_new_cols );
  END;

  PROCEDURE fetch_rows IS
    v_rowset DBMS_TF.ROW_SET_T;
    v_rowcount PLS_INTEGER;
    v_another_col DBMS_TF.TAB_VARCHAR2_T;
  BEGIN
    -- fetch rows into a local rowset
    -- at this point the rows will have columns
    -- from the the table/view/query passed in
    DBMS_TF.GET_ROW_SET(v_rowset,v_rowcount);
    -- for every row in the rowset...
    FOR counter IN 1..v_rowcount LOOP
      -- specify a value for the new column in
      -- the row
      v_another_col(counter) := 1;
    END LOOP; -- every row in the rowset
    -- add the newly populated column to the rowset
    DBMS_TF.PUT_COL( columnid   => 1,
                     collection => v_another_col);
  END;

END;

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

And now for the queries:

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

-- create a view to verify the datatype
-- and length of the added column
CREATE VIEW who_knows AS
SELECT *
  FROM poly_func(table1);
View created.

DESC who_knows
 Name          Type
 ------------- --------------
 COL1          NUMBER
 COL2          NUMBER
 ANOTHER_COL1  VARCHAR2(4000)

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

Wait a minute – how does the view know about the added column when it doesn’t show up until run time? That’s one of the things I’ll investigate in future posts.

Thanks for reading!

Previous posts in this series focused on influencing the PARSE operation of a PTF. This post demonstrates how they influence the OPEN, FETCH and CLOSE operations as well. Here is some simple code to introduce things.

CREATE OR REPLACE PACKAGE poly_pkg AS

  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
           RETURN DBMS_TF.DESCRIBE_T;

  -- executes when the cursor opens when the
  -- PTF is queried
  PROCEDURE open;

  -- executes when a row (or set of rows) is queried
  PROCEDURE fetch_rows;

  -- executes when the cursor closes
  PROCEDURE close;

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
    -- identify when the DESCRIBE executes
    DBMS_OUTPUT.PUT_LINE('DESCRIBE');
    RETURN NULL;
  END;

  PROCEDURE open IS
  BEGIN
    -- identify when the OPEN executes
    DBMS_OUTPUT.PUT_LINE('OPEN');
  END;

  PROCEDURE fetch_rows IS
  BEGIN
    -- identify when the FETCH executes
    DBMS_OUTPUT.PUT_LINE('FETCH');
  END;

  PROCEDURE close IS
  BEGIN
    -- identify when the close executes
    DBMS_OUTPUT.PUT_LINE('CLOSE');
  END;

END;

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

And the query results are:

SELECT col1
  FROM poly_func(table1);
COL1
----
  19
DESCRIBE
OPEN
FETCH
CLOSE

One thing the FETCH_ROWS procedure can do is interrogate the values that are fetched. Here is an example that grabs the rowset and outputs the count.

PROCEDURE fetch_rows IS
  v_rowset DBMS_TF.ROW_SET_T;
  v_rowcount PLS_INTEGER;
  v_colcount PLS_INTEGER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('FETCH');
  -- grab the rowset, count of rows and count
  -- of columns
  DBMS_TF.GET_ROW_SET(v_rowset,v_rowcount,v_colcount);
  DBMS_OUTPUT.PUT_LINE('Rows = ' || v_rowcount);
END;

And now the query…

SELECT col1
  FROM poly_func(table1);
COL1
----
  19
DESCRIBE
OPEN
FETCH
Rows = 1
CLOSE

Future articles will delve more into what FETCH_ROWS can do as well as the ROW_SET_T structure.

Thanks for reading!