`Java::JavaSql::SQLException: Could not rollback with auto-commit set on` when Oracle 12c ojdbc7.jar used

[yahonda]

Steps to reproduce

$ rspec ./spec/plsql/type_spec.rb:105
Coverage may be inaccurate; set the "--debug" command line option, or do JRUBY_OPTS="--debug" or set the "debug.fullTrace=true" option in your .jrubyrc
Run options: include {:locations=>{"./spec/plsql/type_spec.rb"=>[105]}}
F

Failures:

  1) Type find should find existing type
     Failure/Error: Unable to find oracle.jdbc.driver.PhysicalConnection.rollback(oracle/jdbc/driver/PhysicalConnection.java to read failed line

     Java::JavaSql::SQLException:
       Could not rollback with auto-commit set on
     # oracle.jdbc.driver.PhysicalConnection.rollback(oracle/jdbc/driver/PhysicalConnection.java:2427)
     # java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:498)
     # RUBY.rollback(/home/yahonda/git/ruby-plsql/lib/plsql/jdbc_connection.rb:79)
     # RUBY.rollback(/home/yahonda/git/ruby-plsql/lib/plsql/sql_statements.rb:61)
     # RUBY.block in (root)(/home/yahonda/git/ruby-plsql/spec/plsql/type_spec.rb:100)
     # java.lang.invoke.MethodHandle.invokeWithArguments(java/lang/invoke/MethodHandle.java:627)

Finished in 3.15 seconds (files took 7.16 seconds to load)
1 example, 1 failure

Failed examples:

rspec ./spec/plsql/type_spec.rb:105 # Type find should find existing type

$

Environment:

$ ruby -v
jruby 9.1.6.0 (2.3.1) 2016-11-09 0150a76 OpenJDK 64-Bit Server VM 25.111-b16 on 1.8.0_111-b16 +jit [linux-x86_64]

$ java -version
openjdk version "1.8.0_111"
OpenJDK Runtime Environment (build 1.8.0_111-b16)
OpenJDK 64-Bit Server VM (build 25.111-b16, mixed mode)
$

$ opatch lspatches
24315824;Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)
24006101;Database Patch Set Update : 12.1.0.2.161018 (24006101)

OPatch succeeded.
$

Workaround:

Setting JAVA_OPTS as follows.

$ echo $JAVA_OPTS
-Doracle.jdbc.autoCommitSpecCompliant=false
$

This is due to https://docs.oracle.com/database/121/READM/chapter12101.htm#READM316

3.24.4.3 Bug 16063217
Several changes were made to local transaction processing for compliance with the JDBC spec 4.1. If setAutoCommit(true) is called and a local transaction exists, the transaction is automatically committed (in earlier releases, no action was taken). If commit or rollback is called while in AUTOCOMMIT mode, the driver will throw an exception (again, in earlier releases, no action was taken). It is possible that your application will have these situations and it may be difficult to immediately change the software.

Workaround: 
By adding the -Doracle.jdbc.autoCommitSpecCompliant=false system property to the command line, the old behavior of no action will be preserved.

[yahonda]

If you have an access to support.oracle.com Refer "Could not commit with auto-commit set on Exception with JDBC 12.1 (Doc ID 1633346.1)"

[javornikolov]

Thank you @yahonda!
Perhaps we should set autocommit=false in spec_helper/get_connection and maybe even for connection.create*. Otherwise I think we have autocommit=false for OCI and autocommit=true for JDBC which is kind of inconsistent.

[yahonda]

In the long term, agreed to set autocommit = false in somewhere. I prefer not to introduce different auto commit inconsistency behavior between OCI and JDBC. In the meantime it should be enough to keep this issue open then users will know -Doracle.jdbc.autoCommitSpecCompliant=false flag is available.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[yahonda]

It should be open.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[yahonda]

Still valid.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[yahonda]

Well, I believe it is still valid but have no time to dig into it yet.