CODEWITHSUNDEEP
oracle-databaseemailsmtp
kumarb
kumarb

Reputation: 535

Sending multiple emails using UTL_SMTP in oracle

I am trying to write a procedure which send emails from Oracle DB using UTL_SMTP functions. Its working fine if I am only sending one email address but if I am passing multiple emails as comma or semicollon(;), its failling with error like

ORA-29277: invalid SMTP operation
ORA-29279: SMTP permanent error: 501 5.1.3 Invalid address

I am writing the statement like :- where P_ALERT_DESTINATION is a variable which is having multiple emails id.

UTL_SMTP.WRITE_DATA(L_MAIL_CONN, 'To: ' || P_ALERT_DESTINATION || UTL_TCP.CRLF);

Upvotes: 0

Views: 7400

Answers (1)

Jokke Heikkilä
Jokke Heikkilä

Reputation: 928

I think you need to loop all email addresses and make that procedure call to each one. Pseudo code:

for all_addresses loop
  UTL_SMTP.WRITE_DATA(L_MAIL_CONN, 'To: ' || one_address|| UTL_TCP.CRLF)
end loop;

Edited: Here you go, this should work:

declare
  cursor split_cursor(p_to in varchar2) is
  select regexp_substr(p_to, '[^,]+', 1, level) email_address
    from dual
  connect by regexp_substr(p_to, '[^,]+', 1, level) is not null;
begin

  for i in split_cursor(P_ALERT_DESTINATION) loop
    UTL_SMTP.WRITE_DATA(L_MAIL_CONN, 'To: ' || i.email_address|| UTL_TCP.CRLF);
  end loop;

end;

You should also remove all spaces if the variable contains those.

Upvotes: 0

Related Questions