Oracle: How to drop a table if it exists

Easiest way:
Just drop it and ignore the message that it didn't exist in the first place.

For logic, you need to be in PL/SQL and you cannot to DDL natively in there so you need to use the execute immediate like you have in other questions.

Just execute the drop and trap the exception?

Like in your previous question:  You could select from user_tables/all_tables/dba_tables into a variable, then check the variables value and drop if there is a positive results.


Now the bigger question:
Why drop the table in the first place?  When questions come into Oracle from other database products and I see "drop table" in "code", I think temp tables.  You really shouldn't do that in Oracle.  There are other ways...

declare
v_exist number;
begin
select count(*) into v_exist
from user_tables
where table_name = 'TABLE_NAME'
if cnt = 1 then
 execute immediate 'drop table TABLE_NAME';
end if;
end;
I used the user_tables view because you may have select access to another schema table but not a drop table privilege.

do the drop and then capture the exception, and you might want to check out the PURGE option

only keep one of the execute immediate lines

BEGIN
    EXECUTE IMMEDIATE 'drop table table_name';
    --EXECUTE IMMEDIATE 'drop table table_name purge';  --- purge prevents saving it in the recyclebin
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line(SQLERRM);
END;

Select allOpen in new window

I wouldn't try to verify that the table exists with a select.  That is a totally unnecessary step.  Just drop the table and catch the error.  The drop checks for existence for you.

DECLARE 
    not_exist EXCEPTION; 
    PRAGMA EXCEPTION_INIT(not_exist, -942); 
BEGIN 
    ...
    BEGIN 
        EXECUTE IMMEDIATE 'drop table table_name'; 
    EXCEPTION 
        WHEN not_exist THEN 
          NULL; 
    END; 
    ....
END; 

Select allOpen in new window

I don't like to use WHEN OTHERS because that could ignore errors that you really shouldn't be ignoring.  Ignore the message that you want to ignore.

the when others above doesn't ignore any errors -it reports all of them.

but, I totally agree with the sentiment.
I was simply illustrating the basic structure of an exception handler to address the main question,

When I execute the below code in DbVisualizer it generates an error, and there's a red squiggly below the SELECT in the third line.  

@delimiter ++;
declare v_exist number(1);

SELECT COUNT(*) INTO v_exist FROM user_tables WHERE table_name = 'FOO';

if v_exist = 1 then
   execute immediate 'drop table FOO';
end if;

CREATE TABLE FOO (bar number(1));

SELECT bar FROM FOO;

Select allOpen in new window

 14:43:01  [@DELIMITER - 0 row(s), 0.000 secs]  Command processed
 14:43:01  [DECLARE - 0 row(s), 0.071 secs]  [Error Code: 6550, SQL State: 65000]  ORA-06550: line 3, column 1:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
The symbol "begin" was substituted for "SELECT" to continue.
ORA-06550: line 9, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

   ( begin case declare end exception 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
... 2 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.071/0.000 sec  [1 successful, 0 warnings, 1 errors]

Select allOpen in new window

You need a begin statement. You'll also need an end statement plus you need to use an execute immediate statement for ddl commands (i.e. create table foo).

Need a begin between declaring your variables and actually executing code:

FYI:
PL/SQL is pretty much Ada.

declare 
v_exist number(1);

begin
SELECT...

Select allOpen in new window

Okay.  Revised code, new error message

@delimiter ++;
declare v_exist number(1);

begin

   SELECT COUNT(*) INTO v_exist FROM user_tables WHERE table_name = 'FOO';
   
   if v_exist = 1 then
      execute immediate 'drop table FOO';
   end if;
   
end;

execute immediate 'CREATE TABLE FOO (bar number(1))';

SELECT bar FROM FOO;

Select allOpen in new window

 14:49:38  [@DELIMITER - 0 row(s), 0.000 secs]  Command processed
 14:49:38  [DECLARE - 0 row(s), 0.071 secs]  [Error Code: 6550, SQL State: 65000]  ORA-06550: line 13, column 9:
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:

   ;
... 2 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.071/0.000 sec  [1 successful, 0 warnings, 1 errors]

Select allOpen in new window

The last execute immediate is outside the code block.

It is a PL/SQL command and needs to be inside a block of code.

@delimiter ++;
declare v_exist number(1);

begin

   SELECT COUNT(*) INTO v_exist FROM user_tables WHERE table_name = 'FOO';
   
   if v_exist = 1 then
      execute immediate 'drop table FOO';
   end if;
   

execute immediate 'CREATE TABLE FOO (bar number(1))';

end;

SELECT bar FROM FOO;

Select allOpen in new window

an alternative:
exec execute immediate 'CREATE TABLE FOO (bar number(1))';

Which is a shortcut to:
begin execute immediate 'CREATE TABLE FOO (bar number(1))'; end;

To get all to work -
@delimiter ++;
declare v_exist number(1);
BEGIN
SELECT COUNT(*) INTO v_exist FROM user_tables WHERE table_name = 'FOO';
if v_exist = 1 then
   execute immediate 'drop table FOO';
end if;

execute immediate 'CREATE TABLE FOO (bar number(1))';

SELECT bar into something FROM FOO;
END;
++
@delimiter;++

@slightv - Didn't work, red squiggly under 'SELECT' on last line, error message

[Error Code: 6550, SQL State: 65000]  ORA-06550: line 16, column 1:
PLS-00103: Encountered the symbol "SELECT"

awking00 - Also didn't work, not sure what you meant by SELECT bar INTO something, when I removed the 'SELECT bar into something' line it gives a red squiggly under the declare on line 2 and returns and error message

[Error Code: 1031, SQL State: 42000]  ORA-01031: insufficient privileges
ORA-06512: at line 5

Looks like it is a tool specific thing.  I'm out.

@slightwv - Your code works in SQL developer, so I guess we're good.
@awking00 - Your code works as well in SQL Developer without the @delimeter code, so good there too.

This is way simpler in SQL Server.

In PL/SQL when issuing a select statement, it must be selected into a variable If there had been a variable "something" declared as a number the code in ID: 41458866 would have worked.