Hi gurus
Please help to resolve this issue, every thing was going fine but from last two days I' m receiving this error while executing this
exec send_email('xyz@abc.com','jhon.gmail.com',NULL,NULL,'First msg','First msg','First msg');
send_email code is as
CREATE OR REPLACE PROCEDURE IMAGE.send_email (
p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_cc IN VARCHAR2,
p_bcc IN VARCHAR2,
p_subject IN VARCHAR2,
p_text IN LONG DEFAULT NULL,
p_html IN LONG DEFAULT NULL)
IS
p_smtp_hostname VARCHAR2 (225) := 'smtp.abc.com';
p_smtp_portnum VARCHAR2 (5) := '25';
p_username VARCHAR2 (30) := 'test@abc.com';
p_password VARCHAR2 (20) := 'B34adf';
l_boundary VARCHAR2 (255) DEFAULT 'a1b2c3d4e3f2g1';
l_connection UTL_SMTP.connection;
l_body_html CLOB := EMPTY_CLOB; --This LOB will be the email message
l_offset NUMBER;
l_ammount NUMBER;
l_temp LONG DEFAULT NULL;
l_to_recipients LONG;
BEGIN
l_connection := UTL_SMTP.open_connection (p_smtp_hostname, p_smtp_portnum);
UTL_SMTP.helo (l_connection, p_smtp_hostname);
--------------BEGIN AUTHENTICATION--------------
UTL_SMTP.command (l_connection, 'AUTH LOGIN');
UTL_SMTP.command (l_connection,
DEMO_BASE64.ENCODE (UTL_RAW.cast_to_raw (p_username)));
UTL_SMTP.command (l_connection,
DEMO_BASE64.ENCODE (UTL_RAW.cast_to_raw (p_password)));
--------------END AUTHENTICATION--------------
UTL_SMTP.mail (l_connection, '<' || p_from || '>');
-- multiple To recipients starts
FOR x IN ( SELECT REGEXP_SUBSTR (p_to, '[^,]+', 1, LEVEL) address FROM DUAL CONNECT BY REGEXP_SUBSTR (p_to, '[^,]+', 1, LEVEL) IS NOT NULL)
LOOP
UTL_SMTP.rcpt (l_connection, '<' || x.address || '>');
IF (l_to_recipients IS NULL)
THEN
l_to_recipients := 'To: ' || x.address;
ELSE
l_to_recipients := l_to_recipients || ', ' || x.address;
END IF;
END LOOP;
-- multiple To recipients ends
IF p_cc IS NOT NULL
THEN
UTL_SMTP.rcpt (l_connection, '<' || p_cc || '>');
END IF;
IF p_bcc IS NOT NULL
THEN
UTL_SMTP.rcpt (l_connection, '<' || p_bcc || '>');
END IF;
l_temp := l_temp || 'MIME-Version: 1.0' || CHR (13) || CHR (10);
l_temp := l_temp || l_to_recipients || CHR (13) || CHR (10);
l_temp := l_temp || 'CC: ' || p_cc || CHR (13) || CHR (10);
l_temp := l_temp || 'Bcc: ' || p_bcc || CHR (13) || CHR (10);
l_temp := l_temp || 'From: ' || p_from || CHR (13) || CHR (10);
l_temp := l_temp || 'Subject: ' || p_subject || CHR (13) || CHR (10);
l_temp := l_temp || 'Reply-To: ' || p_from || CHR (13) || CHR (10);
l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' || CHR (34) || l_boundary || CHR (34) || CHR (13) || CHR (10);
----------------------------------------------------
-- Write the headers
DBMS_LOB.createtemporary (l_body_html, FALSE, 10);
DBMS_LOB.write (l_body_html, LENGTH (l_temp), 1, l_temp);
----------------------------------------------------
-- Write the text boundary
l_offset := DBMS_LOB.getlength (l_body_html) + 1;
l_temp := '--' || l_boundary || CHR (13) || CHR (10);
l_temp := l_temp || 'content-type: text/plain; charset=us-ascii' || CHR (13) || CHR (10) || CHR (13) || CHR (10);
DBMS_LOB.write (l_body_html, LENGTH (l_temp), l_offset, l_temp);
----------------------------------------------------
-- Write the plain text portion of the email
l_offset := DBMS_LOB.getlength (l_body_html) + 1;
DBMS_LOB.write (l_body_html, LENGTH (p_text), l_offset, p_text);
----------------------------------------------------
-- Write the HTML boundary
l_temp := CHR (13) || CHR (10) || CHR (13) || CHR (10) || '--' || l_boundary || CHR (13) || CHR (10);
l_temp := l_temp || 'content-type: text/html;' || CHR (13) || CHR (10) || CHR (13) || CHR (10);
l_offset := DBMS_LOB.getlength (l_body_html) + 1;
DBMS_LOB.write (l_body_html, LENGTH (l_temp), l_offset, l_temp);
----------------------------------------------------
-- Write the HTML portion of the message
l_offset := DBMS_LOB.getlength (l_body_html) + 1;
DBMS_LOB.write (l_body_html, LENGTH (p_html), l_offset, p_html);
----------------------------------------------------
-- Write the final html boundary
l_temp := CHR (13) || CHR (10) || '--' || l_boundary || '--' || CHR (13);
l_offset := DBMS_LOB.getlength (l_body_html) + 1;
DBMS_LOB.write (l_body_html, LENGTH (l_temp), l_offset, l_temp);
----------------------------------------------------
-- Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;
UTL_SMTP.open_data (l_connection);
WHILE l_offset < DBMS_LOB.getlength (l_body_html)
LOOP
UTL_SMTP.write_data (
l_connection,
DBMS_LOB.SUBSTR (l_body_html, l_ammount, l_offset));
l_offset := l_offset + l_ammount;
l_ammount := LEAST (1900, DBMS_LOB.getlength (l_body_html) - l_ammount);
END LOOP;
UTL_SMTP.close_data (l_connection);
UTL_SMTP.quit (l_connection);
DBMS_LOB.freetemporary (l_body_html);
END;