Oracle DBMS_TF: introduction

The following example is quite possibly the most simple example for a polymorphic table function using dbms_tf. Because of its simplicity, it also does not do anything useful.

This example also demonstrates that the package's describe function is called once per passed table. That is, the second invocation of select * from polymorphic_intro.pass_table_on(table_ptf); does not call describe anymore, as is shown by the output of the dbms_output.put_line.

create or replace package polymorphic_intro as

    function describe (tab in out dbms_tf.table_t)
               return dbms_tf.describe_t;

 --
 -- Note: There is only a specification for this function, but
 -- no implementation.
 --
    function pass_table_on(tab in table)
                return table
                pipelined row polymorphic
                using polymorphic_intro;

end polymorphic_intro;
/

create or replace package body polymorphic_intro AS

    function describe (tab in out DBMS_TF.table_t)
             return dbms_tf.describe_t as
    begin
        dbms_output.put_line('poly_pkg.describe was called.');
        return null;
    end describe;

end polymorphic_intro;
/

show errors

create table table_ptf (
   num integer,
   txt varchar2(10)
);

create table table_ptf_same_structure (
   num integer,
   txt varchar2(10)
);

create table table_ptf_different_structure (
   txt varchar2(10),
   num  integer
);


insert into table_ptf values (1, 'one'  );
insert into table_ptf values (2, 'two'  );
insert into table_ptf values (3, 'three');

insert into table_ptf_same_structure values (4, 'four');
insert into table_ptf_same_structure values (5, 'five');

insert into table_ptf_different_structure values ('six'  , 6);
insert into table_ptf_different_structure values ('seven', 8);

commit;

select * from polymorphic_intro.pass_table_on(table_ptf);
select * from polymorphic_intro.pass_table_on(table_ptf_same_structure);
select * from polymorphic_intro.pass_table_on(table_ptf_different_structure);
select * from polymorphic_intro.pass_table_on(table_ptf);

drop package polymorphic_intro;

drop table table_ptf                     purge;
drop table table_ptf_same_structure      purge;
drop table table_ptf_different_structure purge;

This example extends the functionality of the prvious one in that it allows to use the polymorphic table with the columns(…) pseudo operator.

Similar to the previous example, describe is only called when a new combination of table and/or column names is passed to the polymorphic function.

create or replace package polymorphic_intro as


    function describe (
                  tab  in out dbms_tf.table_t,
                  cols in out dbms_tf.columns_t
               )
               return dbms_tf.describe_t;

    function pass_table_on(
               tab   in table,
               cols  in columns
             )
             return table
                pipelined row polymorphic
                using polymorphic_intro;


end polymorphic_intro;
/

create or replace package body polymorphic_intro AS

    function describe (
                  tab  in out dbms_tf.table_t,
                  cols in out dbms_tf.columns_t
               )
               return dbms_tf.describe_t as

    begin

        dbms_output.put_line('describe is being called, number of columns: ' || cols.count);
        for colNo in 1 .. cols.count loop
            dbms_output.put_line('  ' || cols(colNo));
        end loop;

        return null;
    end describe;

end polymorphic_intro;
/

sho err

create table table_ptf1 (
   num integer,
   txt varchar2(10)
);

insert into table_ptf1 values (1, 'one'  );
insert into table_ptf1 values (2, 'two'  );
insert into table_ptf1 values (3, 'three');


commit;

select * from polymorphic_intro.pass_table_on(table_ptf1, columns(a      ));
select * from polymorphic_intro.pass_table_on(table_ptf1, columns(bc, def));
select * from polymorphic_intro.pass_table_on(table_ptf1, columns(a      ));
select * from polymorphic_intro.pass_table_on(table_ptf1, columns("foo"  ));
select * from polymorphic_intro.pass_table_on(table_ptf1, columns("bar"  ));
select * from polymorphic_intro.pass_table_on(table_ptf1, columns(a_very_very_very_very_very_very_very_very_very_very_long_column_name));

drop package polymorphic_intro;

drop table table_ptf1                     purge;