title: Convert T-SQL function to PL/SQL
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/1089508/how-to-best-split-csv-strings-in-oracle-9i">How to best split csv strings in oracle 9i</a>  </p>
  </blockquote>
  
  
  
  <p>can you help me convert following T-SQL funcntion into Oracle. The function converts a string like </p>
  
  <pre><code>service|nvretail;language|de;yyyy|2011;
  </code></pre>
  
  <p>to a table.</p>
  
  <p>The main problem I have is the usage of the temp table. I could not find any equivalent to it in Oracle.</p>
  
  <pre><code>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) &lt;&gt; @Delimiter1
      SET @paraString = @paraString + @Delimiter1
  
    SET @Pos1 = charindex(@Delimiter1, @paraString)
    WHILE (@pos1 &lt;&gt; 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 &gt; 0)
      begin
        insert into @para 
          values
        (substring(@NextString, 1, @Pos2 - 1),
         substring(@NextString, @Pos2 + 1, len(@NextString)))
      end
    END 
    return;
  end  
  </code></pre>
  
  <p>Thank you in advance.</p>