Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Accessing SMTP.GMAIL.COM from Apex

PericlesMay 24 2022

Hello
I'm trying to setup the outgoing mailing for Apex 22.1
As I read, there a could of steps to accomplish to get this done:
1.- Get the certificates in DER format.
DONE: Got the google.com smtp.google.com and smtp-relay.google.com
2.- Create a directory where to create the wallet
DONE: /opt/oracle/homes/OraDBHome21cXE/admin/XE/apex_wallet
3.- Create the wallet with orapki wallet create -wallet /opt/oracle/homes/OraDBHome21cXE/admin/XE/apex_wallet -pwd <SECURE_PASSWORD> -auto_login
DONE
4.- Add the certificates to the wallet with orapki wallet add -wallet /opt/oracle/homes/OraDBHome21cXE/admin/XE/apex_wallet -trusted_cert -cert "gmail.cer" -pwd <SECURE_PASSWORD>
DONE
5.- Modify the ACL to allow connections to smtp.google.com
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'smtp.gmail.com',
lower_port => 587,
upper_port => 587,
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => 'APEX_220100',
principal_type => xs_acl.ptype_db));

dbms_network_acl_admin.append_wallet_ace(
wallet_path => 'file:/opt/oracle/homes/OraDBHome21cXE/admin/XE/apex_wallet',
ace => xs$ace_type(privilege_list => xs$name_list('use_client_certificates', 'use_passwords'),
principal_name => 'APEX_220100',
principal_type => xs_acl.ptype_db));
DONE
After all this I'm still having problems with the SMTP connection.
Checked the ACL with SELECT * FROM DBA_NETWORK_ACLS and it seems to be ok
image.pngI do verify the wallet with "orapki wallet display -wallet /opt/oracle/homes/OraDBHome21cXE/admin/XE/apex_wallet" and it seems to be ok
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: CN=smtp-relay.gmail.com
Subject: CN=smtp.gmail.com
Subject: CN=*.google.com
Also configured a mail client and checked that the account is open to external connection.

Any suggestion?
This post has been answered by Pericles on May 26 2022
Jump to Answer

Comments

Pericles

Hello
After using OWM to generate a new wallet, and modify the instance parameter 'Use tls" to "After the connection is established" finally I have some improvement.
Now, if I try to send an email to smtp.gmail.com through port 587 I receive this error on the Mail Queue
Error sending mail.
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Checking the DBA_SCHEDULER_JOB_RUN_DETAILS view I'm getting the following error:
Additional info:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SYS.UTL_SMTP", line 110
ORA-06512: at "SYS.UTL_SMTP", line 235
ORA-06512: at "SYS.UTL_SMTP", line 215
ORA-06512: at "SYS.UTL_SMTP", line 630
ORA-06512: at "SYS.UTL_SMTP", line 641
ORA-06512: at "APEX_220100.WWV_FLOW_MAIL", line 163
ORA-06512: at "APEX_220100.WWV_FLOW_MAIL", line 1894
ORA-06512: at line 1
ERROR:
# APEX|WRN|Exception in "": %MAIL.push_queue_immediate:1881<-:1
# APEX| |Error Stack: ORA-29024: Certificate validation failure
# APEX| |ORA-06512: at "SYS.UTL_TCP", line 63
# APEX| |ORA-06512: at "SYS.UTL_TCP", line 314
# APEX| |ORA-06512: at "SYS.UTL_SMTP", line 290
# APEX| |ORA-06512: at "SYS.UTL_SMTP", line 296
# APEX| |ORA-06512: at "APEX_220100.WWV_FLOW_MAIL", line 1243
# APEX| |Backtrace: ORA-06512: at "SYS.UTL_TCP", line 63
# APEX| |ORA-06512: at "SYS.UTL_TCP", line 314
# APEX| |ORA-06512: at "SYS.UTL_SMTP", line 290
# APEX| |ORA-06512: at "SYS.UTL_SMTP", line 296
# APEX| |ORA-06512: at "APEX_220100.WWV_FLOW_MAIL", line 1243
# APEX| |ORA-06512: at "APEX_220100.WWV_FLOW_MAIL", line 1866
# APEX| |
I've downloaded again the certificate for smtp.gmail.com using
echo | openssl s_client -connect smtp.gmail.com:587 -starttls smtp | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > smtp.gmail.com.crt
but still getting the same certificate error.
Also included in the wallet all the certificates for the rest of the chain but that didn't fix the issue
Thanks

Pericles
Answer

Finally, I successfully complete the configuration.
I delete all the certificates on the Wallet and instead of trying to get the certificate from smtp.gmail.com I get the certificate chain of mail.google.com and in this way the connection finally was successful.
Regards

Marked as Answer by Pericles · May 26 2022
1 - 2

Post Details

Added on May 24 2022
#general-database-discussions
2 comments
1,588 views