BEGIN/END and CREATE Table in single .sql file

Question

I have a small SQL script that I'm executing with Oracle's SQL*Plus to emulate create or replace on tables:

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;

SQL*Plus commandline is:

sqlplus aegsys15_owner/pass#234@MARVINUAT03 @createSymbolsTable.sql << EOF
> EOF

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:

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

However, if I do have the forward slash it gives me an error:

    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 <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
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

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?

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:

TABLE foo: Pass
SYNONYM bar: Fail
INDEX foo_1: Pass

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.

Cheers, Victor

you can run both BEGIN/END and create table in one sql file but you have to do minor edition in your cade — pratik garg, Sep 13 '11 at 06:12

score 2 · Accepted Answer · answered Sep 13 '11 at 06:10

2

you forgot to put in your if statement..

BEGIN     
     EXECUTE IMMEDIATE 'DROP TABLE symbols'; 
EXCEPTION     
  WHEN OTHERS THEN         
   IF SQLCODE != -942 THEN     
 --here you have to write something for this exception
 -- if you don't have any activity to do then you can use NULL (atleast)
 -- you can't put this if statement body empty in oracle
 NULL;
END IF; 
END; 
/

and better if you use declare also at the first line, before begin starts

answered Sep 13 '11 at 06:10

pratik garg

3,282
1
17
21

NULL is indeed the missing piece. But there's no reason for putting DECLARE on the first line. That's only needed if you want to declare variables or local procedures. – Codo Sep 13 '11 at 06:48
heya, Awesome, that fixed it =). Hmm, what did you mean by declares? I'm not using declares in this script - but if I did, they'd be at the top - that's what you meant, right? I'm still curious how people handle reporting success/fail for each SQL statement in their scripts, but perhaps that's a separate question... – victorhooi Sep 13 '11 at 06:56
@Codo you are right .. victorhooi this is not neccessary to put declare there at the top of this code.. but for anonymus procedure block need declare keyword that's why i told just for improve your code readability that you "can" add declare also there... – pratik garg Sep 13 '11 at 09:29
2

While this is syntactically correct, it will let all errors pass unreported. I doubt that is the intended result. – Allan Sep 13 '11 at 18:30

Kay Marczinzik · Answer 2 · 2011-09-15T07:21:01.143

-1

For output in slqplus use the prompt command. For output from pl/sql (i.e. inside a begin/end block) use the dbms_output.put_line() function.

prompt Creating foo table
begin
  create table foo...;
  dbms_output.put_line('success');
exception
  when others then
    dbms_output.put_line('fail');
end;
/

edited Sep 15 '11 at 07:21

answered Sep 13 '11 at 07:44

Kay Marczinzik

1
1

BEGIN/END and CREATE Table in single .sql file

2 Answers2

Linked