If I drop a table and the table doesn't exist, I get an error.

Question

Hi,

I need to drop a table and make a new one. If I drop the table and the table doesn't exist, I get an error

How can I check if the table exists?

I'm working on Oracle 11g

Thanks in advance.

Answer 1

something like

select count(*) from user_tables 
where table_name= :table name

or

select count(*) from dba_tables
where owner = :table owner
and table_name = :table name

or a heavy-handed alternative:

begin execute immediate 'drop table table_name'; 
exception when others then null; 
end;

Answer 2

You could do something like this:

DECLARE v_exist PLS_INTEGER;
BEGIN

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

IF v_exist = 1 THEN
    EXECUTE IMMEDIATE 'DROP TABLE YOURTABLEHERE';
END IF;

Answer 3

u can also check by this select * from user_objects

Answer 4

I have been using the following procedure to take care of this:

create or replace procedure drop_table_if_exists ( p_table_name varchar2 )
is
  it_exist number;
begin
  select count(1) 
     into it_exists
     from user_tables
     where table_name = p_table_name
  ;
  if it_exists >= 1 then
    execute immediate 'drop table '||p_table_name;
  end if;
end;
/

exec drop_table_if_exists ( 'TABLE_TO_DROP' );

Hope that helps

Answer 5

eTABLE_OR_VIEW_DOES_NOT_EXIST  EXCEPTION;
PRAGMA EXCEPTION_INIT(eTABLE_OR_VIEW_DOES_NOT_EXIST, -942);

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE SCHEMA.WHATEVER';
EXCEPTION
  WHEN eTABLE_OR_VIEW_DOES_NOT_EXIST THEN
    NULL;
END;

Share and enjoy.