Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table table1 (fid, x, y) as
select 1, 'X', sysdate from dual
union all
select 2, 'X', sysdate from dual
2 rows affected

create table table2 (a, b, fid) as
select 'A', sysdate, 3 from dual
union all
select 'A', sysdate, 4 from dual
2 rows affected

create table "table3" ("c", "d", "fid") as
select 'c', sysdate, 5 from dual
union all
select 'd', sysdate, 6 from dual
2 rows affected

create table table4 (fid) as
select 'FID' from dual
1 rows affected

create table table5 (z) as
select 'Z' from dual
1 rows affected

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


XMLTYPE(DBMS_XMLGEN.GETXML('SELECT"'||COLUMN_NAME||'"FROM"'||TABLE_NAME||'"'))


<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <FID>1</FID>
 </ROW>
 <ROW>
  <FID>2</FID>
 </ROW>
</ROWSET>


<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <FID>3</FID>
 </ROW>
 <ROW>
  <FID>4</FID>
 </ROW>
</ROWSET>


<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <fid>5</fid>
 </ROW>
 <ROW>
  <fid>6</fid>
 </ROW>
</ROWSET>



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;


FID


1

2

3

4

5

6


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;


TABLE_NAME
COLUMN_NAME
FID


TABLE1
FID
1

TABLE1
FID
2

TABLE2
FID
3

TABLE2
FID
4

table3
fid
5

table3
fid
6


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;


OWNER
TABLE_NAME
COLUMN_NAME
FID


FIDDLE_UMZHYWUTLGNRXFPTLFMF
TABLE1
FID
1

FIDDLE_UMZHYWUTLGNRXFPTLFMF
TABLE1
FID
2

FIDDLE_UMZHYWUTLGNRXFPTLFMF
TABLE2
FID
3

FIDDLE_UMZHYWUTLGNRXFPTLFMF
TABLE2
FID
4

FIDDLE_UMZHYWUTLGNRXFPTLFMF
table3
fid
5

FIDDLE_UMZHYWUTLGNRXFPTLFMF
table3
fid
6

TABLE_NAME	COLUMN_NAME	FID
TABLE1	FID	1
TABLE1	FID	2
TABLE2	FID	3
TABLE2	FID	4
table3	fid	5
table3	fid	6

OWNER	TABLE_NAME	COLUMN_NAME	FID
FIDDLE_UMZHYWUTLGNRXFPTLFMF	TABLE1	FID	1
FIDDLE_UMZHYWUTLGNRXFPTLFMF	TABLE1	FID	2
FIDDLE_UMZHYWUTLGNRXFPTLFMF	TABLE2	FID	3
FIDDLE_UMZHYWUTLGNRXFPTLFMF	TABLE2	FID	4
FIDDLE_UMZHYWUTLGNRXFPTLFMF	table3	fid	5
FIDDLE_UMZHYWUTLGNRXFPTLFMF	table3	fid	6