How to define a pl sql function with dynamic return types in Oracle?

Question

I have a set of tables with different data type for the columns and I need to consolidate a way for retrieving data. I thought using a function would be a good idea, but I don't know how to define one function having different return types.

For example, how to define this function to be able to use different definitions for tabletype.

CREATE OR REPLACE FUNCTION retrieve_info(field_id in integer)
RETURN pintegertypetable -- <-- how to change this to return a more generic record built dynamically in the code below?
AS
  r pintegertypetable := pintegertypetable ();
BEGIN
  r.extend;
  r(i) := pintegertypetable (someinteger);
  return r;
END;

Is that possible?. Is there a better way to handle this problem: different columns stored originally in a lot of legacy tables, and given that every column has different data types, in which way we can retrieve the most recent information conserving the original data types without hardcoding views neither storing everything in varchar2 and casting again in client code?

Answer 1

You can implement this by using a weakly-typed Ref Cursor as the return type. This is especially easy to implement from a client interface using JDBC, as the returned cursor type can be stepped through just like any query result and the metadata can be interrogated from ResultSet.getMetaData(). Here's an example:

CREATE OR REPLACE PROCEDURE retrieve_info(field_id in integer, p_cursor in out sys_refcursor)
AS
BEGIN
  open p_cursor for 'select * from emp';
END;

The query in quotes could be anything returning any type, for any number of columns.

Answer 2

Using the answer of Datajam Ltd, this could be useful for someone else:

drop table xxx1;
drop table xxx2;
drop table xxx3;
drop table xxx4;

create table xxx1(val integer);
create table xxx2(val date);
create table xxx3(val number);
create table xxx4(val varchar2(100));

insert into xxx1 (val) select rownum from all_objects where rownum <= 1;
insert into xxx2 (val) select sysdate+rownum from all_objects where rownum <= 2;
insert into xxx3 (val) select 12.345+rownum from all_objects where rownum <= 3;
insert into xxx4 (val) select 'test'||rownum from all_objects where rownum <= 4;

CREATE OR REPLACE PROCEDURE retrieve_info(p_cursor in out sys_refcursor, tabname in varchar2) 
AS 
BEGIN 
    open p_cursor for 'select val from ' || tabname; 
END; 
/



-- CLIENT CODE IN PLSQL:

drop table logtable;
create table logtable(x varchar2(1000));

declare
     v_rc sys_refcursor;
     b varchar2(1000);
begin
     retrieve_info(v_rc, 'xxx2'); -- here you can parameterize your call     
     loop
          fetch v_rc into b;
              exit when v_rc%notfound;
              dbms_output.put_line(b);
              insert into logtable(x) values(b);
      end loop;
end;
/

select * from logtable;