I am trying to send a mail with an attachment through oracle. But I am getting
Oracle error - ORA-29279: SMTP permanent error: 554 Transaction failed: Expected ';', got "/"message
in my code at below mentioned step.
L_STEP := 32;
UTL_SMTP.CLOSE_DATA(L_CONNECTION);
I am not able to understand where I am making a mistake.
Calling procedure like -
USP_SEND_MAIL(P_TO => '[email protected]',
P_CC => '[email protected]',
P_BCC => NULL,
P_FROM =>'[email protected]',
P_SUBJECT => 'Test mail',
P_FILENAME1 => 'Test_File-27 APR 2022 - 12 PM.xls',
P_HTML => '<BR> Dear Sir/Madam <BR>
<BR> Please find attached file.<BR>
<BR> Total count-66<BR>
<BR>Regards <BR> System generated Mailer',
P_ERR_FLAG => L_ERR_FLAG);
Below is my code
PROCEDURE USP_SEND_MAIL (P_TO IN VARCHAR2,
P_CC IN VARCHAR2,
P_BCC IN VARCHAR2,
P_FROM IN VARCHAR2,
P_SUBJECT IN VARCHAR2,
P_FILENAME1 VARCHAR2,
P_HTML LONG,
P_ERR_FLAG OUT NUMBER) IS
L_CONNECTION UTL_SMTP.CONNECTION;
P_SMTP_HOSTNAME VARCHAR2(100);
P_SMTP_PORTNUM VARCHAR2(100);
L_STEP INTEGER := 0;
L_TO VARCHAR2(4000);
L_CC VARCHAR2(4000);
L_BCC VARCHAR2(4000);
CURSOR C1 IS
SELECT 0,
'CERTIFICATE' EBIZ_DATA
FROM DUAL
UNION ALL
SELECT *
FROM (SELECT 1, t.CERT_NO
FROM CERTIFICATE t
ORDER BY 1)
ORDER BY 1;
BEGIN
L_STEP := 1;
-----***** STAGING MAILER CONFIG ****--------
P_SMTP_HOSTNAME := 'email-*********.amazonaws.com';
P_SMTP_PORTNUM := '000';
L_CONNECTION := UTL_SMTP.open_connection(P_SMTP_HOSTNAME,
P_SMTP_PORTNUM,
wallet_path => '*****/oracle19c/owm/wallets',
wallet_password => '********',
secure_connection_before_smtp => FALSE);
L_STEP := 2;
-----***** STAGING MAILER CONFIG ****--------
utl_smtp.helo(L_CONNECTION, P_SMTP_HOSTNAME);
--utl_smtp.command(L_CONNECTION, 'STARTTLS');
utl_smtp.starttls(L_CONNECTION);
utl_smtp.command(L_CONNECTION, 'AUTH LOGIN');
utl_smtp.command(L_CONNECTION,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('********'))));
utl_smtp.command(L_CONNECTION,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('********'))));
L_STEP := 3;
UTL_SMTP.HELO(L_CONNECTION, P_SMTP_HOSTNAME);
UTL_SMTP.MAIL(L_CONNECTION, P_FROM);
FOR x IN (SELECT LEVEL AS id,
REGEXP_SUBSTR(P_TO, '[^;]+', 1, LEVEL) AS TO_EMAIL_NAME
FROM DUAL
CONNECT BY REGEXP_SUBSTR(P_TO, '[^;]+', 1, LEVEL) IS NOT NULL) LOOP
utl_smtp.Rcpt(L_CONNECTION, x.TO_EMAIL_NAME);
END LOOP;
IF P_CC IS NOT NULL THEN
FOR x IN (SELECT LEVEL AS id,
REGEXP_SUBSTR(P_CC, '[^;]+', 1, LEVEL) AS CC_EMAIL_NAME
FROM DUAL
CONNECT BY REGEXP_SUBSTR(P_CC, '[^;]+', 1, LEVEL) IS NOT NULL) LOOP
utl_smtp.Rcpt(L_CONNECTION, x.CC_EMAIL_NAME);
L_CC := L_CC || 'CC: ' || x.CC_EMAIL_NAME || CRLF;
END LOOP;
ELSE
L_CC := '';
END IF;
IF P_BCC IS NOT NULL THEN
FOR x IN (SELECT LEVEL AS id,
REGEXP_SUBSTR(P_BCC, '[^;]+', 1, LEVEL) AS BCC_EMAIL_NAME
FROM DUAL
CONNECT BY REGEXP_SUBSTR(P_BCC, '[^;]+', 1, LEVEL) IS NOT NULL) LOOP
utl_smtp.Rcpt(L_CONNECTION, x.BCC_EMAIL_NAME);
L_BCC := L_BCC || 'BCC: ' || x.BCC_EMAIL_NAME || CRLF;
END LOOP;
ELSE
L_BCC := '';
END IF;
L_STEP := 8;
UTL_SMTP.OPEN_DATA(L_CONNECTION);
L_STEP := 9;
-- Use MIME mail header
UTL_SMTP.WRITE_DATA(L_CONNECTION, 'MIME-Version: 1.0' || UTL_TCP.crlf);
L_STEP := 10;
UTL_SMTP.WRITE_DATA(L_CONNECTION,
'Date: ' ||
TO_CHAR(SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss') || UTL_TCP.crlf);
L_STEP := 11;
UTL_SMTP.WRITE_DATA(L_CONNECTION, 'TO: ' || P_TO || UTL_TCP.crlf);
IF L_CC IS NOT NULL THEN
L_STEP := 12;
--UTL_SMTP.WRITE_DATA(L_CONNECTION, 'Cc: ' || P_CC || UTL_TCP.crlf);
UTL_SMTP.WRITE_DATA(L_CONNECTION, L_CC);
END IF;
IF L_BCC IS NOT NULL THEN
L_STEP := 13;
--UTL_SMTP.WRITE_DATA(L_CONNECTION, 'Bcc: ' || P_BCC || UTL_TCP.crlf);
UTL_SMTP.WRITE_DATA(L_CONNECTION, L_BCC);
END IF;
L_STEP := 14;
UTL_SMTP.WRITE_DATA(L_CONNECTION, 'From: ' || P_FROM || UTL_TCP.crlf);
L_STEP := 15;
UTL_SMTP.WRITE_DATA(L_CONNECTION, 'Subject: ' || P_SUBJECT || UTL_TCP.crlf);
L_STEP := 16;
UTL_SMTP.WRITE_DATA(L_CONNECTION, 'Reply-To: ' || P_FROM || UTL_TCP.crlf);
L_STEP := 17;
UTL_SMTP.WRITE_DATA(L_CONNECTION,
'Content-Type: multipart/mixed; boundary="EBIZ_BOUNDARY"' || UTL_TCP.crlf || UTL_TCP.crlf);
L_STEP := 18;
UTL_SMTP.WRITE_DATA(L_CONNECTION, 'This is multipart message' || UTL_TCP.crlf);
L_STEP := 19;
UTL_SMTP.WRITE_DATA(L_CONNECTION, '--EBIZ_BOUNDARY' || UTL_TCP.crlf);
L_STEP := 20;
-- Message body
UTL_SMTP.WRITE_DATA(L_CONNECTION, 'Content-Type: text/html;' || UTL_TCP.crlf);
L_STEP := 21;
UTL_SMTP.WRITE_DATA(L_CONNECTION, 'Content-Transfer_Encoding: 7bit' || UTL_TCP.crlf || UTL_TCP.crlf);
L_STEP := 22;
UTL_SMTP.WRITE_DATA(L_CONNECTION, P_HTML || UTL_TCP.crlf);
L_STEP := 23;
UTL_SMTP.WRITE_DATA(L_CONNECTION, ' ' || UTL_TCP.crlf || UTL_TCP.crlf || UTL_TCP.crlf || UTL_TCP.crlf || UTL_TCP.crlf);
L_STEP := 24;
-- Attachment section 1
UTL_SMTP.WRITE_DATA(L_CONNECTION, '--EBIZ_BOUNDARY' || UTL_TCP.CRLF);
L_STEP := 25;
--UTL_SMTP.WRITE_DATA(L_CONNECTION, 'CONTENT-TYPE: TEXT/PLAIN;' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(L_CONNECTION,
'CONTENT-TYPE: APPLICATION/APPLICATION/VND.OPENXMLFORMATS-OFFICEDOCUMENT.SPREADSHEETML.SHEET;' || UTL_TCP.CRLF);
L_STEP := 26;
UTL_SMTP.WRITE_DATA(L_CONNECTION,
' NAME="' || P_FILENAME1 || '"' || UTL_TCP.CRLF);
L_STEP := 27;
UTL_SMTP.WRITE_DATA(L_CONNECTION,
'CONTENT-TRANSFER_ENCODING: 8BIT' || UTL_TCP.CRLF);
L_STEP := 28;
UTL_SMTP.WRITE_DATA(L_CONNECTION,
'CONTENT-DISPOSITION: ATTACHMENT;' || UTL_TCP.CRLF);
L_STEP := 29;
UTL_SMTP.WRITE_DATA(L_CONNECTION,
' FILENAME="' || P_FILENAME1 || '"' || UTL_TCP.CRLF || UTL_TCP.CRLF);
L_STEP := 30;
FOR X IN C1 LOOP
UTL_SMTP.WRITE_DATA(L_CONNECTION, X.EBIZ_DATA || UTL_TCP.CRLF);
END LOOP;
-- End MIME mail
L_STEP := 31;
UTL_SMTP.WRITE_DATA(L_CONNECTION, UTL_TCP.crlf || UTL_TCP.crlf || '--EBIZ_BOUNDARY--');
L_STEP := 32; **GETTING AN ERROR HERE**
UTL_SMTP.CLOSE_DATA(L_CONNECTION);
L_STEP := 33;
UTL_SMTP.QUIT(L_CONNECTION);
P_ERR_FLAG := 0;
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
P_ERR_FLAG := 1;
IL_ERROR_LOGGING.ERROR(MESSAGE => SQLERRM || 'message',
MODULE => 'RETAIL UNSYNC POLICY MAILER',
V_PARAMATER => (PARAMS(' L_STEP =>' || L_STEP)));
UTL_SMTP.QUIT(L_CONNECTION);
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
P_ERR_FLAG := 1;
IL_ERROR_LOGGING.ERROR(MESSAGE => SQLERRM || 'message',
MODULE => 'RETAIL UNSYNC POLICY MAILER',
V_PARAMATER => (PARAMS(' L_STEP =>' || L_STEP)));
UTL_SMTP.QUIT(L_CONNECTION);
WHEN UTL_SMTP.PERMANENT_ERROR THEN
P_ERR_FLAG := 1;
IL_ERROR_LOGGING.ERROR(MESSAGE => SQLERRM || 'message',
MODULE => 'RETAIL UNSYNC POLICY MAILER',
V_PARAMATER => (PARAMS(' L_STEP =>' || L_STEP)));
UTL_SMTP.QUIT(L_CONNECTION);
WHEN OTHERS THEN
P_ERR_FLAG := 99;
IL_ERROR_LOGGING.ERROR(MESSAGE => SQLERRM || 'message',
MODULE => 'RETAIL UNSYNC POLICY MAILER',
V_PARAMATER => (PARAMS(' L_STEP =>' || L_STEP)));
UTL_SMTP.QUIT(L_CONNECTION);
END;
Copyright Notice：Content Author:「Rahul More」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/72025003/unable-to-send-mail-with-an-attachment-through-oracle-using-utl-smtp