I recently had an issue where I needed to retrieve a list of values from a PLSQL function and include the returned list in a SQL “where in” clause.

Eg. “Select * from TB_Debtors where ID in FN_GetOverDueDebtors();”

Obviously the function FN_GetOverDueDebtors could only return one value and therefore this SQL could only ever return one record (assuming that ID is the primary key).

The use of a function in this example may seem to be an over kill as you could simply write something like “where ID in (Select ID from TB_OverDueDebtors)”

However in cases where we would need some more complicated checking and filtering, then a function would be useful.

The Solution

Introducing Pipelined PLSQL Functions:

Pipelined functions were introduced in Oracle 9i and allows a function to become a “virtual table”. Firstly the collection type being returned needs to be defined on the database.

Create or Replace Type IDS as Table of Number;

This creates a data collection type in the DB that can contain numeric values.

Now the function definition:

CREATEFUNCTIONFN_GetOverDueDebtors()

RETURNIDS PIPELINED

IS  

CURSOR cGetDebtors IS SELECT ID, Name, LastPaidDate From TB_Debtors  

Where LastPaidDate > sysdate - 10;

tID          TB_Debtors.ID%Type;

tName          TB_Debtors.Name%Type;                             

tLastPaidDate          TB_Debtors.LastPaidDate%Type;               

BEGIN

Open cGetDebtors;

Loop

Fetch cGetDebtors Into tID, tName, tLastPaidDate;

Exit When cGetDebtors %NotFound;

-- Some extra checking

If tID >= 500 then

If sysdate – tLastPaidDate> 20 then

-- For ids >= 500 we are allowing 20 days to pay

PIPEROW(tID);

End If;

Else

-- For ids < 500 we are allowing 10 days to pay

PIPEROW(tID);

End If;

End Loop;

Return;

END;

The Conclusion

 “Select * from TB_Debtors where ID in FN_GetOverDueDebtors();” will now treat FN_GetOverDueDebtors as a table and return a row for each ID returned by the function.

It can be called directly by wrapping a function with the TABLE operator.

“Select * from TABLE(FN_GetOverDueDebtors());”

I have also found pipelined functions useful in a scenario where I needed to return 10 records even if >10 records matched the where clause.  The function simply piped out rows representing the non-existing records with values of “N/A”.