Convert T-SQL function to PL/SQL

Question

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.

@Lieven, the OP does not specify Oracle 9i. Oracle 10g and 11g have some much more powerful features to split strings than 9i had. — Ollie, Nov 18 '11 at 10:10
@Ollie - you are right but in essence, all the TSQL is doing is splitting a string. Plenty of examples to find on that for any given environment. +1 on *your* answer btw. — Lieven Keersmaekers, Nov 18 '11 at 10:15
See this one as well: http://stackoverflow.com/questions/8099430/does-oracle-provide-the-way-of-returning-multiple-substrings-from-a-string-clob/8100085#8100085 — , Nov 18 '11 at 10:18

Ollie · Answer 1 · 2011-11-18T11:27:34.377

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

Ollie, thank you for the answer, but could you modify the function so it returns a REF CURSOR. — llasarov, Nov 18 '11 at 10:47

Kevin Burton · Accepted Answer · 2011-11-18T11:53:42.877

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

Convert T-SQL function to PL/SQL

2 Answers2