info about polymorphic table functions

I'm using a polymorphic function (skip_col_not_in_model ) to remove the columns of a table which are not in another table.

CREATE OR REPLACE PACKAGE pkg_skip_col
AS
    FUNCTION skip_col (tab TABLE, col COLUMNS)
        RETURN TABLE
        PIPELINED ROW POLYMORPHIC USING pkg_skip_col;

    FUNCTION describe (tab IN OUT DBMS_TF.TABLE_T, col DBMS_TF.COLUMNS_T)
        RETURN DBMS_TF.DESCRIBE_T;

    FUNCTION skip_col_not_in_model (tab TABLE, tabk_model TABLE)
        RETURN TABLE
        PIPELINED ROW POLYMORPHIC USING pkg_skip_col;

    FUNCTION describe (tab         IN OUT DBMS_TF.TABLE_T,
                       tab_model   IN     DBMS_TF.TABLE_T)
        RETURN DBMS_TF.DESCRIBE_T;
END;

CREATE OR REPLACE PACKAGE BODY pkg_skip_col
AS

    FUNCTION describe (tab         IN OUT DBMS_TF.TABLE_T,
                       tab_model   IN     DBMS_TF.TABLE_T)
        RETURN DBMS_TF.DESCRIBE_T
    AS
    BEGIN
        FOR i IN 1 .. tab.column.COUNT ()
        LOOP
            FOR j IN 1 .. tab_model.column.COUNT ()
            LOOP
                tab.column (i).PASS_THROUGH :=
                    tab.column (i).DESCRIPTION.NAME = tab_model.column (i).DESCRIPTION.NAME ;
                EXIT WHEN tab.column (i).PASS_THROUGH;
            END LOOP;
        END LOOP;

        RETURN NULL;
    END;

    FUNCTION describe (tab IN OUT DBMS_TF.TABLE_T, col DBMS_TF.COLUMNS_T)
        RETURN DBMS_TF.DESCRIBE_T
    AS
    BEGIN
        FOR i IN 1 .. tab.column.COUNT ()
        LOOP
            FOR j IN 1 .. col.COUNT ()
            LOOP
                tab.column (i).PASS_THROUGH :=
                    tab.column (i).DESCRIPTION.NAME != col (j);
                EXIT WHEN NOT tab.column (i).PASS_THROUGH;
            END LOOP;
        END LOOP;

        RETURN NULL;
    END;

END;
/

I can compile the body. But not the spec.

[Error] Compilation (24: 59): PLS-00766: more than one parameter of TABLE type is not allowed

Similary to this query

  with a (a1,a2) as (select 1,2 from dual) select * from 
pkg_skip_col.skip_col(a,COLUMNS(a1))

a1:1

I would like to do that

  with a (a1,a2) as (select 1,2 from dual),
       b (a1,a2,a3) as (select 1,2,3 from dual)
select * from  pkg_skip_col.skip_col_not_in_model(a,b)

a1:1, a2: 2 (expected resulted only)

The problem comes from the fact that I can't give two table.

to avoid this problem, I can give the table_model as a string, get the column (search in table all_tab_columns ). But the code is not so easy to understand anymore und doesn't work with common table expression. Has somebody ideas of improvement?

code

CodePudding user response：

You can do this (from 19.6) using SQL macros. Unlike polymorphic table functions, these allow you to have many dbms_tf.table_t arguments.

SQL macros return the text of a SQL expression which is constructed at parse time. So here you can build the select list similar to the method you've used in the PTF.

For example:

create or replace function skip_col_not_in_model ( 
  query_table dbms_tf.table_t,
  ref_table   dbms_tf.table_t
) 
  return clob sql_macro as
  select_list clob;
  stmt        clob;
begin

  <<query_cols>>
  for i in 1 .. query_table.column.count loop
    for j in 1 .. ref_table.column.count loop
      if query_table.column(i).description.name = ref_table.column(j).description.name then
        select_list := 
            select_list 
         || query_table.column(i).description.name
         || ',';
         continue query_cols;
      end if;
    end loop;
  end loop;
  
  stmt := 'select ' 
       || rtrim ( select_list, ',' ) 
       || ' from query_table';
  
  return stmt;
  
end skip_col_not_in_model;
/

with a (a1,a2) as (
  select 1,2 from dual
), b (a1,a2,a3) as (
  select 1,2,3 from dual
)
select * 
from   skip_col_not_in_model ( a, b );

        A1         A2
---------- ----------
         1          2

You can convert any describe only polymorphic table to a macro.

to avoid this problem, I can give the table_model as a string

In general this doesn't work! As the docs say:

While the constant scalar values are passed as-is to the DESCRIBE function, all other values are passed as NULLs.

So if you use a bind variable to pass the table name, it's value is null.

In this example, notice that when using :new_col for the string parameter val its null in the describe:

create or replace package ptf_pkg
as
  function select_val (tab table, val varchar2)
    return table
    pipelined row polymorphic using ptf_pkg;

  function describe ( tab in out dbms_tf.table_t, val varchar2 )
    return dbms_tf.describe_t;
end;
/

create or replace package body ptf_pkg
as

  function describe ( tab in out dbms_tf.table_t, val varchar2 )
      return dbms_tf.describe_t
  as
  begin
  
    dbms_tf.trace ( 'val = ' || nvl ( val, 'IT IS NULL' ) );
    return null;
    
  end;
  
end;
/

select * from ptf_pkg.select_val ( dual, 'Y' );

D
-
X

val = Y

var new_col varchar2(30);
exec :new_col := 'NEW_COL';

select * from ptf_pkg.select_val ( dual, :new_col );

D
-
X

val = IT IS NULL