Forum Stats

  • 3,767,980 Users
  • 2,252,736 Discussions
  • 7,874,399 Comments

Discussions

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

user12197742
user12197742 Member Posts: 10 Blue Ribbon

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

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 5,789 Silver Crown

    Huh?

    "Auto commit = ON" is NOT default behavior.

  • user12197742
    user12197742 Member Posts: 10 Blue Ribbon

    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
    user12197742 Member Posts: 10 Blue Ribbon

    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
    User_H3J7U Member Posts: 632 Silver Trophy
    edited Nov 11, 2021 12:36PM
    -- 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
    user12197742 Member Posts: 10 Blue Ribbon

    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
    User_H3J7U Member Posts: 632 Silver Trophy

    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
    user12197742 Member Posts: 10 Blue Ribbon

    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
    user12197742 Member Posts: 10 Blue Ribbon

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

    eg.