Oracle and functions which return a resultset

I knew it was possible to do this. But what I want in fact is to use that function in a select clause. Almost all examples found on Internet explain how to return a result set from a function using SYS_REFCURSOR. In that manner you won't be able to use select statements on the resultset. In addition, problems appear when you want to create a report base on a certain stored function/procedure. Fo instance, I have tried using Crystal Reports for Java with a stored procedure which returns a resultset. Instead of getting the dataset, Crystal Reports returned an error complaining about the stored procedure.

The alternative would to use a select statement in crystal reports which hides my stored function/procedure. To achieve this, I will use pipelined functions.

Step 1:

create or replace type tp_Person is object(SN NUMBER,
FirstName VARCHAR2(200),
LastName VARCHAR2(200));

Step 2:

create or replace type tp_TblPersons is table of tp_person index by binary_integer;

Step 3:

create or replace function fc_TestPersons return tp_tblpersons pipelined
as
cursor c is select * from persons;
rec tp_Person;
begin
OPEN c;

rec := tp_Person(null, null, null);

LOOP
FETCH c INTO rec.sn, rec.firstname, rec.lastname;
exit when c%notfound;

pipe row(rec);
END LOOP;

CLOSE c;
return;
end fc_TestPersons;

Step 4:

In the end all you have to do is to write a select statement like the following one:

SELECT * FROM table(fc_TestPersons());

Final words:

This feature of Oracle PL/SQL language is extremely useful. You must use it when you are sure there are no others options. It uses oracle buffers and memory, so intense use of it might result in low performance.