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

This post explains what happens (or doesn’t happen as the case may be) to outlier columns and value. Namely the following:

  1. Added columns that do not get populated
  2. Values added to rows outside the current row set

Here is the table used in these examples:

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

The DESCRIBE function of the PTF adds 2 columns – one date and one numeric.

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

  v_new_col1 := DBMS_TF.COLUMN_METADATA_T( 
                   type => DBMS_TF.TYPE_DATE,
                   name => 'DT_COL1' );
  v_new_col2 := DBMS_TF.COLUMN_METADATA_T( 
                   type    => DBMS_TF.TYPE_NUMBER,
                   name    => 'NUM_COL1',
                   max_len => 8 );
  v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col1,
                                       2 => v_new_col2 );
  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;

And the FETCH_ROWS procedure.

Note it does not add any date values. None. Nada. Nilch.

Also not it adds one two many rows of data (rowcount + 1).

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

  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...
    -- ...plus one more
    FOR row_counter IN 1..v_rowcount + 1 LOOP
      v_col.tab_varchar2(row_counter) := 'ABC';
      v_col.tab_number(row_counter)   := 
            col_counter * row_counter;
    END LOOP; -- every row in rowset

    -- output the number of entries added
    DBMS_OUTPUT.PUT_LINE('Col Tab Count ' || 
                         v_col.tab_number.COUNT);

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

  END LOOP; -- every put column

  DBMS_TF.PUT_ROW_SET(v_rowset);

END;

And the results:

SELECT *
  FROM poly_func(table1);
DT_COL1 NUM_COL1
------- --------
               2
               4
Col Tab Count 3
Col Tab Count 3

The date column (DT_COL1) was still added but will be null for all rows because it was never populated. And even though 3 rows were populated in the FETCH_ROWS procedure only two were returned by the PTF because there are only two rows in the table named table1.

Thanks for reading!

 

 

sdla;sd