Select data from all tables which contain these data-column

Question

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

There's probably a better answer out there but in plsql you could use results from a to put together dynamic SQL (do a bit of looping to construct your unioned select as text then execute that text as dynamic SQL - I can't remember syntax but googling would find that quickly (just a quick thought hence comment rather than answer) — Chris, Nov 11 '21 at 16:59

Alex Poole · Answer 1 · 2021-11-16T15:13:27.413

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 dbms_xmlgen.getxmltype(
       '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 dbms_xmlgen.getxmltype(
         '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, dbms_xmlgen.getxmltype(
         '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, dbms_xmlgen.getxmltype(
         '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

The basis for this trick goes back to at least 2007 but may be even older, from before getxmltype() existed (it seems to have been added in 10g); I'd originally used xmltype(getxml()):

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

which works most of the time, but if any of the tables are empty throws "ORA-06502: PL/SQL: numeric or value error".

Looks great, thanks. The basic code: SELECT XMLTYPE ( creates an error: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYS.XMLTYPE", line 272 ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. ....... *Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints. Adding this helped: WHERE ... AND TABLE_NAME LIKE 'H%'; — Pierre de la Verre, Nov 12 '21 at 16:01
@PierredelaVerre - strange, presumably from the XML string being too large, but `getxml()` returns a CLOB, and `xmltype()` should handle that. But I've added an alternative that avoids that conversion - it would be interesting to see if that gets the same error. — Alex Poole, Nov 12 '21 at 16:14
Strange: "select * from user_tab_columns where upper(column_name) = 'FID'" returns 70 lines. Your simplified statement works, but returns 21 "NULL" and 49 "(XMLTYPE)" — Pierre de la Verre, Nov 16 '21 at 10:17
Oh, right... so 21 of those tables with FID are empty, I guess. That would explain why it errored with the first version. That didn't come up with the count version this was based on, as that would still return zero; or in data versions I've used it for before, where I must just not have had empty tables to deal with. The simpler version is better anyway though. — Alex Poole, Nov 16 '21 at 10:27
I tested your fiddle-example with setting "Oracle 11g" - it creates errors. So thanks a lot for your help- at the moment my Oracle seem to be too old. Have a fine day! — Pierre de la Verre, Nov 22 '21 at 11:15

frifle · Answer 2 · 2021-11-11T18:44:22.713

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

Select data from all tables which contain these data-column

2 Answers2

Linked