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

If you are interested in casting single columns to strings, the easiest way is to write a package with overloaded functions. Each function has the same name, only the signature of the input parameter(s) varies:

SQL> create or replace package str_util
  2  as
  3      function s (p in varchar2) return varchar2;
  4      function s (p in number) return varchar2;
  5      function s (p in date, fmt in varchar2 := 'dd-mon-yyyy') return varchar2;
  6  end;
  7  /

Package created.

SQL>

This is obviously a simplistic implementation. For production you might want some error handling, numeric format masks, more datatypes, etc.

SQL> create or replace package body str_util
  2  as
  3      function s (p in varchar2) return varchar2
  4      is
  5      begin
  6          return p;
  7      end s;
  8      function s (p in number) return varchar2
  9      is
 10      begin
 11          return to_char(p);
 12      end s;
 13      function s (p in date, fmt in varchar2 := 'dd-mon-yyyy') return varchar2
 14      is
 15      begin
 16          return to_char(p, fmt);
 17      end s;
 18  end;
 19  /

Package body created.

SQL>

In the foolowing query I show the datatypes of three columns of a table:

SQL> select dump(id) id_is_number
  2         , dump(col2) col2_is_varchar
  3         , dump(col3) col3_is_date
  4  from big_table
  5  where rownum = 1
  6  /

ID_IS_NUMBER
------------------------------------------------------
COL2_IS_VARCHAR
------------------------------------------------------
COL3_IS_DATE
------------------------------------------------------
Typ=2 Len=3: 194,5,37
Typ=1 Len=11: 73,95,65,82,71,85,77,69,78,84,49
Typ=12 Len=7: 120,107,10,15,11,10,14


SQL>

In this version of the query I call the packaged function and get three strings back:

SQL> select dump(str_util.s(id)) id_is_number
  2         , dump(str_util.s(col2)) col2_is_varchar
  3         , dump(str_util.s(col3)) col3_is_date
  4  from big_table
  5  where rownum = 1
  6  /

ID_IS_NUMBER
------------------------------------------------------------------
COL2_IS_VARCHAR
------------------------------------------------------------------
COL3_IS_DATE
------------------------------------------------------------------
Typ=1 Len=3: 52,51,54
Typ=1 Len=11: 73,95,65,82,71,85,77,69,78,84,49
Typ=1 Len=11: 49,53,45,111,99,116,45,50,48,48,55


SQL>

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;

Answer 3

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.