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
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()
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
Comments