Dropper my schema tables on Oracle

Question

I am using a Schema in Oracle (sql Developer) and within my schema there are already several tables created that I was using earlier. Now running a new script in this scheme it shows the following error:

Relatório de erros -
ORA-00955: nome já está sendo usado por um objeto existente
00955. 00000 -  "name is already used by an existing object"
*Cause:    
*Action:

I know the reason for the error is that there is already a table with the same name in the schema, but my question is... In Postgres I used DROP TABLE IF EXISTS name_table CASCADE; and in oracle is not allowed to use in this way, is there any similar command that has the same purpose? That every time I recompile (F5) the entire script it replaces the tables?

Answer 1

To solve this problem you will need to open an anonymous sql block, check before if it exists and if there is no create:

Create if there is no

declare
nCount NUMBER;
v_sql LONG;

begin
SELECT count(*) into nCount FROM dba_tables where table_name = 'EMPLOYEE';
IF(nCount <= 0)
THEN
v_sql:='
create table EMPLOYEE
(
ID NUMBER(3),
NAME VARCHAR2(30) NOT NULL
)';
execute immediate v_sql;

END IF;
end;

Dropar If there is one

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE mytable';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;