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



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;
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

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;
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

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;
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1