PTF’S – Adding Columns and Rows Deep Dive Part 1

PTF’s contain two types of columns:

  1. Ones that are passed into the PTF – as a table, view, WITH clause or result set of a nested PTF. These are referred to as GET columns.
  2. Ones that are added and optionally populated by a PTF. These are referred to as PUT columns.

This post is a deep-dive about adding and populating PUT columns.

Here is the table I’ll use for this post:

SELECT *
  FROM table1; 
COL1 COL2 COL3
---- ---- ---- 
  19   99    9
  19   98    8

In the PTF shown below I’ll add two columns – COL4 and COL5.

CREATE OR REPLACE PACKAGE poly_pkg AS
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
    RETURN DBMS_TF.DESCRIBE_T;
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

    v_new_col1 DBMS_TF.COLUMN_METADATA_T;
    v_new_col2 DBMS_TF.COLUMN_METADATA_T;
    v_new_cols DBMS_TF.COLUMNS_NEW_T;

  BEGIN

    -- The added columns need to be defined in the
    -- DESCRIBE so they can be included in the
    -- underlying parse operation
    v_new_col1 := DBMS_TF.COLUMN_METADATA_T( 
                        type => DBMS_TF.TYPE_VARCHAR2,
                        name => 'COL4',
                        max_len => 15 );
    v_new_col2 := DBMS_TF.COLUMN_METADATA_T( 
                        type => DBMS_TF.TYPE_VARCHAR2,
                        name => 'COL5',
                        max_len => 15 );
    v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col1,
                                         2 => v_new_col2 );
    RETURN DBMS_TF.DESCRIBE_T ( new_columns => v_new_cols );

  END;

END;
/

CREATE OR REPLACE FUNCTION poly_func(p_tbl IN TABLE)
  RETURN TABLE PIPELINED
  ROW POLYMORPHIC USING poly_pkg;
/
SELECT *
FROM poly_func(table1);
-- The query fails with this error:
---- ORA-62573: new column (COL4) is not allowed with
---- describe only polymorphic table function
-- It fails because a FETCH_ROWS procedure has not been
-- added

I’ll add the worlds simplest FETCH_ROWS procedure to get past the error:

PROCEDURE fetch_rows IS
BEGIN
  NULL;
END;

Now the query looks like this:

SELECT *
  FROM poly_func(table1);
COL1 COL2 COL3 COL4 COL5
---- ---- ---- ---- ----
  19   99    9
  19   98    8

The added (or put) columns are returned but with no values.

Next I’ll augment the FETCH_ROWS procedure to add hard coded values for COL4 and COL5. I’ll assume the table will always have two rows.

PROCEDURE fetch_rows IS

  v_rowset DBMS_TF.ROW_SET_T;
  v_col4   DBMS_TF.COLUMN_DATA_T;
  v_col5   DBMS_TF.COLUMN_DATA_T;

BEGIN

  -- I know I have two rows and two columns so
  -- I'll just hardcode that in!
  -- First up are two rows worth of values for col4
  v_col4.tab_varchar2(1) := 'Row 1 Col 4';
  v_col4.tab_varchar2(2) := 'Row 2 Col 4';

  -- And now two rows worth of values for col5
  v_col5.tab_varchar2(1) := 'Row 1 Col 5';
  v_col5.tab_varchar2(2) := 'Row 2 Col 5';

  -- Add the rows to the rowset
  v_rowset(1) := v_col4;
  v_rowset(2) := v_col5;

  -- Put/Add/Staple the rowset I just created to the
  -- ones from the PTF query (or GET) operation
  DBMS_TF.PUT_ROW_SET(v_rowset);

END;

Some key points about the above code sample:

  • Data for the first column is put into a variable named v_col4. Two row values are put in – ‘Row 1 Col 4’ and ‘Row 2 Col 4’
  • Data for the second column is put into a variable named v_col5. Two row values are put in – ‘Row 1 Col 5’ and ‘Row 2 Col 5’
  • The two columns are added to the local rowset named v_rowset as row 1 and row 2
  • Then the local rowset is attached to the fetched rowset
  • The fetched rowset did not require an explicit fetch

And the result is…

SELECT *
  FROM poly_func(table1);
COL1 COL2 COL3 COL4        COL5
---- ---- ---- ----------- ---------------
  19   99    9 Row 1 Col 4 Row 1 Col 5
  19   98    8 Row 2 Col 4 Row 2 Col 5

To recap – column variables are PL/SQL tables attached to a rowset as columns 1 and 2 and that local rowset is attached to the fetched rowset as columns 4 and 5.

That’s simple and easy to follow.  ~ Noone. Ever.

That example worked but it’s not very flexible because it assumes two rows and two added columns. Let’s make it more flexible using environment variables to determine how may columns and rows we are dealing with.

Environment variables are retrieved using the GET_ENV function. In this code we’ll use it to get the following:

  • The number of rows fetched
  • The number of columns PUT or added during the describe. In this case it will be two (col4 and col5)
  • The number of columns fetched (get). In this case it will be three (col1, col2 and col3)
PROCEDURE fetch_rows IS

  v_env DBMS_TF.ENV_T;
  v_rowset DBMS_TF.ROW_SET_T;
  v_rowcount NUMBER;
  v_col DBMS_TF.COLUMN_DATA_T;
  v_put_cols DBMS_TF.TABLE_METADATA_T;
  v_get_cols DBMS_TF.TABLE_METADATA_T;
  v_col_disp NUMBER;

BEGIN

  -- Get the environment for the current row set
  -- including the number of rows returned,
  -- number of columns from the fetch/get (3)
  -- and the number of columns being added (2)
  v_env := DBMS_TF.GET_ENV;
  v_rowcount := v_env.row_count;
  v_put_cols := v_env.put_columns;
  v_get_cols := v_env.get_columns;

  --
  -- Now loop through the columns and add enough
  -- values to populate every row
  --
  -- for every put column...
  FOR col_counter IN 1..v_put_cols.COUNT LOOP

    -- for every row in the rowset...
    FOR row_counter IN 1..v_rowcount LOOP
      v_col_disp := v_get_cols.COUNT + col_counter;
      -- Populate the column based on the row number
      -- and column number
      v_col.tab_varchar2(row_counter) := 'Row ' || 
                   row_counter || ' Col ' || v_col_disp;
    END LOOP; -- every row in rowset

    -- add the rows of columns to the rowset
    v_rowset(col_counter) := v_col;

  END LOOP; -- every put column

  -- Add/Put/Staple the new columns onto the end
  -- of each row from the fetch/get.
  DBMS_TF.PUT_ROW_SET(v_rowset);

END;

Now here is the result:

SELECT *
  FROM poly_func(table1);
COL1 COL2 COL3 COL4        COL5
---- ---- ---- ----------- ---------------
  19   99    9 Row 1 Col 4 Row 1 Col 5 
  19   98    8 Row 2 Col 4 Row 2 Col 5

Looks the same as with the hardcoded values. But now it works no matter how many rows are fetched. For example here is the output for 3 records.

SELECT *
FROM poly_func(table1);
COL1 COL2 COL3 COL4        COL5
---- ---- ---- ----------- -----------
  19   99    9 Row 1 Col 4 Row 1 Col 5
  19   98    8 Row 2 Col 4 Row 2 Col 5
  19   97    7 Row 3 Col 4 Row 3 Col 5

What about this trickery? v_col_disp := v_get_cols.COUNT + col_counter;

The number of put columns was used to control the column loop thus it counted 1 and 2. The display required a value of 4 for col4 and 5 for col5. The solution was to increment the counter by the number of get columns (3) and used that for display.

Thanks for reading!