oracle drop table if exists pl/sql not working using exception

Question

oracle: drop table if exists pl/sql not working using exception. e.g.,

SQL> SET SERVEROUTPUT ON
SQL> BEGIN 
SQL>   EXECUTE IMMEDIATE 'DROP TABLE Foo'; 
SQL>  EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(sqlerrm); 
SQL> END;
SQL> /

The Foo table is not dropped.

create table Foo (id number(20,0), name varchar(20), 
    primary key(id));
create table Bar (id number(20,0), name varchar(20), 
    primary key(id),
    constraint FK1 foreign key (id) references Foo (id));

There is a FK constraint.

SET CONSTRAINTS ALL DEFERRED;
BEGIN EXECUTE IMMEDIATE 'DROP TABLE Foo'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
SET CONSTRAINTS ALL IMMEDIATE;

The table Foo is not deleted.

Please share the ddl (create table command) of the Foo table. I doubt, you must have created Foo table using double quotes (case sensitive). Like `"Foo"` — Popeye, Jan 26 '20 at 06:01
Try to use table name wrapped in double quotes as `"Foo"` in drop table statement — Popeye, Jan 26 '20 at 06:16
Are you able to return a record through issuing `select * from user_tables where regexp_like(table_name,'foo','i')` — Barbaros Özhan, Jan 26 '20 at 07:53

score 0 · Answer 1 · answered Jan 26 '20 at 12:01

0

How about this?

If table doesn't exist:

SQL> set serveroutput on;
SQL> select table_name from user_tables where upper(table_name) = 'FOO';

no rows selected

SQL> declare
  2    l_nex exception;
  3    pragma exception_init (l_nex, -942);
  4  begin
  5    execute immediate 'drop table foo';
  6  exception
  7    when l_nex then
  8      dbms_output.put_line('You can not drop a table that does not exist');
  9  end;
 10  /
You can not drop a table that does not exist

PL/SQL procedure successfully completed.

SQL>

If table exists:

SQL> create table foo (id number);

Table created.

SQL> declare
  2    l_nex exception;
  3    pragma exception_init (l_nex, -942);
  4  begin
  5    execute immediate 'drop table foo';
  6  exception
  7    when l_nex then
  8      dbms_output.put_line('You can not drop a table that does not exist');
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select table_name from user_tables where upper(table_name) = 'FOO';

no rows selected

SQL>

answered Jan 26 '20 at 12:01

Littlefoot

131,892
15
35
57

what is -942? what is the exception name for TABLE_NOT_EXISTS? – eastwater Jan 26 '20 at 21:45
942 is "table or view does not exist". I don't think there's a predefined exception for it (such as NO_DATA_FOUND). – Littlefoot Jan 26 '20 at 21:59
But there is FK constraints. The table should be deleted regardless of FK constraints. – eastwater Jan 26 '20 at 22:17
That's not going to happen until you drop the foreign key constraint. – Littlefoot Jan 27 '20 at 06:09
SET CONSTRAINTS ALL DEFERRED; why it is not working? – eastwater Jan 27 '20 at 07:55

oracle drop table if exists pl/sql not working using exception

1 Answers1