Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    20

    Unanswered: 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
    Alok

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Alternative of Multistatement table-valued function in oracle

    Originally 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...

    Code:
    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
    	)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Posts
    20

    UDF in ORACLE

    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

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    i think they call it "parameterized view" (it's been a while

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: UDF in ORACLE

    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


    Code:
      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;
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: UDF in ORACLE

    Here's a sample function..

    Code:
    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;
    
    /
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •