Dear Ask TOM Team,
We have upgrade our DEV Environment to the Oracle 12.2 Release and we are about to finish our upgrade tests. Now one test that we thought should be a simple one is the SEND Mail over the UTL_SMTP package.
In the release 12.1 we already use the WALLET feature and this was working fine. Now with 12.2 it seems that a new parameter is out there (secure_host). Which is not in the documentation of Oracle yet: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/UTL_SMTP.html#GUID-D6826448-1407-4476-AFB8-D1EB14E38F67
I thought this can not be so difficult so I tried to test it on myself. Now after three days of try and error. I've no idea what do.
We use office365 from Microsoft for our Mailserver. May you can help me what to do, so that this test code would send mails again like on the release 12.1:
We really tried a lot of values for the parameter "secure_host" but we get always the ORA-24263 Error.
Here is our test code:
l_mailhost VARCHAR2 (64) := 'smtp.office365.com';
l_from VARCHAR2 (64) := 'firstname.lastname@example.org';
l_to VARCHAR2 (64) := 'email@example.com';
l_subject VARCHAR2 (64) := 'Test message from Oracle DB 12.2';
crlf varchar2(2) := UTL_TCP.CRLF;
c := utl_smtp.open_connection (
host => l_mailhost,
port => 587,
wallet_path => 'file:C:\oracle\app\wallets\',
wallet_password => 'walletpw',
secure_connection_before_smtp => FALSE
,secure_host => 'outlook.com'
utl_smtp.helo(c, l_mailhost) ;
utl_smtp.command( c, 'AUTH LOGIN');
utl_smtp.command( c, 'bm9fcmVwbHlAaGVwYzb2Z0LmNvbQ==');
utl_smtp.command( c, 'SHVmdTxLSE=');
UTL_SMTP.mail (c, l_from);
UTL_SMTP.rcpt (c, l_to);
UTL_SMTP.write_data (c, 'Date: ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || crlf);
UTL_SMTP.write_data (c, 'From: ' || l_from || crlf);
UTL_SMTP.write_data (c, 'Subject: ' || l_subject || crlf);
UTL_SMTP.write_data (c, 'To: ' || l_to || crlf);
UTL_SMTP.write_data (c, 'asdf' || crlf);
EXCEPTION WHEN OTHERS THEN
I'll raise a doc bug that the parameter has not been documented.
The description for that parameter is:
* secure_host the host name to be matched against the common name (CN) of
* the SMTP server's certificate when a secure connection is
* used. It can also be a domain mame like "*.example.com".
* If NULL, the SMTP host name to connect to will be used.
with additional details being
When making a secured connection or request, UTL_TCP, UTL_SMTP and UTL_HTTP now verify the digital certificate of the remote server and will raise the following error if the server address in the common name (CN) attribute of the certificate does not match the requested connection or URL.
ORA-24263: Certificate of the remote server does not match the target address.
In case when the address in the certificate is expected to be different (for example, when accessing the server by IP address), the caller can provide the expected address or domain name to match via an additional parameter when making the connection or request.
But ultimately, I suspect you need to find the precise CN name for outlook.com
Hope this helps.