Oracle - delete table if it exists

[laitinen]

Hi gurus!

I was wondering if any Oracle expert could tell me how to properly delete a table in Oracle.

In MS SQL Server and MySQL "if exsist" is a keyword, but this is not supported in Oracle. I dont want to be given a warning or error message that the table does not exist when executing my query.

Any can help me with this issue?

Cheers,

Laitinen

[davide++]

Hi all.

I don't understand your iusse.

If you run your DROP TABLE command by a Sql*Plus session you can simply ignore the (obvious) error message "ORA-00942: table or view does not exist".
If you're looking for a way to check if a table exists you can execute the query

SELECT COUNT(1)
  FROM USER_TABLES
 WHERE TABLE_NAME = 'MY_TABLE';

If you get 1 then MY_TABLE exists.

I hope this will help you.

[Krzemo]

Alternate (but not very much ) solution:

declare 
  p_exists number :=0;
begin
  select nvl((select 1 from user_tables where table_name='TABLE1'  and rownum=1),0) into p_exists from dual ;
if p_exists = 1 then
  execute immediate '...

Best regards,
Krzemo.