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

This post explains how PTF’s magically match up new columns with their new data. Truth be told – it’s not magic. The matching is done based on datatype.

Yes. It is that simple.

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

  /*
    || Four new columns
    || Two are varchar2 and two are number
  */
  v_new_col1 DBMS_TF.COLUMN_METADATA_T;
  v_new_col2 DBMS_TF.COLUMN_METADATA_T;
  v_new_col3 DBMS_TF.COLUMN_METADATA_T;
  v_new_col4 DBMS_TF.COLUMN_METADATA_T;
  v_new_cols DBMS_TF.COLUMNS_NEW_T;

BEGIN

  -- define a column named vc2_col1
  v_new_col1 := DBMS_TF.COLUMN_METADATA_T( 
                     type    => DBMS_TF.TYPE_VARCHAR2,
                     name    => 'VC2_COL1',
                     max_len => 8 );

  -- define a column named num_col1
  v_new_col2 := DBMS_TF.COLUMN_METADATA_T( 
                     type    => DBMS_TF.TYPE_NUMBER,
                     name    => 'NUM_COL1',
                     max_len => 8 );

  -- define a column named vc2_col2
  v_new_col3 := DBMS_TF.COLUMN_METADATA_T( 
                     type    => DBMS_TF.TYPE_VARCHAR2,
                     name    => 'VC2_COL2',
                     max_len => 8 );

  -- define a column named numcol2
  v_new_col4 := DBMS_TF.COLUMN_METADATA_T( 
                     type    => DBMS_TF.TYPE_NUMBER,
                     name    => 'NUM_COL2',
                     max_len => 8 );

  -- add the columns to the list of new columns
  v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col1,
                                       2 => v_new_col2,
                                       3 => v_new_col3,
                                       4 => v_new_col4 );
  -- dont display columns queried from the table because
  -- they will just clutter the display
  FOR counter IN 1..p_tbl.column.count LOOP
    p_tbl.column(counter).pass_through := FALSE;
  END LOOP;
  RETURN DBMS_TF.DESCRIBE_T ( new_columns => v_new_cols );
END;

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;

BEGIN

  -- get the number of rows fetched, number of get columns
  -- and number of put columns
  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;

  -- 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

      /*
       || Assign a string value (ABC) to the varchar2_tab
       || for current column and assign a number (123) to
       || the number_tab for the current column and let
       || Oracle decide which to use based on the
       || datatype of the new column
      */
      v_col.tab_varchar2(row_counter) := 'ABC';
      v_col.tab_number(row_counter) := 123;

    END LOOP; -- every row in rowset

    -- add column values to rowset
    v_rowset(col_counter) := v_col;

  END LOOP; -- every put column

  -- rivet the new rowset to the main rowset
  DBMS_TF.PUT_ROW_SET(v_rowset);

END;

And the result is…

SELECT *
  FROM poly_func(table1);
VC2_COL1   NUM_COL1 VC2_COL2   NUM_COL2
-------- ---------- -------- ----------
ABC             123 ABC             123
ABC             123 ABC             123

ABC was put in the VARCHAR2 columns (VC2_COL1 and VC2_COL2) and 123 was put in the NUMBER columns (NUM_COL1 and NUM_COL2).

Thanks for reading!