How to select only the colums of a table which are the same of another table

user_8458083 0
Viewed 64

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

oracle sql
1 Answers

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
user_14859550
Related