TomBecker.net

Looping Through a Comma Delimited String in Oracle

Normally when I have to loop through an list of items to insert into a database I use VB to loop through the array and then insert each item individually. However, this poses some problems if everything has to be done as one unit of work (for more background see previous post). So instead of doing the looping in VB, i had to do the loop in P/L SQL.

I found an article that told me how to do this quite easily. I typed in all the code and syntactically it was correct. I was happy since I thought I was going to have to do some weird string manipulation to go through each of items I was passing in. However what that article doesn't tell you, is that the comma_to_table function does not work for integers. It will throw the following error:

So after about 20 more minutes of research I came across another person having the same problem, and he wrote his own function that would act the same as the comma_to_table function but it would accept numbers in the delimited string.

His function is called delimstring_to_table (code example below) and it works beautifully. He also includes in his util package a table_to_delimstring function that will do the reverse. I haven't used it but I assume that it works just as well.

I am not going to include the package information on my site, you are going to have to visit the above link for that. I am just going to show the use of his functions:

--declare variables
temp_tab util.varchar2_table;
table_len number;

--convert comma delimated array to table.
util.delimstring_to_table('1,4,90,5,2', temp_tab, table_len, ',');

--loop through array and do inserts
for i in 1..table_len
loop
   INSERT INTO someTable
     (ID, description)
   VALUES
     (IN_ID, temp_tab(i));
end loop;

So as you can see looping is pretty easy in P/L SQL. You can use the comma_to_table in place of the delimstring_to_table function if your delimited string is just alpha characters and the loop will work exactly the same way.

Labels: Oracle

posted by Tom Becker at 7/17/2008