How can a table be returned from an Oracle function without a custom type or cursor?

Question

I am looking to return a table of results from an Oracle function. Using a cursor would be easiest, but the application I have to work this into will not accept a cursor as a return value. The alternative is to create a type (likely wrapped in a package) to go along with this function. However, it seems somewhat superfluous to create several types (I have 4+ functions to write) just so I can return table results. Is there an alternative that I am missing?

Answer 1

UPDATE: See the first comment for TABLE solution withou limit of size.

Return an VARRAY or use a PIPELINED function to query from them.

• For VARRAY look in this article for a detail. Code example from there:

  CREATE OR REPLACE TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(30)
  /
  
  
  CREATE OR REPLACE FUNCTION getEmpArray RETURN EMPARRAY
  AS
    l_data EmpArray := EmpArray();
    CURSOR c_emp IS SELECT ename FROM EMP;
  BEGIN
    FOR emp_rec IN c_emp LOOP
      l_data.extend;
      l_data(l_data.count) := emp_rec.ename;
    END LOOP;
  RETURN l_data;
  

  END;

• For PiPELINED functions checkout here. Code example:

  create or replace function Lookups_Fn return lookups_tab
    pipelined
  is
    v_row lookup_row;
  begin
    for j in 1..10
    loop
      v_row :=
        case j
          when 1 then lookup_row ( 1, 'one' )
          --...
          when 7 then lookup_row ( 7, 'seven' )
          else        lookup_row ( j, 'other' )
        end;
      pipe row ( v_row );
    end loop;
    return;
  end Lookups_Fn;
  /
  
  
  select * from table ( Lookups_Fn );
  

Answer 2

You could always return XML from your function if that suits the application developers.

XML can be generated a number of ways in Oracle, depending on what you have installed and what version you're using.

XMLTYPE is very useful in certain contexts, and can be generated from SQL using the XMLElement, XMLAttributes, XMLAgg, etc. built-in functions. If the client doesn't support XMLTYPE it can easily be cast to CLOB values.

Perhaps the simplest, though not the best (IMO) option is to use the dbms_xmlgen package:

SQL> set serveroutput on size 1000;
SQL> exec dbms_output.put_line( dbms_xmlgen.getXML( 'select * from dual' ) );

Output:

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DUMMY>X</DUMMY>
 </ROW>
</ROWSET>

That gives you your "table" results in a single CLOB value.