Sunday, March 28, 2010

How to return multiple rows with oracle function

There are three basic ways to return multiple rows by oracle function:
======================================================================

A) use a refcursor as the return type
B) use an object table as the return type
C) use a mulit-delimited string as a return type (clob most likely)

A) use a refcursor as the return type
======================================

1.

CREATE OR REPLACE PACKAGE TYPES
AS
TYPE ref_c IS REF CURSOR;
END;

2.

CREATE OR REPLACE FUNCTION multiple_return
RETURN TYPES.ref_c
AS
l_cursor TYPES.ref_c;
BEGIN
OPEN l_cursor FOR
SELECT *
FROM emp;
RETURN l_cursor;
END;

3.

SELECT multiple_return
FROM DUAL




B)use an object table as the return type
==========================================

1.

CREATE OR REPLACE TYPE num_tab IS TABLE OF NUMBER;


2.

CREATE OR REPLACE PACKAGE pipeline
AS
FUNCTION get_num_row
RETURN num_tab PIPELINED;
END;


3.

CREATE OR REPLACE PACKAGE BODY pipeline
AS
FUNCTION get_num_row
RETURN num_tab PIPELINED
IS
BEGIN
FOR i IN 1 .. 20
LOOP
PIPE ROW (i);
END LOOP;

RETURN;
END get_num_row;
END;



4.

SELECT * FROM TABLE (pipeline.get_num_row);

3 comments:

Anonymous said...

Clearest most concise example I've seen yet. Thanks! Now how can a table be queried to return records in the "use an object table as the return type"

What I've gathered from other posts there is:
1) a record object created
2) a table of the record objects created
3) an instantiation of the record and/or table objects
4) a select statement that populates the records -- little confused here
5) a return statement returning the table

Cheers,
Tom

Anonymous said...

Thanks for the clear and concise examples.

Little confused on how to select from a table and return records

What I've gathered from other posts there is:
1) a record object created
2) a table of the record objects created
3) an instantiation of the record and/or table objects
4) a select statement that populates the records -- little confused here
5) a return statement returning the table

Anonymous said...

i love u man!