Q: Convert T-SQL function to PL/SQL 
Possible Duplicate:
How to best split csv strings in oracle 9i 

can you help me convert following T-SQL funcntion into Oracle. The function converts a string like 
service|nvretail;language|de;yyyy|2011;

to a table.
The main problem I have is the usage of the temp table. I could not find any equivalent to it in Oracle.
CREATE FUNCTION [dbo].[TF_ConvertPara]
(   
  @parastringNVARCHAR(max)
)
RETURNS @para TABLE 
(
  [Key]    varchar(max),
  [Value]  varchar(max)
)
begin
  DECLARE @NextString NVARCHAR(40)
  DECLARE @Pos1       INT
  DECLARE @Pos2       INT
  DECLARE @NextPos    INT
  DECLARE @Delimiter1 NCHAR=';'
  DECLARE @Delimiter2 NCHAR='|'

  if substring(@paraString, len(@paraString) - 1, 1) <> @Delimiter1
    SET @paraString = @paraString + @Delimiter1

  SET @Pos1 = charindex(@Delimiter1, @paraString)
  WHILE (@pos1 <> 0)
  BEGIN
    SET @NextString = substring(@paraString, 1, @Pos1 - 1)
    SET @paraString = substring(@paraString, @pos1 + 1, len(@paraString))
    SET @pos1 = charindex(@Delimiter1, @paraString)

    SET @Pos2 = charindex(@Delimiter2, @NextString)
    if (@Pos2 > 0)
    begin
      insert into @para 
        values
      (substring(@NextString, 1, @Pos2 - 1),
       substring(@NextString, @Pos2 + 1, len(@NextString)))
    end
  END 
  return;
end  

Thank you in advance.

A: If you are looking to return the tokens of your string as rows then you could do it all in a single SQL query:
WITH t AS (SELECT 'service|nvretail;language|de;yyyy|2011;' as val FROM dual)
SELECT row_output
  FROM t,
       XMLTABLE( '/root/e/text()'
          PASSING xmltype( '<root><e>' || 
                            REGEXP_REPLACE (t.val, '(;|\|)', '</e><e>' ) || 
                           '</e></root>' )
                  COLUMNS row_output VARCHAR2( 4000 ) PATH '/');

Returns:

service 
nvretail
language
de      
yyyy    
2011


If you want these rows in a table then wrap the SQL in a simple function to either return the in-memory table or insert the records into a DB table, whichever you need.
Here is an example of a function returning a table (collection) of VARCHAR2 records:
CREATE OR REPLACE
FUNCTION str_to_table (
   p_input_str IN VARCHAR2
)
RETURN DBMS_SQL.VARCHAR2_TABLE
IS
   CURSOR str_to_rows (
      cp_str IN VARCHAR2
   )
   IS
      SELECT row_output
        FROM (SELECT cp_str as val FROM dual) t,
             XMLTABLE( '/root/e/text()'
                PASSING xmltype( '<root><e>' || 
                        REGEXP_REPLACE(t.val, '(;|\|)', '</e><e>') || 
                                 '</e></root>' )
                        COLUMNS row_output VARCHAR2( 4000 ) PATH '/');

   varchar_tab DBMS_SQL.VARCHAR2_TABLE;
BEGIN
   OPEN  str_to_rows(p_input_str);
   FETCH str_to_rows BULK COLLECT INTO varchar_tab;
   CLOSE str_to_rows;
   --
   RETURN varchar_tab;
EXCEPTION
   WHEN others
   THEN
      IF str_to_rows%ISOPEN
      THEN
         CLOSE str_to_rows;
      END IF;
      RAISE;
END str_to_table;

It's untested so there may be a couple of syntax errors but it should be prety close.
To return a ref cursor:
CREATE OR REPLACE
FUNCTION str_to_table (
   p_input_str IN VARCHAR2
)
RETURN sys_refcursor
IS                       
   cur SYS_REFCURSOR;
BEGIN
   OPEN cur
    FOR SELECT row_output
          FROM (SELECT p_input_str as val FROM dual) t,
               XMLTABLE( '/root/e/text()'
                         PASSING xmltype( '<root><e>' || 
                         REGEXP_REPLACE (t.val, '(;|\|)', '</e><e>' ) || 
                                          '</e></root>' )
                         COLUMNS row_output VARCHAR2( 4000 ) PATH '/');
   --
   RETURN cur;
END str_to_table;

Hope it helps...

A: To return the key value pairs, How about this (for Oracle 10g and above):
CREATE OR REPLACE FUNCTION csv_to_rows(mycsv IN VARCHAR2)
RETURN sys_refcursor
AS
  c SYS_REFCURSOR;
BEGIN
    OPEN c FOR         
        SELECT SUBSTR(element,1,INSTR(element,'|')-1) as key ,
        SUBSTR(element,INSTR(element,'|')+1,99999) as val
        FROM (
            SELECT REGEXP_SUBSTR(mycsv,'[^;]+',1,LEVEL) element          
                FROM dual      
                CONNECT BY LEVEL < LENGTH(REGEXP_REPLACE(mycsv,'[^;]+')) + 1
             );
    RETURN c;     
END;

and to see your results:
DECLARE 
    c SYS_REFCURSOR;
    akey VARCHAR2(100);
    aval VARCHAR2(100);
BEGIN
 c := csv_to_rows('service|nvretail;language|de;yyyy|2011');
 LOOP
     FETCH c into akey,aval;
     EXIT WHEN c%NOTFOUND;

     dbms_output.put_line('Key : '||akey || ' Value : '||aval);
 END LOOP;    
END;

this should give you 
Key : service  Value : nvretail
Key : language Value : de
Key : yyyy     Value : 2011

