How can I return a table from an Oracle function without a special type or cursor?

Question

I want to return a table of results from an Oracle function. Using a cursor would be easiest, but the application I have to work with will not accept the cursor as the return value. An alternative is to create a type (probably wrapped in a package) to perform this function. However, it seems somewhat unnecessary to create several types (I have 4+ functions for writing), so I can return the results of the table. Is there an alternative I'm missing?

Answer 1

UPDATE: See the first comment on a TABLE solution with a size limit.

Return VARRAY or use the PIPELINED function to request them.

• For VARRAY, check out this article for more information. Sample code 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 the PiPELINED checkout function 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 can always return XML from your function if it suits application developers.

XML can be generated in several ways in Oracle, depending on what you installed and which version you are using.

XMLTYPE ​​ SQL XMLElement, XMLAttributes, XMLAgg .. XMLTYPE, CLOB.

, , (IMO) dbms_xmlgen:

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

:

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

"" CLOB.