Using transactions in sqlplus

Question

I'm coming to oracle from postgresql. In postgresql, I can use the command-line tool, psql, to execute a delete or update in a transaction:

dbpool.production=> begin;
BEGIN
dbpool.production=*> delete from foo where account_id like '%1000%';
DELETE 9
dbpool.production=*> commit;
COMMIT
dbpool.production=> 

I like to see that I got the count I expected before committing the transaction.

Things don't work like that in sqlplus, the Oracle command-line tool. When I type "begin;", it just starts reading more input, never apparently doing anything:

SQL> begin;
 2 update opacs_work_orders set customer_id = null;
 3 commit;
 4

How can I, using sqlplus, execute one or more statements with a transaction?

• user1822: In SQL*Plus auto-commit is disabled by default. Just run your DML and then issue a commit or rollback to end the transaction. It's the same as using \set AUTCOMMIT off in pgsql (which is the default on all my computers)

Answer 1

You simply don't use begin in sqlplus if you're just going to issue a series of SQL queries. You're in a transaction already as soon as you issue some SQL. You can't really be outside of a transaction anyway for practical purposes (sure, if you've just logged in, or just committed and haven't started anything else, well, you're not in a transaction).

A few things to be careful with though:

• sqlplus does have an autocommit setting. It's off by default in modern versions, but just to make sure:

  SQL> show autocommit
  autocommit OFF
  

  If it happens to be on:

  SQL> set autocommit off
  

• sqlplus commits on exit by default even in modern versions. To disable that:

  SQL> set exitcommit off
  

  (That's fairly new, appeared in 11g or 11gR2. Previously always committed on exit.)

• DDL commits. (Twice. Once before, once after.) Note that truncate is DDL in Oracle.

Aside from these gotchas, you're in a transaction already when you start sqlplus, and can commit or rollback as you please.

$ sqlplus mat
SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 20 19:51:31 2014
...
SQL> insert into abc values (1) ;
1 row created.
SQL> select count(*) from abc;
 COUNT(*)
----------
 1
SQL> rollback;
Rollback complete.
SQL> select count(*) from abc;
 COUNT(*)
----------
 0

You need to use begin/end when you want to run a PL/SQL block. (The short/single line form for that being exec.)

Answer 2

The answer

You're in a transaction already. You can't really be outside of a transaction anyway.

is not true. By default, an Oracle transaction starts at the first DML (insert/update/delete), and ends with commit/rollback. You can start a transaction manually, with the SET TRANSACTION command, but most people don't.

As for a clearer answer between PG and Oracle:

In Postgres, BEGIN tells PG to start a transaction (otherwise it's in auto-commit). The transaction ends of course on commit/rollback.

In Oracle, BEGIN (or DECLARE) is the start of a PL/SQL block. The block ends with the END; keyword followed by a / on the next line to tell SQL*Plus to execute the block. The block and transaction are completely separate - you could be in a transaction before you execute the block, or the block could start a transaction by issuing some DML.

In Oracle, if you are not in a transaction, a commit/rollback is essentially a no-op. You can tell if you're in a transaction by

SELECT count(*)
 FROM v$session v
 WHERE v.AUDSID = sys_context('userenv','sessionid')
 AND v.TADDR IS NOT NULL;

You'll get 1 if you are in a transaction, 0 if not.

• user1822: A side note: in Postgres you can turn off autocommit as well and then you don't need begin to start a transaction.