Search

Polymorphic Table Function


ORACLE Database has included Table Functions for a long time. But what if you want to change the columns at runtime based on input parameters? You can write messy dynamic quey, one like i used in of my post earlier. But using a PTF could be preferrable by far as you can generate rows in your result set dynamically. PTF can be used to add or remove the column of a table at run time, convert comma seperated values to column and for dynamic pivot.


Lets explore the add and remove column dynamic functionality of Oracle PTF with use case.



Implementing PTF


1. The PL/SQL package which contains the describe function.

2. The standalone or package function naming the PTF and its associated implementation package.



PTF has the following subprograms.


  • DESCRIBE function (Required) :

  • OPEN procedure (Optional) :

  • FETCH_ROWS procedure (Optional)

  • CLOSE procedure (Optional)



REMOVE COLUMNS : Oracle has provided a routine to remove column which is :


CREATE OR REPLACE PACKAGE test_pkg AS

  FUNCTION fist_ptf(tab IN TABLE,
                  col IN COLUMNS)
  RETURN TABLE PIPELINED
  ROW POLYMORPHIC USING test_pkg;

  FUNCTION describe (tab IN OUT DBMS_TF.table_t,
                     col IN     dbms_tf.columns_t)
    RETURN DBMS_TF.describe_t;

END test_pkg;




CREATE OR REPLACE PACKAGE BODY test_pkg AS

  FUNCTION describe (tab IN OUT DBMS_TF.table_t,
                     col IN     dbms_tf.columns_t)
    RETURN DBMS_TF.describe_t
  AS
  BEGIN
    -- Loop through all the table columns.
    FOR i IN 1 .. tab.column.count() LOOP
      -- Loop through all the columns listed in the second parameter.
      FOR j IN 1 .. col.count() LOOP
        -- Set pass_through to true for any columns not in the exclude list.
        tab.column(i).pass_through := (tab.column(i).description.name != col(j));
        -- Exit inner loop if you find a column that shouldn't be included.
        EXIT WHEN NOT tab.column(i).pass_through;
      END LOOP;
    END LOOP;

    RETURN NULL;
  END;

END test_pkg;

Here its important to know the Pass-through columns. They are passed from the input table of the Polymorphic Table Function (PTF) to the output, without any modifications.

The DESCRIBE function indicates the pass-through columns by setting the COLUMN_T.PASS_THROUGH boolean flag on the input table descriptor, DBMS_TF.TABLE_T.



SELECT * FROM test_pkg.fist_ptf(employees, COLUMNS(job_id,PHONE_NUMBER,HIRE_DATE,COMMISSION_PCT,EMPLOYEE_ID,MANAGER_ID,SALARY)) where department_id = 90;
--------------------------------------------------------------------
 1	Steven	King	SKING	90
2	Neena	Kochhar	NKOCHHAR	90
3	Lex	De Haan	LDEHAAN	90
   	


ADD COLUMNS


Adding a column to the output of a polymorphic table function is a two step process.


1 in describe() define the new column

2 in fetch_rows() populate the new column, this can be performed via either:

  • dbms_tf.put_col()

  • dbms_tf.put_row_set()


Referencing Input Table Data While Creating output Column


  1. in describe() identify the input columns we wish to reference by setting their FOR_READ attribute to TRUE

  2. in fetch_rows() retrieve the FOR_READ=TRUE columns using either:

  • dbms_tf.get_col()

  • dbms_tf.get_row_set()

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) identify the input columns we wish to reference by setting FOR_READ to TRUE
    --

    p_input_table_metadata.column(2).for_read := true ; 
    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_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( employees) ;
---------------------------------------------------------------------
   	


Both Add & Remove Column in PTF(POLYMORHIC TABLE FUNCTION)



create or replace package hide_and_add_cols_pkg as

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

  procedure fetch_rows;

end hide_and_add_cols_pkg;
/

create or replace package body hide_and_add_cols_pkg as

function describe (
  tab        in out dbms_tf.table_t,
  add_cols   dbms_tf.columns_t,
  hide_cols  dbms_tf.columns_t
) return dbms_tf.describe_t as
  new_cols dbms_tf.columns_new_t;
  col_list dbms_tf.columns_t := add_cols;
begin

  for i in 1 .. tab.column.count loop
    if tab.column(i).description.name member of hide_cols then
      tab.column(i).for_read := false;
      tab.column(i).pass_through := false;
    end if;
  end loop;

  for i in 1 .. col_list.count loop

    new_cols(i) := dbms_tf.column_metadata_t (
      name => col_list(i),
      type => dbms_tf.type_number
    );

  end loop;

  return dbms_tf.describe_t (
    new_columns => new_cols
  );

end describe;
procedure fetch_rows
  as
    env       dbms_tf.env_t;
    col       dbms_tf.tab_number_t;
    last_row  pls_integer := 0;
  begin

    env := dbms_tf.get_env();

    for cols in 1 .. env.put_columns.count loop

     
      for rws in 1 .. env.row_count loop
        col ( rws ) := ( rws + last_row ) * cols;
      end loop;

      dbms_tf.put_col ( cols, col );

    

    end loop;

end fetch_rows;

end hide_and_add_cols_pkg;
/
----------------------------------------------------------------------
  
create or replace function hide_existing_add_new_cols (
  tab       table, 
  add_cols  columns, 
  hide_cols columns
) return table pipelined 
  row polymorphic 
  using hide_and_add_cols_pkg;
/ 




select * 
from   hide_existing_add_new_cols ( 
  employees
    order by employee_id, 
  columns ( c1,c2,c3 ), 
  columns ( department_id, SALARY, HIRE_DATE, PHONE_NUMBER,EMPLOYEE_ID, MANAGER_ID,COMMISSION_PCT,EMAIL,JOB_ID ) WHERE DEPARTMEN
);

--------------------------------------------------------------------
1	Steven	King	1	2	3
2	Neena	Kochhar	2	4	6
3	Lex	De Haan	3	6	9


FETCH rows in the above subprogram uses ROW NUMBER * NEW COLUMN POSITION

to populate the new column. In fetch row subprogram DBMS_TF.get_env() holds the current environment. I can now use this to loop through the PUT columns—those added to the result set—and define a value for each row. I can also iterate to the current row and build the values and return them using dbms_tf.put_col.


Summary :


PTF is a powerful feature to dynamically shape the result set, what is done by a PTF can be done by other functions in oracle like the analytical functions and use of dynamic sql but writing those dynamic query could be complex and whacky. Say for example you want the rowtype o/p of all the tables in your schema writing a subprogram with dynamic query that takes the table as input parameter and returns it rowtype colud be tough. A work around could be.



 create or replace procedure genlookup(tab_in in varchar2,col_in in varchar2)
is
begin
dbms_output.put_line('create or replace function ' || tab_in || '_row_for(');
dbms_output.put_line(' ' || col_in||'_in  IN ' || tab_in || '.'||col_in||'%type)');
dbms_output.put_line('return '||tab_in||'%rowtype');
dbms_output.put_line('is');
dbms_output.put_line('l_return '||tab_in||'%rowtype;');
dbms_output.put_line('begin');
dbms_output.put_line('select * into l_return from ' ||tab_in);
dbms_output.put_line('where ' || col_in ||'='||col_in||'_in;');
dbms_output.put_line('return l_return;');
dbms_output.put_line('end;');
end;


begin
for i in (select ccol.table_name,ccol.column_name pkycol_name
            from user_constraints cons,user_cons_columns ccol
             where cons.owner = ccol.owner


             and cons.constraint_name = ccol.constraint_name
             and cons.table_name like '%EMP%' and cons.CONSTRAINT_TYPE = 'P')
 
  loop
   genlookup(i.table_name,i.pkycol_name);
   end loop;
   end;

The Subprogram above might not look as complex as polymorphic table functions but it can be as robust as the former as this workaround will create numerous individual functions to query from.


Please reach out to me for more or PFT.


#oracledatabase#oracle#oracle18c#oracledeveloper#oraclejason#PTF#PolymorphicTableFunction#tablefunction#linkedln#linkedinlearning













16 views
  • Facebook
  • Twitter
  • LinkedIn