( I think it could be a simple question for the most users here ..)

Short description: I need a way (maybe with PL/SQL which I don't know ..) to "select defined data from all tables which contain this type of data"

Long description (example): I have a different number of different tables. An often changing part of them - I don't know the number and the names - contains the column "FID". Now I need two steps: a) Select all tables which contain the column "FID". ( I know how to do this as single step) b) Select from all found tables the value FID and show it.

For me the problem is the step from a) to b). With known tables I would use UNION, but with a dynamic result of tables I have no idea ..

CodePudding user response：

You could use a variation on an XML magic trick, by using dbms_xmlgen to get all the values into XML documents based on a query against user_tab_columns:

select xmltype(dbms_xmlgen.getxml(
       'select "' || column_name || '" from "' || table_name || '"'))
from user_tab_columns
where upper(column_name) = 'FID'
and data_type = 'NUMBER';

... where I'm assuming FID is expected to be a numeric ID, so limiting only to numeric columns (and also allowing for mixed case/quoted identifiers for table and columns names, just in case). That gives one row per table, with an XML document listing the FID values in that table.

Then from that XML you can extract the individual values, again as numbers:

with cte (xml) as (
  select xmltype(dbms_xmlgen.getxml(
         'select "' || column_name || '" as fid from "' || table_name || '"'))
  from user_tab_columns
  where upper(column_name) = 'FID'
  and data_type = 'NUMBER'
)
select x.fid
from cte
cross apply xmltable(
  '/ROWSET/ROW'
  passing cte.xml
  columns fid number path 'FID'
) x;

Or if you want to see the table/column each value came from, just include those in the CTE and select list:

with cte (table_name, column_name, xml) as (
  select table_name, column_name, xmltype(dbms_xmlgen.getxml(
         'select "' || column_name || '" as fid from "' || table_name || '"'))
  from user_tab_columns
  where upper(column_name) = 'FID'
  and data_type = 'NUMBER'
)
select cte.table_name, cte.column_name, x.fid
from cte
cross apply xmltable(
  '/ROWSET/ROW'
  passing cte.xml
  columns fid number path 'FID'
) x;

If you want to search other schemas, then use all_tab_columns instead, and optionally include each table's owner:

with cte (owner, table_name, column_name, xml) as (
  select owner, table_name, column_name, xmltype(dbms_xmlgen.getxml(
         'select "' || column_name || '" as fid from "' || owner || '"."' || table_name || '"'))
  from all_tab_columns
  where upper(column_name) = 'FID'
  and data_type = 'NUMBER'
)
select cte.owner, cte.table_name, cte.column_name, x.fid
from cte
cross apply xmltable(
  '/ROWSET/ROW'
  passing cte.xml
  columns fid number path 'FID'
) x;

db<>fiddle

CodePudding user response：

If you want to use pl/sql I really love pipelined functions:

create type result_type as Object ( text varchar2(2000) );

create type result_type_table as table of result_type;

create or replace function select_all( p_column_name in varchar2 )
return result_type_table
deterministic
pipelined
as
  v_table_name varchar2(40);
  v_result result_type := result_type('');
  v_table_name_cursor sys_refcursor;
  v_inner_cursor sys_refcursor;
begin
   open v_table_name_cursor
   for 'select a.table_name
        from user_tab_cols a
        ,    user_tables b
        where a.column_name = :1
        and   a.table_name = b.table_name'
   using upper(p_column_name);
   loop
      fetch v_table_name_cursor into v_table_name;
      exit when v_table_name_cursor%notfound;
      open v_inner_cursor
      for 'select '||p_column_name||' from '||v_table_name;
      loop   
         fetch v_inner_cursor into v_result.text; 
         exit when v_inner_cursor%notfound;
         pipe row (v_result );
      end loop;
      close v_inner_cursor;  
   end loop;
   close v_table_name_cursor;
end;
/

Using this function is simple:

select * from table( select_all('your_column_name') );

db<>fiddle