Alternative of Multistatement table-valued function in oracle

Hi all,
Can anybody tell me what is the equivalent of Multistatement table-valued function<UDF> in Oracle. I want to make a function which can return a table.
Thanks
AlokOriginally posted by Alokg
Hi all,
Can anybody tell me what is the equivalent of Multistatement table-valued function<UDF> in Oracle. I want to make a function which can return a table.

Thanks
Alok

You're in a different dimension now...

No table or cursor pointers...

but here's some sample code theft...

Create Function dbo.GetCSVTable
(
@.Array varchar(8000),
@.Delimiter varchar(8000) = ','
)
Returns @.table Table (
IndexID int Identity Not Null,
Value varchar(8000),
ValueInt int,
ValueDateTime datetime
)|||I want to write an UDF in ORACLE which can return a table. IN MSSQL we can use Multistatement table-valued functions but do we have some equivalent in ORACLE..
Can someone help me?

Thanks
Alok|||i think they call it "parameterized view" (it's been a while :)|||I see...got it backwards...

This is a sql server board though...

Oracle, unlike sql server doesn't move stuff around they supply pointers..

You should google "reference cursors" or go to tech net (or how about the right forum)

But here's a procedure that uses a ref cursor...I would imagine a function might make use of this way as well

PROCEDURE Get_EligPlanTypes_sp (I_EMPLID IN VARCHAR2,
EligPlanTypesCur OUT CurRefType) IS

BEGIN

--* Retrieve eligible plan types for entry into the plan object.

OPEN EligPlanTypesCur FOR SELECT DISPLAY_PLN_SEQ
,PLAN_TYPE
,OPTION_CD
,ELECTION_MADE
FROM ENR_PARTIC_PLAN
WHERE EMPLID = I_EMPLID
ORDER BY DISPLAY_PLN_SEQ;

EXCEPTION
WHEN OTHERS THEN
RAISE;

END Get_EligPlanTypes_sp;|||Here's a sample function..

CREATE OR REPLACE PACKAGE dba_Functions_Package
AS

Function InstrCount (strValue Varchar2
,strTarget Varchar2)
RETURN NUMBER;

END dba_Functions_Package;

/

CREATE OR REPLACE PACKAGE BODY dba_Functions_Package
AS
-- ************************************************** *******************
-- *** F U N C T I O N (InstrCount) D E C L A R A T I O N S *****
-- ************************************************** *******************

Function InstrCount (strValue IN Varchar2
,strTarget IN Varchar2)
RETURN Number
IS
numOccurs Integer := 0;
numReturn Number := -1;
BEGIN

While numReturn != 0 Loop
numReturn := Instr(strValue,strTarget,1,numOccurs+1);
If numReturn <> 0 Then
numOccurs := numOccurs + 1;
End If;
End Loop While;

RETURN(numOccurs);

EXCEPTION
WHEN OTHERS THEN RAISE;

End InstrCount;

END dba_Functions_Package;

/