title: BEGIN/END and CREATE Table in single .sql file
body: <p>I have a small SQL script that I'm executing with Oracle's SQL*Plus to emulate create or replace on tables:</p>
  
  <pre><code>BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE symbols';
  EXCEPTION
      WHEN OTHERS THEN
          IF SQLCODE != -942 THEN
      END IF;
  END;
  /
  CREATE TABLE symbols ( 
              blah blah,
              blah blah,
          );
  EXIT;
  </code></pre>
  
  <p>SQL*Plus commandline is:</p>
  
  <pre><code>sqlplus aegsys15_owner/pass#234@MARVINUAT03 @createSymbolsTable.sql &lt;&lt; EOF
  &gt; EOF
  </code></pre>
  
  <p>If I omit the forward slash (/) after END, it seems to only process the first BEGIN/END block, and ignores the CREATE TABLE section underneath. Also, it doesn't print anything help out at all - just connecting/disconnecting:</p>
  
  <pre><code>SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 13 15:49:34 2011
  
  Copyright (c) 1982, 2009, Oracle.  All rights reserved.
  
  Connected to:
  Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
  With the Partitioning, OLAP, Data Mining and Real Application Testing options
  
   78  Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
  With the Partitioning, OLAP, Data Mining and Real Application Testing options
  </code></pre>
  
  <p>However, if I do have the forward slash it gives me an error:</p>
  
  <pre><code>    END IF;
      *
  ERROR at line 6:
  ORA-06550: line 6, column 5:
  PLS-00103: Encountered the symbol "END" when expecting one of the following:
  ( begin case declare exit for goto if loop mod null pragma
  raise return select update while with &lt;an identifier&gt;
  &lt;a double-quoted delimited-identifier&gt; &lt;a bind variable&gt; &lt;&lt;
  continue close current delete fetch lock insert open rollback
  savepoint set sql execute commit forall merge pipe purge
  
  CREATE TABLE symbols (
               *
  ERROR at line 1:
  ORA-00955: name is already used by an existing object
  </code></pre>
  
  <p>Firstly, what's the best way of having both the BEGIN/END exception block at the top, and the CREATE TABLE block in the same .sql file?</p>
  
  <p>And secondly, what's some way of getting some helpful output out of SQL*Plus? Each .sql file we run may have multiple CREATE statements (tables, indexes, synonyms etc.). Our ideal output would be something like:</p>
  
  <pre><code>TABLE foo: Pass
  SYNONYM bar: Fail
  INDEX foo_1: Pass
  </code></pre>
  
  <p>Not sure if something like that is achievable with SQL or PL/SQL though - happy to write a Bash or Python wrapper script around this, if you guys think that's a better solution.</p>
  
  <p>Cheers,
  Victor</p>