Adding a column to the output of a polymorphic table function is a two step process.
- in describe() define the new column
in fetch_rows() populate the new column, this can be performed via either:
- dbms_tf.put_col()
- dbms_tf.put_row_set()
(Note: This is the first lesson in the tutorial to use a fetch_rows() procedure.)
For step 1 we need to learn and use the dbms_tf.describe_t data type. We can visualize dbms_tf.describe_t like this.
describe_t.new_columns
describe_t.new_columns(i)
describe_t.new_columns(i).name
describe_t.new_columns(i).name_len
describe_t.new_columns(i).type
describe_t.new_columns(i).max_len
describe_t.new_columns(i).precision
describe_t.new_columns(i).scale
describe_t.new_columns(i).charsetid
describe_t.new_columns(i).charsetform
describe_t.new_columns(i).collation
describe_t.cstore_chr
describe_t.cstore_chr(a)
describe_t.cstore_num
describe_t.cstore_num(a)
describe_t.cstore_bol
describe_t.cstore_bol(a)
describe_t.cstore_dat
describe_t.cstore_dat(a)
describe_t.method_names
describe_t.method_names(a)
For step 2 we need to learn and use the tab_varchar2_t data type (or tab_number_t, tab_date_t, etc.) to populate our new columns via put_col().
TYPE TAB_VARCHAR2_T IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;
Alternately we can use the dbms_tf.row_set_t data type to populate the new column via put_row_set(). We can visualize dbms_tf.row_set_t like this.
row_set_t
row_set_t(i).description
row_set_t(i).description.name
row_set_t(i).description.name_len
row_set_t(i).description.type
row_set_t(i).description.max_len
row_set_t(i).description.precision
row_set_t(i).description.scale
row_set_t(i).description.charsetid
row_set_t(i).description.charsetform
row_set_t(i).description.collation
row_set_t(i).tab_varchar2(j)
row_set_t(i).tab_number(j)
row_set_t(i).tab_date(j)
row_set_t(i).tab_binary_float(j)
row_set_t(i).tab_binary_double(j)
row_set_t(i).tab_raw(j)
row_set_t(i).tab_char(j)
row_set_t(i).tab_clob(j)
row_set_t(i).tab_blob(j)
row_set_t(i).tab_timestamp(j)
row_set_t(i).tab_timestamp_tz(j)
row_set_t(i).tab_interval_ym(j)
row_set_t(i).tab_interval_ds(j)
row_set_t(i).tab_timestamp_ltz(j)
row_set_t(i).tab_rowid(j)
Note that the "description" record contains a description of any FOR_READ=TRUE columns retrieved via get_row_set(). fetch_rows() should only read this information, not write to it. Writing to it has no effect on the end result.
To better understand how row_set_t works take a look at what a DEPT row set might look like.
row_set_t( 1 ).description.name = 'DEPTNO' ; row_set_t( 1 ).description.type = dbms_tf.type_number ; row_set_t( 1 ).description.precision = 2 ; row_set_t( 1 ).description.scale = 0 ; row_set_t( 1 ).tab_number = ( 10, 20, 30, 40 ) ; row_set_t( 2 ).description.name = 'DNAME' ; row_set_t( 2 ).description.type = dbms_tf.type_varchar2 ; row_set_t( 2 ).description.max_len = 14 ; row_set_t( 2 ).tab_varchar2 = ( 'ACCOUNTING', 'RESEARCH', 'SALES', 'OPERATIONS' ) row_set_t( 3 ).description.name = 'LOC' ; row_set_t( 3 ).description.type = dbms_tf.type_varchar2 ; row_set_t( 3 ).description.max_len = 13 ; row_set_t( 3 ).tab_varchar2 = ( 'NEW YORK', 'DALLAS', 'CHICAGO', 'BOSTON' )
PUT_COL()
Let's look at an example using the PUT_COL() method.
create or replace package ptf_demo as
function describe( p_input_table_metadata in out dbms_tf.table_t ) return dbms_tf.describe_t ;
procedure fetch_rows ;
function augment ( p_table table)
return table pipelined row polymorphic using ptf_demo ;
end ptf_demo ;
/
create or replace package body ptf_demo as
function describe( p_input_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t
as
v_new_column_desc dbms_tf.describe_t ;
begin
--
-- step (1) define the new column
--
v_new_column_desc.new_columns(1).name := 'THE_NEW_COLUMN' ;
v_new_column_desc.new_columns(1).type := dbms_tf.type_varchar2 ;
v_new_column_desc.new_columns(1).max_len := 100 ;
return v_new_column_desc ;
end describe ;
procedure fetch_rows as
v_new_column dbms_tf.tab_varchar2_t ;
begin
--
-- step (2) populate the new column
--
v_new_column(1) := 'hello' ;
v_new_column(2) := 'world' ;
dbms_tf.put_col( 1, v_new_column );
end fetch_rows ;
end ptf_demo ;
/
select *
from ptf_demo.augment( scott.dept ) ;
DEPTNO DNAME LOC THE_NEW_COLUMN
---------- ---------- ---------- ----------------------------------------------------------------------------------------------------
10 ACCOUNTING NEW YORK hello
20 RESEARCH DALLAS world
30 SALES CHICAGO
40 OPERATIONS BOSTON
PUT_ROW_SET()
Now let's look at a similar example which uses the PUT_ROW_SET() method. We only need to change the package body for this example. The package specification remains the same as above.
create or replace package body ptf_demo as
function describe( p_input_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t
as
v_new_column_desc dbms_tf.describe_t ;
begin
--
-- step (1) define the new column
--
v_new_column_desc.new_columns(1).name := 'THE_NEW_COLUMN' ;
v_new_column_desc.new_columns(1).type := dbms_tf.type_varchar2 ;
v_new_column_desc.new_columns(1).max_len := 100 ;
return v_new_column_desc ;
end describe ;
procedure fetch_rows as
v_new_columns dbms_tf.row_set_t;
begin
--
-- step (2) populate the new column
--
v_new_columns(1).tab_varchar2(1) := 'hello' ;
v_new_columns(1).tab_varchar2(2) := 'world' ;
dbms_tf.put_row_set( v_new_columns );
end fetch_rows ;
end ptf_demo ;
/
select *
from ptf_demo.augment( scott.dept );
DEPTNO DNAME LOC THE_NEW_COLUMN
---------- ---------- ---------- ----------------------------------------------------------------------------------------------------
10 ACCOUNTING NEW YORK hello
20 RESEARCH DALLAS world
30 SALES CHICAGO
40 OPERATIONS BOSTON
DBMS_TF.ROW_SET_T.DESCRIPTION
Above we claimed that updating the description record has no effect on the output. Let's confirm this with a test.
create or replace package body ptf_demo as
function describe( p_input_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t
as
v_new_column_desc dbms_tf.describe_t ;
begin
v_new_column_desc.new_columns(1).name := 'THE_NEW_COLUMN' ;
v_new_column_desc.new_columns(1).type := dbms_tf.type_varchar2 ;
v_new_column_desc.new_columns(1).max_len := 100 ;
return v_new_column_desc ;
end describe ;
procedure fetch_rows as
v_new_columns dbms_tf.row_set_t;
begin
v_new_columns(1).description.name := 'A_DIFFERENT_COLUMN_NAME' ;
dbms_tf.put_row_set( v_new_columns );
end fetch_rows ;
end ptf_demo ;
/
select *
from ptf_demo.augment( scott.dept ) ;
DEPTNO DNAME LOC THE_NEW_COLUMN
---------- ---------- ---------- ----------------------------------------------------------------------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Note how the column name remained set to the value defined in describe(), i.e. "THE_NEW_COLUMN". The column name set in fetch_rows(), "A_DIFFERENT_COLUMN_NAME", was ignored.
NO_DATA_FOUND Exceptions
Be sure to avoid or properly handle NO_DATA_FOUND exceptions in fetch_rows(). When executing a PL/SQL procedure or function, e.g. fetch_rows(), the SQL engine does not treat NO_DATA_FOUND exceptions as errors. Instead it simply stops processing when it encounters NO_DATA_FOUND, see AskTOM "NO_DATA_FOUND in Functions").
This is what happens when fetch_rows() raises NO_DATA_FOUND.
create or replace package body ptf_demo as
function describe( p_input_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t
as
v_new_column_desc dbms_tf.describe_t ;
begin
v_new_column_desc.new_columns(1).name := 'THE_NEW_COLUMN' ;
v_new_column_desc.new_columns(1).type := dbms_tf.type_varchar2 ;
v_new_column_desc.new_columns(1).max_len := 100 ;
return v_new_column_desc ;
end describe ;
procedure fetch_rows as
v_new_columns dbms_tf.row_set_t;
v_some_string varchar2(100);
begin
v_new_columns( 1 ).tab_varchar2(1) := 'hello world' ;
--
-- let's accidentally raise a NO_DATA_FOUND exception by referencing
-- a non-existent array index
--
v_some_string := v_new_columns( 99 ).tab_varchar2(1) ;
dbms_tf.put_row_set( v_new_columns );
end fetch_rows ;
end ptf_demo ;
/
select *
from ptf_demo.augment( scott.dept ) ;
DEPTNO DNAME LOC THE_NEW_COLUMN
---------- ---------- ---------- ----------------------------------------------------------------------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Note how no error was raised. Note also that put_row_set() failed to execute, i.e. "hello world" did not appear in the output.
Referencing Input Table Data
To reference input table data when setting a new output column value we must do two things.
- in describe() identify the input columns we wish to reference by setting their FOR_READ attribute to TRUE
in fetch_rows() retrieve the FOR_READ=TRUE columns using either:
- dbms_tf.get_col()
- dbms_tf.get_row_set()
Let's see an example using the get_col() method.
create or replace package body ptf_demo as
function describe( p_input_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t
as
v_new_column_desc dbms_tf.describe_t ;
begin
--
-- step (1) identify the input columns we wish to reference by setting FOR_READ to TRUE
-- (column #3 in DEPT is the LOC column)
--
p_input_table_metadata.column(3).for_read := true ;
--
-- define new columns
--
v_new_column_desc.new_columns(1).name := 'THE_NEW_COLUMN' ;
v_new_column_desc.new_columns(1).type := dbms_tf.type_varchar2 ;
v_new_column_desc.new_columns(1).max_len := 100 ;
return v_new_column_desc ;
end describe ;
procedure fetch_rows as
v_loc dbms_tf.tab_varchar2_t ;
v_new_column dbms_tf.tab_varchar2_t ;
i pls_integer ;
begin
--
-- step (2) retrieve the FOR_READ=TRUE columns
--
dbms_tf.get_col( 1, v_loc );
--
-- loop through the for_read rows and set new column values
--
i := v_loc.first ;
while i is not null loop
v_new_column(i) := lower( v_loc(i) ) ;
i := v_loc.next(i) ;
end loop ;
--
-- output our new values
--
dbms_tf.put_col( 1, v_new_column );
end fetch_rows;
end;
/
select *
from ptf_demo.augment( scott.dept ) ;
DEPTNO DNAME LOC THE_NEW_COLUMN
---------- ---------- ---------- ----------------------------------------------------------------------------------------------------
10 ACCOUNTING NEW YORK new york
20 RESEARCH DALLAS dallas
30 SALES CHICAGO chicago
40 OPERATIONS BOSTON boston
Here is an exmaple using the get_row_set() method.
create or replace package body ptf_demo as
function describe( p_input_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t
as
v_new_column_desc dbms_tf.describe_t ;
begin
--
-- step (1) identify the input columns we wish to reference by setting FOR_READ to TRUE
--
p_input_table_metadata.column(2).for_read := true ; -- (1) DNAME
p_input_table_metadata.column(3).for_read := true ; -- (2) LOC
--
-- define new columns
--
v_new_column_desc.new_columns(1).name := 'THE_NEW_COLUMN' ;
v_new_column_desc.new_columns(1).type := dbms_tf.type_varchar2 ;
v_new_column_desc.new_columns(1).max_len := 100 ;
return v_new_column_desc ;
end describe ;
procedure fetch_rows as
v_for_read_columns dbms_tf.row_set_t;
v_new_columns dbms_tf.row_set_t;
i pls_integer ;
begin
--
-- step (2) retrieve the FOR_READ=TRUE columns
--
dbms_tf.get_row_set( rowset => v_for_read_columns );
--
-- loop through the for_read rows and set new column values
--
i := v_for_read_columns(1).tab_varchar2.first ;
while i is not null loop
v_new_columns(1).tab_varchar2(i) :=
v_for_read_columns(1).tab_varchar2(i) || ' (' ||
v_for_read_columns(2).tab_varchar2(i) || ')' ;
i := v_for_read_columns(1).tab_varchar2.next(i) ;
end loop ;
--
-- output our new values
--
dbms_tf.put_row_set( v_new_columns );
end fetch_rows;
end;
/
select *
from ptf_demo.augment( scott.dept ) ;
DEPTNO DNAME LOC THE_NEW_COLUMN
---------- ---------- ---------- ----------------------------------------------------------------------------------------------------
10 ACCOUNTING NEW YORK ACCOUNTING (NEW YORK)
20 RESEARCH DALLAS RESEARCH (DALLAS)
30 SALES CHICAGO SALES (CHICAGO)
40 OPERATIONS BOSTON OPERATIONS (BOSTON)