Kashif Masood Hashmi Blog

CREATE OR REPLACE TYPE HR.a_email_address as object (email_add VARCHAR2(100))

/

CREATE OR REPLACE TYPE HR.a_email_add as table of a_email_address

/

CREATE OR REPLACE FUNCTION HR.get_email_add (rcplist IN VARCHAR2)

  RETURN a_email_add

  IS

-- rcplist as parameter should contain comma separated data ( if more than 1 recipient )

  l_email_add a_email_add := a_email_add();

  xx number:=1;

  --

Begin

  for rec in (

              select substr(rcplist,

                     instr(','||rcplist||',', ',', 1, rn),

                     instr(','||rcplist||',', ',', 1, rn+1)

                     - instr(','||rcplist||',', ',', 1, rn) - 1) mvalue

                from ( select level rn from dual

             connect by level <= length(rcplist)-length(replace(rcplist,',',''))+1)

             )

  loop

   --DBMS_OUTPUT.PUT_LINE (rec.mvalue);

     l_email_add.extend;

     l_email_add(l_email_add.last) := a_email_address(NULL);

     --

     l_email_add(xx).email_add := rec.mvalue;

     xx:=xx+1;

   end loop;

  --

  Return l_email_add;

End;

/

To test above code

SELECT * FROM TABLE(get_email_add(‘afzaal@mcbah.com,shabbir@mcbah.com,Kashif@mcbah.com’)

/