Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    5

    Unanswered: TableValuedFunction to Oracle10g

    Hi,

    i've to migrate a TableValuedFunction from SqlServer to Oracle 10g.
    This function gets a comma seperated List of GUIDs, splitts and inserts them
    into the return value to the function.

    Now we are able to work with the function as a normal table like

    SqlServer2005:
    SELECT COUNT(*) FROM SplitIDs(@IDs);

    Here is the function:
    Code:
    ALTER FUNCTION [dbo].[SplitIDs]
    (
    	@IDList varchar(MAX)
    )
    RETURNS 
    @ParsedList table
    (
    	ID UNIQUEIDENTIFIER
    )
    AS
    BEGIN
    	DECLARE @CurrentID varchar(36), @Pos int
    
    	SET @IDList = LTRIM(RTRIM(@IDList))+ ','
    	SET @Pos = CHARINDEX(',', @IDList, 1)
    
    	IF REPLACE(@IDList, ',', '') <> ''
    	BEGIN
    		WHILE @Pos > 0
    		BEGIN
    			SET @CurrentID = LTRIM(RTRIM(LEFT(@IDList, @Pos - 1)))
    			IF @CurrentID <> ''
    			BEGIN
    				INSERT INTO @ParsedList (ID) 
    				VALUES (CAST(@CurrentID AS UNIQUEIDENTIFIER)) --Use Appropriate conversion
    			END
    			SET @IDList = RIGHT(@IDList, LEN(@IDList) - @Pos)
    			SET @Pos = CHARINDEX(',', @IDList, 1)
    
    		END
    	END	
    	RETURN
    END
    How can i migrate this to Oracle10G.

    Thx a lot.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try:
    Code:
    SQL> create or replace function split_ids (p_ids varchar2) return id_tab
      2  is
      3    l_tab id_tab := id_tab();
      4    l_ids long := p_ids;
      5    l_pos integer;
      6  begin
      7    l_pos := instr(l_ids, ',');
      8    while l_pos > 0 loop
      9       l_tab.extend;
     10       l_tab(l_tab.count) := substr(l_ids, 1, l_pos-1);
     11       l_ids := substr(l_ids,l_pos+1);
     12       l_pos := instr(l_ids, ',');
     13    end loop;
     14    l_tab.extend;
     15    l_tab(l_tab.count) := l_ids;
     16    return l_tab;
     17  end;
     18  /
    
    Function created.
    
    SQL> select * from table(split_ids('a,b,c,d'));
    
    COLUMN_VALUE
    ------------------------------------
    a
    b
    c
    d
    Note the requirement to use the TABLE() syntax in the SELECT statement.

  3. #3
    Join Date
    Apr 2009
    Posts
    5

    Thumbs up

    Hi,

    thx for the solution, works fine.

Posting Permissions

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