Oracle – Using Transactions in SQL*Plus

oracle-11g-r2sqlplustransaction

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?

Best Answer

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.)

Related Question