Sqlcl Error report - Could not rollback with auto-commit set on

user12197742

Sqlcl - giving this error. Its a bit of a deal breaker for me. Seems to be new behaviour

Mike Kutz

Huh?

"Auto commit = ON" is NOT default behavior.

user12197742

error message is "Could not rollback with auto-commit set on"

auto-commit is off

I believe its reverting to the default jdbc behaviour

user12197742

SQL> show auto

autocommit OFF

SQL> set autocommit off

Set autocommit script command forced off, as connection level autocommit is on

SQL>

SQL>

SQL> create table rwtest(id number);

Table RWTEST created.

SQL> insert into rwtest values (1999);

1 row inserted.

SQL> rollback;

Error starting at line : 1 in command -

rollback

Error report -

Could not rollback with auto-commit set on

User_H3J7U

-- default settings after starting
SQL> rollback;

Rollback complete.

SQL> set autocommit on
SQL> rollback;

Rollback complete.

SQL> set autocommit off
SQL> rollback;

Rollback complete.

SQL> script
  2  conn.setAutoCommit(true);
  3* /
SQL> set autocommit on
Set autocommit script command forced off, as connection level autocommit is on
SQL> set autocommit off
Set autocommit script command forced off, as connection level autocommit is on
SQL> rollback;

Error starting at line : 1 in command -
rollback
Error report -
Could not rollback with auto-commit set on

user12197742

My default is different:

Picked up JAVA_TOOL_OPTIONS: -Djava.vendor="Sun Microsystems Inc."

SQLcl: Release 21.1 Production on Thu Nov 11 14:46:06 2021

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Attempting vault based connection.

    Connecting to : system/[email protected]:oracle:thin:@mydb

Connected using xxxxxxxxxxxxxxxxxxx

Connected.

SQL> rollback;

Error starting at line : 1 in command -

rollback

Error report -

Could not rollback with auto-commit set on

SQL>

User_H3J7U

Start sqlcl -verbose and execute:

show login
show jdbc

Check glogin/login scripts (locations under show login) and jdbc connection settings.

Use code formatting when publishing your messages.

user12197742

sql> show login

Glogin possible locations

--------------------

Login possible locations

--------------------

.\

SQL> show auto

autocommit OFF

SQL> show jdbc

-- Database Info --

Database Product Name: Oracle

Database Product Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Database Major Version: 12

Database Minor Version: 2

-- Driver Info --

Driver Name: Oracle JDBC driver

Driver Version: 21.1.0.0.0

Driver Major Version: 21

Driver Minor Version: 1

Driver URL: jdbc:oracle:thin:@xxxdev

Driver Location: Unable to load jar:C:/Program%20files/sqlcl/lib/ojdbc8.jar

Url: jar:file:/C:/Program%20files/sqlcl/lib/ojdbc8.jar ! / oracle / jdbc / oracledriver.class

user12197742

So I fixed by adding to JAVA_TOOLS_OPTIONS -Doracle.jdbc.autoCommitSpecCompliant=false

eg.