Hi Guys,
I'm having issues with configuring Apex_Mail and the wallet to use Google's smtp-relay service. Note: there is a related archived post along similar lines from last year
I'm running Apex 5.1 on Oracle db 12.2 in the current Chrome version 61.0.3163.100.
When running the following procedure:
ALTER SESSION SET CURRENT_SCHEMA = APEX_050100;
DECLARE
l_con utl_smtp.Connection;
BEGIN
l_con := utl_smtp.Open_connection (
host => 'smtp-relay.gmail.com'
, port => 587
, wallet_path => '<<file:/u01/app...../>>'
, wallet_password => '<<my_password>>'
, secure_connection_before_smtp => false ) ;
utl_smtp.helo(l_con, 'smtp-relay.gmail.com');
utl_smtp.starttls(l_con);
utl_smtp.auth(l_con,
'<<mydomain.com>>',
'<<my password>>',
'PLAIN');
UTL_SMTP.mail(l_con, '<<my_from_email>>');
UTL_SMTP.rcpt(l_con, '<<my_to_email>>');
UTL_SMTP.data(l_con, 'Hello world!' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.close_data(l_con);
UTL_SMTP.quit(l_con);
END;
It returns...
Error report -
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_TCP", line 63
ORA-06512: at "SYS.UTL_TCP", line 303
ORA-06512: at "SYS.UTL_SMTP", line 290
ORA-06512: at "SYS.UTL_SMTP", line 296
ORA-06512: at line 11
29024. 00000 - "Certificate validation failure"
*Cause: The certificate sent by the other side could not be validated. This may occur if
the certificate has expired, has been revoked, or is invalid for another reason.
*Action: Check the certificate to determine whether it is valid. Obtain a new certificate,
alert the sender that the certificate has failed, o
Interestingly, if I change the "secure_connection_before_smtp => false " parameter to "true", the error changes to
Error report -
ORA-29019: The protocol version is incorrect.
ORA-06512: at "SYS.UTL_TCP", line 63
ORA-06512: at "SYS.UTL_TCP", line 303
ORA-06512: at "SYS.UTL_SMTP", line 177
ORA-06512: at "SYS.UTL_SMTP", line 201
ORA-06512: at line 4
29019. 00000 - "The protocol version is incorrect."
*Cause: There is a protocol version mismatch between the two peers.
*Action: Specify the correct protocol version in the product's configuration
file.
I have tried the current certs for both "smtp-relay.gmail.com" and "smtp.gmail.com" in the Wallet. (I used OpenSSL to query these and when viewed in the Wallet GUI they appear as expected)
I have added the WALLET_LOCATION and "SSL_Certificate= 3.0 or 2.0" to the SQLNet.ora file as the recommended fix for the second ORA-29019
I have triple check my ACL privs to ensure APEX_050100 has connect, resolve, http, http_proxy and smtp privs.
I have also configured the gmail smtp service, testing both options to require TLS and not require TLS.
So far...no banana....
Thanks in advance for your consideration....