0

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...

Wing Kin Chan
  • 23
  • 2
  • 7
  • 1
    Unclear to me. What are db1 and db2? What is SFS_APPLS? – J. Chomel Jan 19 '17 at 07:52
  • You are trying to write a PL/SQL function that returns rows of dynamically determined columns? I think this is not possible - or at least not accessible with SQL. As long as you stay within PL/SQL you can do this, but I wonder what you would do with the result there. The `ORDER BY` clauses in your last query are superfluous by the way. And what are filed1 and filed2 anyway? Would you even select these columns? And `UNION` is made to remove duplicates from results. Is this really intended? Please tell us what you are trying to achieve. – Thorsten Kettner Jan 19 '17 at 08:57
  • Your best bet may be not to use PL/SQL at all, but some other programming language (C#, PHP, whatever). Execute the first query, use it to build the second SQL string, execute that query, show the results in a grid or table. – Thorsten Kettner Jan 19 '17 at 09:17

1 Answers1

0

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.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • I want to get the common column on two table for viewing, but they are different structure, so that I would like to get the common column on user_tab_columns and select the row on this two table – Wing Kin Chan Jan 19 '17 at 09:33
  • This is quite complicated with PL/SQL. As mentioned, you *can* use a refcursor as shown, but you would have to get the column descriptions for the refcursor in order to access it (described here: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2109642600346678459), but it's usually much easier from outside with Java, C# or whatever other programming language. – Thorsten Kettner Jan 19 '17 at 09:38