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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
I 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

dvohra21

It is documented.

"The Oracle.connect() method defaults to a false setting of the auto-commit flag. However, it also has signatures to set it explicitly. In the Oracle JDBC implementation, the auto-commit flag defaults to true."

Key Programming Considerations

kdario

Well, this is documentation for sqlj and first sentence just confirms what I said(that auto-commit by default should be false)

Just to clarify my original post:

When you retrieve jdbc connection in adf application(for example, in app module impl class), that connection already has autocommit=false (because framework will set this property)

Difference between adf 11.x and 12.x is in setting db proxy user (by using conn.openProxySession() ) :

In 11.x (which uses ojdbc6 driver) invocation of openProxySession() method will preserve autocommit=false

In 12.x (which uses ojdbc7 driver) invocation of openProxySession() method will reset autocommit to true (and adf app will not work correctly with this setting)

Dario

dvohra21

ojdbc7.jar is based on JDBC 4.1 and JDBC 4.1 still sets auto-commit to true by default.

"The default is for auto-commit mode to be enabled when the Connection object is

created."

http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf?AuthParam=1404073866_5e28356fa3b11ea367d6b…

Could auto commit be false for the PROXYTYPE_USER_NAME specified?

OracleConnection (Oracle Database JDBC Java API Reference)

dvohra21

CONNECTION_PROPERTY_AUTOCOMMIT_DEFAULT is true in both 11g and 12c.

http://docs.oracle.com/cd/E18283_01/appdev.112/e13995/constant-values.html#oracle_jdbc_OracleConnection_CONNECTION_PROPERTY_AUTOCOMMIT_DEFAULT

http://docs.oracle.com/cd/E16655_01/appdev.121/e17663/constant-values.html#oracle_jdbc_OracleConnection_CONNECTION_PROPERTY_AUTOCOMMIT_DEFAULT