3

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?

  • 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) – a_horse_with_no_name Oct 20 '14 at 17:53
5

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

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.

  • A side note: in Postgres you can turn off autocommit as well and then you don't need begin to start a transaction. – a_horse_with_no_name Nov 4 '14 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.