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

create table table6 (fid) as
select 42 from dual where 1=0
select dbms_xmlgen.getxmltype(
       'select "' || column_name || '" from "' || table_name || '"')
from user_tab_columns
where upper(column_name) = 'FID'
and data_type = 'NUMBER';


DBMS_XMLGEN.GETXMLTYPE('SELECT"'||COLUMN_NAME||'"FROM"'||TABLE_NAME||'"')


<ROWSET>
 <ROW>
  <FID>1</FID>
 </ROW>
 <ROW>
  <FID>2</FID>
 </ROW>
</ROWSET>


<ROWSET>
 <ROW>
  <FID>3</FID>
 </ROW>
 <ROW>
  <FID>4</FID>
 </ROW>
</ROWSET>


<ROWSET>
 <ROW>
  <fid>5</fid>
 </ROW>
 <ROW>
  <fid>6</fid>
 </ROW>
</ROWSET>


null


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;


FID


1

2

3

4

5

6


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;


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, 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;


OWNER
TABLE_NAME
COLUMN_NAME
FID


FIDDLE_FQVIIEXWGQFTIONCOWQI
TABLE1
FID
1

FIDDLE_FQVIIEXWGQFTIONCOWQI
TABLE1
FID
2

FIDDLE_FQVIIEXWGQFTIONCOWQI
TABLE2
FID
3

FIDDLE_FQVIIEXWGQFTIONCOWQI
TABLE2
FID
4

FIDDLE_FQVIIEXWGQFTIONCOWQI
table3
fid
5

FIDDLE_FQVIIEXWGQFTIONCOWQI
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_FQVIIEXWGQFTIONCOWQI	TABLE1	FID	1
FIDDLE_FQVIIEXWGQFTIONCOWQI	TABLE1	FID	2
FIDDLE_FQVIIEXWGQFTIONCOWQI	TABLE2	FID	3
FIDDLE_FQVIIEXWGQFTIONCOWQI	TABLE2	FID	4
FIDDLE_FQVIIEXWGQFTIONCOWQI	table3	fid	5
FIDDLE_FQVIIEXWGQFTIONCOWQI	table3	fid	6