SQL & PL/SQL

Hi All,

I have the below piece of code which sends email with attachment from pl/sql. This code only can send single attachment at a time, I am trying to extend this to be able to send multiple attachments at a time and which accepts clob & blob attachments.

DECLARE

cursor c_get_det

is

select null key_file_name, null key_file

from dual

where 1=2

union

SELECT key_file_name, to_char(key_file)

 FROM xxc\_som\_orders

WHERE order\_header\_id = 1000197;

p_smtp_host VARCHAR2 (240) := 'oraclemail.xyz.com';

p_from VARCHAR2 (240) := 'xyz@gmail.com';

p_to VARCHAR2 (240) := 'xyz@gmail.com';

p_subject VARCHAR2 (2000) := 'Order Details';

p_text_msg VARCHAR2 (2000) := 'Please find the order details';

p_attach_name VARCHAR2 (240);

p_attach_mime VARCHAR2 (30) := 'text/plain';

p_attach_clob CLOB;

BEGIN

open c_get_det;

fetch c_get_det INTO p_attach_name, p_attach_clob;

close c_get_det;

l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);

UTL_SMTP.helo(l_mail_conn, p_smtp_host);

UTL_SMTP.mail(l_mail_conn, p_from);

UTL_SMTP.rcpt(l_mail_conn, p_to);

UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

IF p_text_msg IS NOT NULL THEN

UTL\_SMTP.write\_data(l\_mail\_conn, '--' || l\_boundary || UTL\_TCP.crlf);

UTL\_SMTP.write\_data(l\_mail\_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL\_TCP.crlf || UTL\_TCP.crlf);

UTL\_SMTP.write\_data(l\_mail\_conn, p\_text\_msg);

UTL\_SMTP.write\_data(l\_mail\_conn, UTL\_TCP.crlf || UTL\_TCP.crlf);

END IF;

IF p_attach_name IS NOT NULL THEN

UTL\_SMTP.write\_data(l\_mail\_conn, '--' || l\_boundary || UTL\_TCP.crlf);

UTL\_SMTP.write\_data(l\_mail\_conn, 'Content-Type: ' || p\_attach\_mime || '; name="' || p\_attach\_name || '"' || UTL\_TCP.crlf);

UTL\_SMTP.write\_data(l\_mail\_conn, 'Content-Disposition: attachment; filename="' || p\_attach\_name || '"' || UTL\_TCP.crlf || UTL\_TCP.crlf);

FOR i IN 0 .. TRUNC((DBMS\_LOB.getlength(p\_attach\_clob) - 1 )/l\_step) LOOP

  UTL\_SMTP.write\_data(l\_mail\_conn, DBMS\_LOB.substr(p\_attach\_clob, l\_step, i \* l\_step + 1));

END LOOP;

UTL\_SMTP.write\_data(l\_mail\_conn, UTL\_TCP.crlf || UTL\_TCP.crlf);

END IF;

UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);

UTL_SMTP.close_data(l_mail_conn);

--dbms_output.put_line('l_mail_conn'||l_mail_conn);

UTL_SMTP.quit(l_mail_conn);

END;

Any suggestions would be appreciated.

Thanks!