Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table frifle_1 ( text_column varchar2(100), other_column varchar2(100) );
begin
insert into frifle_1 ( text_column ) values ( 'ONE' );
insert into frifle_1 ( text_column ) values ( 'TWO' );
commit;
end;
/
1 rows affected

create table frifle_2 ( text_column varchar2(100), other_column varchar2(100) );
begin
insert into frifle_2 ( text_column ) values ( 'THREE' );
insert into frifle_2 ( text_column ) values ( 'FOUR' );
commit;
end;
/
1 rows affected

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;
/
select * from table( select_all('text_column') );


TEXT


ONE

TWO

THREE

FOUR