Oracle-L: RE: FUNCTION.

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Thu, 28 Mar 2002 04:58:48 -0800
Message-ID: <F001.00435CFF.20020328045848@fatcity.com>

Hamid,

here is a simple proc to do what you want:

CREATE OR REPLACE PROCEDURE Str_Insert (

      p_recipient   IN   VARCHAR2
                      ) IS

      l_recipient        VARCHAR2(200);
      start_indx         NUMBER := 1;
      str_length         NUMBER;

BEGIN
         start_indx := 1;
         str_length := LENGTH(p_recipient) + 1;
         LOOP
            l_recipient := RTRIM(SUBSTR(p_recipient,start_indx,

INSTR(p_recipient||',',',',start_indx)-start_indx+1),',');

your insert statement would go here

            start_indx := INSTR(p_recipient||',',',',start_indx) + 1;
            EXIT WHEN start_indx >= str_length;
         END LOOP;

END;
/

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Wednesday, March 27, 2002 5:18 PM
To: Multiple recipients of list ORACLE-L

HI LIST, I need some help to write a function or procedure to insert into a table, the input of this function will be a comma delimiter string like (aaa,bbbc,d,f,ggg) and the the result would be 4 records inserted into the table.the number of input could be diffrent.

tableA

1 aaa
2 bbbc
3 d
4 f
5 ggg

Thanks in Advance,

Hamid Alavi
Office 818 737-0526
Cell 818 402-1987

The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Hamid Alavi
INET: hamid.alavi_at_quovadx.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------