Spool Oracle SQL Query to File Without Screen Output

Tom Kyte posted something like this once and the issue was the CURSOR_SHARING parameter.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3696883368520

If it is set to FORCE at the system/database level on host2, it could be mapping/forcing the 'XXXX' hard coded value to an arbitrary 32 character variable.

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