Computer Overlord

Oracle: CSV To String Array Function

So I was having difficulty passing an array of id’s as a parameter from java to an Oracle stored procedure.  I didn’t seem to have many problems doing this in .Net, but java was a right headache for passing this as it was expecting a CERTAIN array type and a CERTAIN DB connection type.

For right or for wrong, I decided to pass the data through as a CSV string instead and have the procedure convert it to an array.  This is the function I used for that:

CREATE OR REPLACE FUNCTION "STRING_SPLIT" 
(str varchar2, 
delimiter varchar2)

     return str_array
as
   val_list str_array;
   head varchar2(4000);
   tail varchar2(4000);
   i number :=1;

   do_loop boolean := true;
begin

-- Initialize array
   val_list := str_array('');
   tail := str;

   while do_loop loop
       head := substr(tail,1,instr(tail,delimiter,1,1)-1);
    if instr(tail,delimiter,1,1) > 0 then
       if i = 1 then
         val_list.DELETE(1);
         val_list(i) := head;
      else
         val_list.EXTEND;
         val_list(i) := head;
      end if;
    else
       if i <> 1 then
         val_list.EXTEND;
       end if;
       val_list(i) := tail;
       do_loop := false;
    end if;
    i := i + 1;
    tail := substr(tail,instr(tail,delimiter,1,1)+1,length(tail));
  end loop;

return val_list;

end;

12pm