Using transactions in sqlplus 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=&gt; begin;
BEGIN
dbpool.production=*&gt; delete from foo where account_id like '%1000%';
DELETE 9
dbpool.production=*&gt; commit;
COMMIT
dbpool.production=&gt; 


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&gt; 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?
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&gt; show autocommit
autocommit OFF


If it happens to be on:

SQL&gt; set autocommit off

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

SQL&gt; 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&gt; insert into abc values (1) ;

1 row created.

SQL&gt; select count(*) from abc;

  COUNT(*)
----------
         1

SQL&gt; rollback;

Rollback complete.

SQL&gt; 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.)