Oracle - delete table if it exists
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
Re: Oracle - delete table if it exists
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
Code:
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.
Re: Oracle - delete table if it exists
Alternate (but not very much ;)) solution:
Code:
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.