troubleshooting Question

Can you please help me with a issue with sending a 'array' in 'To' parameter in SMTP_PKG?

Avatar of Rao_S
Rao_S asked on
Oracle Database
9 Comments1 Solution625 ViewsLast Modified:
hi,
i have a SMTP_PKG to send HTML email with more than 32K size.
i have attached the package, and here is the code where i need to send the email to multiple recipients where i have to use a variable because i have to select the list of email id's from a table. but does not work, i get an error..
i have changed the email ids, for testing you can create the package with correct hostname and correct email address in the pl/sql block...

declare
    lv_smtp     SMTP_PKG.SMTP_EMAIL_TYP;
    str1        varchar2 (200) := 'aaaa.zzzz@gmail.com;ssss.xxxx@gmail.com;dddd.cccc@gmail.com';
    str2        varchar2( 200 );
    ppid        varchar2( 200 );
    --lv_to   array default array();
begin

    dbms_output.put_line(str1);
   
    for i in ( select regexp_substr( str1, '[^;]+', 1, level) ppid
               from dual
               connect by regexp_substr (str1, '[^;]+', 1,  level) is not null
             )
    loop
        dbms_output.put_line(i.ppid);
        str2 := str2||''''||i.ppid||''', ';
    end loop;
   
    dbms_output.put_line(str2);
   
    lv_smtp.sender_email := 'ffff.vvvv@gmail.com';
    lv_smtp.sender       := 'SMTP Test <ffff.vvvv@gmail.com>';
    lv_smtp.recipients   :=  SMTP_PKG.ARRAY ( str2 );
    lv_smtp.cc           :=  SMTP_PKG.ARRAY ();
    lv_smtp.bcc          :=  SMTP_PKG.ARRAY ();
    lv_smtp.subject      := 'This is from SMTP_PKG';
    lv_smtp.message      := 'Hello, this is the mail  using a variable';
   
    SMTP_PKG.SEND   ( lv_smtp );
   
exception when others then
    dbms_output.put_line ('Error: ' ||sqlcode||sqlerrm);
end;
/
SMTP-PKG.txt
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros