Category: Table Functions

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!

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!

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!