Re: inserting comma-separated values with pl/sql

>>>>> "Christoph" == Christoph Seidel <Christoph.Seidel1_at_gmx.de> writes:

 Christoph> Hi there, i have a stored proc which has a varchar
 Christoph> parameter which contains a list of values, separated by
 Christoph> comma.

 Christoph> e.g.: 123,456,789

 Christoph> what i wanna do is to separate the values and insert them  Christoph> into a table

 Christoph> e.g.

 Christoph> insert into t (f) values (123); insert into t (f) values  Christoph> (456); insert into t (f) values (789);

 Christoph> Can this be done with pl/sql? And if i can be done, how?

Yes, quite easily - I did this recently. Essentially, this is what I did.

1. Define a type which was a plsql collection of varchar2 elements e.g.

TYPE tokens IS TABLE OF varchar2(256) INDEXED BY BINARY_INTEGER;

I then defined a function which accepted a comma delimited string as its argument and returned my 'tokens' type.

FUNCTION strToTokens(str IN VARCHAR2) RETURNS tokens;

This function used the instr() function and the substr() function to walk through the comma delimited string, removing each element and storing it into the tokens collection. To allow for strings with ,, in them, I would put a NULL in that position in the collection.

Finally, I just used a plsql loop which processed the tokens collection and inserted each token into a table (if it was not NULL).

There are a few gotchas to watch out for, depending on the contents of your input string. some of which are -

1. handling "empty" fields e.g. this,string,,has,an,empty,field
2. Special characters in your string e.g. this,string,has,'the,a,quote,and,a,&,etc\
3. Qhoted fields with commas e.g. can,you,"have this, or maybe",not
4. Trimming whitespace e.g. this, field ,has,white,space

You can probably think of some others. As you are reading from a database field, you probably won't have al the possibilities listed above, but I tend to think its a good idea if possible when writing packages to do this that you make them as general/generic as possible - odds are in a few months time you will need to process some other comma delimited string and if you solve this general case now, you won't have to re-invent it later.

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!