Hi Tom,
Very Good Morning !
First let me make sure, here no sample table data is needed .
Usually for all of the scheduled procedures, we come with some kind of mail procedures, where in we get either success/Failure mail once the job execution is over.
we generally use the SMTP server system ip as part of the sending mail procedure...something like this in our code :
emailserver VARCHAR2 (30) :='192.168.15.115';
The next step would be as below and so on:
l_mail_conn := UTL_SMTP.open_connection (emailserver, port);
UTL_SMTP.helo (l_mail_conn, emailserver);
we were able to send the mails successfully to our company mail id, but there is a scenario where in we need to send the job mail to our customer also.But when we add our customer email id, we could not send the mail with the above configuration settings(smtp server ip :192.168.15.115).
so we thought of replacing the SMTP SERVER IP(192.168.15.115) with the below code snippet:
emailserver VARCHAR2 (30) := 'smtp.gmail.com';
port NUMBER := 25;
l_mail_conn := UTL_SMTP.open_connection (emailserver, port);
UTL_SMTP.helo (l_mail_conn, emailserver);
we are getting the below error message :
Error starting at line 1 in command:
execute PRC_RECON_INVLOC_VARIANCE_OFF
Error report:
ORA-29279: SMTP permanent error: 502 Command not implemented
ORA-06512: at "SYS.UTL_SMTP", line 54
ORA-06512: at "SYS.UTL_SMTP", line 140
ORA-06512: at "SYS.UTL_SMTP", line 289
ORA-06512: at "QCHKSLOC_JAN1919.PRC_RECON_INVLOC_VARIANCE_OFF", line 48
ORA-06512: at line 1
29279. 00000 - "SMTP permanent error: %s"
*Cause: A SMTP permanent error occurred.
*Action: Correct the error and retry the SMTP operation.
Any suggestion for fixing the above issue would be appreciated.
Please do let us know if anything a system admin has to do.
Below is our complete set of code that we have been using:
CREATE OR REPLACE PROCEDURE prc_recon_invloc_variance_off
AS
mailcontent VARCHAR2 (500);
sendoraddress VARCHAR2 (30) := 'GL_RECON@xyz.net';
v_recipient1 VARCHAR2 (30) := 'praveenkumar.k@xyz.net';
v_recipient3 VARCHAR2 (30) := 'muneer@xyz.net';
-- emailserver VARCHAR2 (30) :='smtp.gmail.com';--'192.168.15.115';
emailserver VARCHAR2 (30) := 'smtp.gmail.com';
port NUMBER := 587;
l_mail_conn UTL_SMTP.connection;
c_mail_boundary CONSTANT VARCHAR2 (255) DEFAULT '10000000000000';
crlf VARCHAR2 (2) := CHR (13) || CHR (10);
v_process_name VARCHAR2 (100):= 'PRC_RECON_INVLOC_VARIANCE_OFF';
v_sqlerrorcd VARCHAR2 (10);
v_sqlerrormsg VARCHAR2 (255);
v_cnt NUMBER;
file_name VARCHAR2 (50);
v_user_name VARCHAR2 (20);
p_username VARCHAR2 (100) := 'gl_recon@xyz.net';
p_password VARCHAR2 (20) := 'abc@123';
------------------------------------------------------
BEGIN
SELECT SYS_CONTEXT ('USERENV', 'current_schema')
INTO v_user_name
FROM DUAL;
l_mail_conn := UTL_SMTP.open_connection (emailserver, port);
UTL_SMTP.helo (l_mail_conn, emailserver);
UTL_SMTP.starttls (l_mail_conn);
--------------BEGIN AUTHENTICATION--------------
UTL_SMTP.command (l_mail_conn, 'AUTH LOGIN');
/* UTL_SMTP.command (l_mail_conn,
UTL_ENCODE.text_encode (p_username,
'WE8ISO8859P1',
UTL_ENCODE.base64
)
);
UTL_SMTP.command (l_mail_conn,
UTL_ENCODE.text_encode (p_password,
'WE8ISO8859P1',
UTL_ENCODE.base64
)
);*/
UTL_SMTP.command (l_mail_conn,
UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_username)
)
);
UTL_SMTP.command (l_mail_conn,
UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_password)
)
);
--------------END AUTHENTICATION--------------
UTL_SMTP.mail (l_mail_conn, sendoraddress);
UTL_SMTP.rcpt (l_mail_conn, v_recipient1);
UTL_SMTP.rcpt (l_mail_conn, v_recipient3);
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: ' || v_recipient1 || UTL_TCP.crlf);
UTL_SMTP.write_data (l_mail_conn, 'Bcc: ' || v_recipient3 || UTL_TCP.crlf);
UTL_SMTP.write_data (l_mail_conn,
'From: ' || sendoraddress || UTL_TCP.crlf);
UTL_SMTP.write_data
(l_mail_conn,
'Subject: Success status For Daily Recon Inventory LOC Report On'
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
|| UTL_TCP.crlf
);
UTL_SMTP.write_data (l_mail_conn,
'Reply-To: ' || sendoraddress || 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="'
|| c_mail_boundary
|| '"'
|| UTL_TCP.crlf
|| UTL_TCP.crlf
);
UTL_SMTP.write_data (l_mail_conn, '--' || c_mail_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data (l_mail_conn,
'Content-Type: text/html'
|| UTL_TCP.crlf
|| UTL_TCP.crlf
);
UTL_SMTP.write_data (l_mail_conn,
'<html><body>' || UTL_TCP.crlf || UTL_TCP.crlf
);
UTL_SMTP.write_data (l_mail_conn,
'Hi,' || UTL_TCP.crlf || ' ' || CHR (13) || '' || crlf
);
UTL_SMTP.write_data (l_mail_conn, '<br/><br/>' || crlf);
UTL_SMTP.write_data
(l_mail_conn,
' Today No Variance For Daily Recon Inventory LOC Report On '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
|| ' @ '
|| v_user_name
|| ' '
|| 'DB.'
);
UTL_SMTP.write_data
(l_mail_conn,
'<br/><br/><br/>Thanks and Regards ,
<BR> DB Development.
</BODY>
</html>'
);
UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.close_data (l_mail_conn);
UTL_SMTP.quit (l_mail_conn);
END;Regards,
Vinesh