Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: utl_smtp.rcpt and multiple emails
On Sep 28, 6:13 pm, jobs <j..._at_webdos.com> wrote:
> thank you.
>
> now if only i can figure out how to split a string in 9i. This
> apparently is a 10 g thing.
>
> myarray := split(sendto, ',');
Nothing very complex here, writing a split function is pretty trivial in PL/SQL (assuming that myarray is a PL/SQL collection, if it's an SQL collection type (a nested table or a VARRAY) then you will need to extend the array before adding value to it using array.extend() method):
function split( s in varchar2, delim in varchar2 := ',') return
myarray
is
rv myarray; ix pls_integer := 1; -- index into the array d_pos pls_integer; -- delimiter positionl_s varchar2(32765) := s; -- working local copy of the string begin
d_pos := instr(l_s,delim); -- next delimiter position if d_pos > 0 then -- found it rv(ix) := substr(l_s,1,d_pos-1); -- extract next token ix := ix + 1; -- advance the array index (sometimes I miss C ++ syntax... :)) l_s := substr(l_s, d_pos+1); -- trim the token from thesource string
else
rv(ix) := l_s; -- there's only one token in the source string l_s := null; -- indicate that we're doneend if;
If you are going to create this function standalone (that is, not in a package,) then I'd suggest that you use a nested table type for output - you'll have an additional benefit of being able to query this function as if it was a table ( SELECT * FROM TABLE(split(...)) ) and make it pipelined to speed up things a bit. See the docs for details on these features if you are not familiar with them (TABLE() function and pipelined functions.)
Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Fri Sep 28 2007 - 10:41:40 CDT