How to Find Out If an Oracle Database Is Set to Autocommit

How do I find out if an oracle database is set to autocommit?

Oracle SQL Developer 4

• Window > Preferences > Database > Advanced > check/uncheck Autocommit

Oracle SQL*Plus

• Use SET AUTOCOMMIT

SQuirreL SQL Client Version 3.7

• File > New Session Properties > SQL tab > SQL section > check/uncheck Auto Commit SQL

Toad for Oracle Xpert 9.6.1.1

• View > Toad Options... > Oracle > Transactions > check/uncheck Commit after every statement

SQL Workbench/J

• Check the "Autocommit" property in the connection profile to set the connection default
• Use SQL > Autocommit from the menu to change it dynamically
• Use SET AUTOCOMMIT

Oracle database SELECT... FOR UPDATE with autocommit on

As said in documentation:

the default is for a SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved. In almost all cases, however, a statement is completed, and therefore committed, right after it is executed

Another documentation:

Enabling auto-commit may be more convenient, but gives you less control. For example, you have no option to roll back changes. In addition, some SQLJ or JDBC features are incompatible with auto-commit mode. For example, you must disable the auto-commit flag for update batching or SELECT FOR UPDATE syntax to work properly.

So for this query type you should use manual commit

What is use of Connection.setAutoCommit(false); when using Oracle database?

Oracle documentation provides a very good explanation of when and why this should be used. Please go through the same!

If your JDBC Connection is in auto-commit mode pro grammatically or by default, (this is by default, fyi), then every SQL statement is committed to the database upon its completion.

You can refer to this question for more detailed explanation on the same topic.

How set Autocommit gobal in the instance before running a procedure in Oracledb using Node.js

Pass autoCommit as an option to execute() (or executeMany()). See this example.

You can also set the option globally, if that suits your business requirements: oracledb.autoCommit = true.

Both will do what your SQL*Plus example does.

See the documentation:

https://oracle.github.io/node-oracledb/doc/api.html#propexecautocommit

and

https://oracle.github.io/node-oracledb/doc/api.html#propdbisautocommit

Finally, I'd recommend using the async/await style of programming, which is usable with Node.js 7.6 onwards.

How does Oracle handle SQL when autocommit=false and there is no commit or rollback?

If you have neither a commit nor a rollback, the transaction would remain open waiting for you to issue one or the other. If you tried to close the Connection object the results are implementation-dependent. Your transaction might be committed, it might be rolled back.

How to set autocommit value to false in Oracle Data Source connection?

1.Store it in a variable

DataSource ds = (DataSource)ctx.getBean("dataSourceBean");
try {
    Connection con =ds.getConnection();
    con.setAutoCommit(false);
    System.out.println("Autocommit " +con.getAutoCommit());
} catch (SQLException e) {          
    e.printStackTrace();
}

When you call the ds.getConnection() again , you get another connection from pool

2.Change Weblogic DataSource configuration
Auto commit transactions if not explicitly committed or rolledback

is setautocommit(true) needed after conn.commit()

That depends on where you got that connection from. If you created the connection yourself, there is no need to restore the state of auto commit.

If you got it from a data source, you should restore the state to what it was because the data source might keep the connections in a pool and the next piece of code might not expect what you set.

commit() doesn't influence the value of auto commit. Enabling auto commit just makes sure that the JDBC driver calls commit() after each statement that you execute. You can still call commit() as often as you like, it just won't have any effect (except that rollback() will not always do what you want).

[EDIT] How auto commit is handled depends on your connection pool. dbcp has a config option to turn auto commit off before giving you a connection, c3p0 will roll back connections when you return then to the pool. Read the documentation for your connection pool how it works.

If you don't know which pool is used, the safe solution is to set auto commit to false whenever you get a connection and to roll back the connection if you get an exception. I suggest to write a wrapper:

public <T> T withTransaction( TxCallback<T> closure ) throws Exception {
    Connection conn = getConnection();
    try {
        boolean autoCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);

        T result = closure.call(conn); // Business code

        conn.commit();
        conn.setAutoCommit(autoCommit);
    } catch( Exception e ) {
        conn.rollback();
    } finally {
        conn.close();
    }
}

This code will correctly handle the connection for you and you don't need to worry about it anymore in your business code.

---

Related Topics