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

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!

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

Populating new columns in PTF’s is done using PL/SQL tables defined in the DBMS_TF package. There is one PL/SQL table type for every datatype. For example here is the one for VARCHAR2 data.

TYPE TAB_VARCHAR2_T IS TABLE OF VARCHAR2(32767)
  INDEX BY PLS_INTEGER;

This means it holds multiple string values of up to 32767 characters.

The association between data values and the columns they will occupy is defined in the COLUMN_DATA_T PL/SQL record.

The PL/SQL table and record entries are manipulated using standard PL/SQL as shown below:

DECLARE
  v_column_data  DBMS_TF.COLUMN_DATA_T;
  v_tab_varchar2 DBMS_TF.TAB_VARCHAR2_T;
BEGIN
  v_tab_varchar2(1) := 'ABC';
  v_tab_varchar2(2) := 'DEF';
  v_tab_varchar2(3) := 'GHI';
  v_column_data.tab_varchar2 := v_tab_varchar2;
  v_column_data.tab_number := DBMS_TF.TAB_NUMBER_T( 1 => 1,
                                                    2 => 2,
                                                    3 => 3 );
  FOR counter IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE( 
          v_column_data.tab_varchar2(counter) || ' ' ||
          v_column_data.tab_number(counter));
  END LOOP;
  v_column_data.tab_number.DELETE(2);
  FOR counter IN 1..3 LOOP
    BEGIN
      DBMS_OUTPUT.PUT_LINE( 
          v_column_data.tab_varchar2(counter) || ' ' ||
          v_column_data.tab_number(counter));
    EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL;
    END;
  END LOOP;
  FOR counter IN 1..3 LOOP
    IF v_column_data.tab_varchar2.EXISTS(counter) THEN
      DBMS_OUTPUT.PUT( 
          v_column_data.tab_varchar2(counter) || ' ');
    END IF;
    IF v_column_data.tab_number.EXISTS(counter) THEN
      DBMS_OUTPUT.PUT(
          v_column_data.tab_number(counter) );
    END IF;
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END;

Note the need to handle exceptions when accessing rows with no values. If not handled this error is thrown:

ORA-06502: PL/SQL: numeric or value error:
           character to number conversion error

Here are the results of the code:

ABC 1
DEF 2
GHI 3
ABC 1
GHI 3
ABC 1
DEF
GHI 3

Here is the full list of available PL/SQL tables.

TAB_VARCHAR2_T
TAB_NUMBER_T
TAB_DATE_T
TAB_BINARY_FLOAT_T
TAB_BINARY_DOUBLE_T
TAB_RAW_T
TAB_CHAR_T
TAB_CLOB_T
TAB_BLOB_T
TAB_TIMESTAMP_T;
TAB_TIMESTAMP_TZ_T
TAB_INTERVAL_YM_T
TAB_INTERVAL_DS_T
TAB_TIMESTAMP_LTZ_T
TAB_BOOLEAN_T 
TAB_ROWID_T 
TAB_NATURALN_T

The COLUMN_DATA_T associates the column to its data. It has this structure:

TYPE COLUMN_DATA_T IS RECORD
  ( description            COLUMN_METADATA_T, 
    tab_varchar2           TAB_VARCHAR2_T,
    tab_number             TAB_NUMBER_T,
    tab_date               TAB_DATE_T,
    tab_binary_float       TAB_BINARY_FLOAT_T,
    tab_binary_double      TAB_BINARY_DOUBLE_T,
    tab_raw                TAB_RAW_T,
    tab_char               TAB_CHAR_T,
    tab_clob               TAB_CLOB_T,
    tab_blob               TAB_BLOB_T,
    tab_timestamp          TAB_TIMESTAMP_T,
    tab_timestamp_tz       TAB_TIMESTAMP_TZ_T,
    tab_interval_ym        TAB_INTERVAL_YM_T,
    tab_interval_ds        TAB_INTERVAL_DS_T,    
    tab_timestamp_ltz      TAB_TIMESTAMP_LTZ_T,
    tab_rowid              TAB_ROWID_T);

Within the PTF you can populate any of the PL/SQL tables even if they are not returned. So how does the PTF decide which one to return? I’m glad you asked because I’ll cover it in the next post.

Thanks for reading!