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.