Dropping the tables from my schema in Oracle

Question

Dropping the tables from my schema in Oracle

Navigation

#1 by (1 votes)

1

I am using a Schema in Oracle (sql developer) and within my schema there are already several tables created that I was using previously. Now running a new script in this scheme it has 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 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?

sql oracle oracle11g sqldeveloper

asked by anonymous 05.11.2017 / 02:37

1 answer

Run code inside the event listener How to securely save a private key?

score 1 · Accepted Answer

To solve this problem you will need to open an anonymous sql block, check if it exists and if it does not exist:

Create if it does not exist

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;

Drop if there is

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