Oracle SQL - If Exists, Drop Table & Create -

can 1 please guide me what's wrong query? in sql server check presence of object_id of table drop , re-create it. new oracle , wrote query:

declare table_exists integer;  begin  select count(*) table_exists sys.all_tables table_name='tablename1';  exception           when no_data_found                     table_exists :=0; if(table_exists)=1     execute immediate 'drop table tablename1;'   'create table tablename1;';   dbms_output.put_line('table dropped , re-created!'); else       execute immediate 'create table tablename1;';      dbms_output.put_line('new table created!'); end if; end; 

i output - anonymous block completed, table not created. table existing, dropped check if pl/sql creating table, no. wrong here? missing? please guide.

the exception clause lasts till next end , not next statement. if want continue after catching exception need add additional begin/end:

declare      table_exists integer;  begin      begin         select count(*) table_exists sys.all_tables table_name='tablename1';      exception      when no_data_found          table_exists :=0;      end;      if(table_exists)=1          execute immediate 'drop table tablename1;'              execute immediate 'create table tablename1;';          dbms_output.put_line('table dropped , re-created!');      else          execute immediate 'create table tablename1;';          dbms_output.put_line('new table created!');      end if;  end; 

as pointed out gordon, exception clause not needed in case since count(*) return 1 row. following sufficient:

declare      table_exists integer;  begin      select count(*) table_exists sys.all_tables table_name='tablename1';       if(table_exists)=1          execute immediate 'drop table tablename1;'              execute immediate 'create table tablename1;';          dbms_output.put_line('table dropped , re-created!');      else          execute immediate 'create table tablename1;';          dbms_output.put_line('new table created!');      end if;  end;