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

i have set of tables different data type columns , need consolidate way retrieving data. thought using function idea, don't know how define 1 function having different return types.

for example, how define function able use different definitions tabletype.

create or replace function retrieve_info(field_id in integer) return pintegertypetable -- <-- how change return more generic record built dynamically in code below?   r pintegertypetable := pintegertypetable (); begin   r.extend;   r(i) := pintegertypetable (someinteger);   return r; end; 

is possible?. there better way handle problem: different columns stored in lot of legacy tables, , given every column has different data types, in way can retrieve recent information conserving original data types without hardcoding views neither storing in varchar2 , casting again in client code?

you can implement using weakly-typed ref cursor return type. easy implement client interface using jdbc, returned cursor type can stepped through query result , metadata can interrogated resultset.getmetadata(). here's example:

create or replace procedure retrieve_info(field_id in integer, p_cursor in out sys_refcursor) begin   open p_cursor 'select * emp'; end; 

the query in quotes returning type, number of columns.