how to select a column by user_tab_columns selection in oracle sql?

Question

refer to topic, I have create a sql to get the common filed on user_tab_table:

select SUBSTR (SYS_CONNECT_BY_PATH (COLUMN_NAME , ','), 2) into str
  from (select COLUMN_NAME , ROW_NUMBER () over (order by COLUMN_NAME ) RN,
               COUNT (*) OVER () cnt
          from USER_TAB_COLUMNS
          where TABLE_NAME in( 'tb1','tb2')
          group by COLUMN_NAME
          having  COUNT(*) >= 2
          )
 WHERE rn = cnt
start with RN = 1
connect by RN = prior RN + 1;

so that I got the column string, but after that, I have no idea to apply(or combine) on this sql:

select {result}
from tb1
order by filed2;    
union 
select {result}
from tb2
order by filed1;

can some one give me some tips on it? thx for help...

Answer 1

Here is how to write a PL/SQL function to get a cursor for the query where you select the common columns from tb1 and tb2.

create or replace function get_common_columns
  return sys_refcursor
as
  v_str     varchar2(10000);
  v_sql     varchar2(10000);
  v_cursor  sys_refcursor;
begin
  select listagg(column_name, ',') within group(order by column_name)
  into v_str
  from
  (
    select column_name
    from user_tab_columns
    where table_name in ('tb1', 'tb2')
    group by column_name
    having count(*) >= 2
  );

  v_sql := 'select ' || v_str || ' from tb1 ' ||
           'union ' ||
           'select ' || v_str || ' from tb2 ' ||
           'order by ' || v_str;

  open v_cursor for v_sql;
  return v_cursor;
end get_common_columns;

Only, what do you want to do with the result? Unfortunately you cannot access it from SQL (e.g. select * from table(get_common_columns) does not work). You can only use this inside PL/SQL. And even this will get hard, because you would usually need to know what's in a refcursor (i.e. which columns) in order to work with it.

Having said this, you may want to achieve what you have in mind with some completely different approach.