CODEWITHSUNDEEP
oracle-databasefunctiontypescursor
David Beckman
David Beckman

Reputation:

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

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?

Upvotes: 3

Views: 19321

Answers (2)

FerranB
FerranB

Reputation: 36827

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

Return a 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 );

Upvotes: 3

kurosch
kurosch

Reputation: 2312

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.

Upvotes: 1

Related Questions