IF THEN ELSE problem

[Stedo]

hi,

i have a simple problem. I am writing a script to check if a table exists, if so drop it otherwise create it. I have the following where I have decared anumber to be a NUMBER:

IF anumber=1 THEN
DROP TABLE MyTABLE;

ELSE
CREATE TABLE AS SELECT * FROM TABLE2;

END IF;

When I try to execute the code I get the following error:

ERROR at line 11: 
ORA-06550: line 11, column 1: 
PLS-00103: Encountered the symbol "DROP" 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> << 
close current delete fetch lock insert open rollback 
savepoint set sql execute commit forall merge 
<a single-quoted SQL string> pipe 
<an alternatively-quoted SQL string> 
The symbol "lock was inserted before "DROP" to continue. 
ORA-06550: line 11, column 44: 
PLS-00103: Encountered the symbol ";" when expecting one of the following: 
. , @ in <an identifier> 
<a double-quoted delimited-identifier> partition subpartition

Where am I going wrong here?

Thanks
Stephen

 

[SantaMufasa]

Stephen,

Here are some issues to confirm:

1) "IF...THEN...ELSE" code in the Oracle World must appear within a PL/SQL block of code (which your sample code does not portray).

2) PL/SQL cannot execute DDL (e.g., "CREATE TABLE...", "ALTER TABLE...", or "DROP TABLE...") directly. You must embed DDL in an "EXECUTE IMMEDIATE" statement in PL/SQL.

3) Your "CREATE TABLE..." statement is syntactically incomplete since it mentions no actual TABLE name.

Here, then, is a working proof-of-concept of your code.

SQL> @tt_358
SQL> set verify off
SQL> select * from mytable;
select * from mytable
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from table2;

    FIELD1     FIELD2     FIELD3 FIELD4
---------- ---------- ---------- ----------
         1          1          1 OPEN
         1          1          1 CLOSED

SQL> accept num prompt "Enter a value for 'anumber': "
Enter a value for 'anumber': 0
SQL> declare
  2      anumber number := '&num';
  3  begin
  4      IF anumber=1 THEN
  5          execute immediate 'DROP TABLE MyTABLE';
  6      ELSE
  7          execute immediate 'CREATE TABLE myTABLE AS SELECT * FROM TABLE2';
  8      END IF;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select * from mytable;

    FIELD1     FIELD2     FIELD3 FIELD4
---------- ---------- ---------- ----------
         1          1          1 OPEN
         1          1          1 CLOSED

SQL> accept num prompt "Enter a value for 'anumber': "
Enter a value for 'anumber': 1
SQL> declare
  2      anumber number := '&num';
  3  begin
  4      IF anumber=1 THEN
  5          execute immediate 'DROP TABLE MyTABLE';
  6      ELSE
  7          execute immediate 'CREATE TABLE myTABLE AS SELECT * FROM TABLE2';
  8      END IF;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select * from mytable;
select * from mytable
              *
ERROR at line 1:
ORA-00942: table or view does not exist
******************************************************************************

Notice in the above code, that

1) mytable does not exist to begin with.
2) the code creates mytable after entering a '0' for 'anumber'.
3) the code drops mytable after entering a '1' for 'anumber'.

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Stedo]

hi,

Made the changes you suggested, everything works fine.

Thanks
Stephen

 

[Beilstwh]

What Dave wrote was correct (of course), but I was wondering why you want to drop and recreate tables? If you let us know, we might be able to come up with a better idea. Normally creating and dropping tables is not a good idea as a regular application task.

Bill
Oracle DBA/Developer
New York State, USA