Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: utl_smtp.rcpt and multiple emails

Re: utl_smtp.rcpt and multiple emails

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Fri, 28 Sep 2007 08:41:40 -0700
Message-ID: <1190994100.779275.76010@g4g2000hsf.googlegroups.com>


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 position
  l_s varchar2(32765) := s; -- working local copy of the string begin
  loop
    exit when l_s is null; -- exit when the source string is empty
    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 the
source string

    else

      rv(ix) := l_s;                   -- there's only one token in
the source string
      l_s := null;                     -- indicate that we're done
    end if;
  end loop;
  return rv;
end split;

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US