Monday, February 15, 2010

Drop table if it exists in Oralce

If you want to drop a table which is not in existence then surely you will get an error - table or view does not exist. If you put this type of code in a stored procedure then this error halt the whole procedure execution.

So, the better solution for this is to check whether the table exists or not. If it exists then drop that. The logic can be put in a stored procedure (not in a function as function cannot perform DML or DDL) then we can call this procedure from other procedure and we don't have to duplicate this code. So the procedure will be very simple like the following -

CREATE OR REPLACE procedure drop_table_ifexist(tableName varchar2) IS
tableCount NUMBER := 0;
BEGIN
select count(*) into tableCount
from user_tables
where table_name = upper(tableName);

--oracle store all table names in upper cases.

if(tableCount > 0) then
execute immediate 'drop table '||tableName;
end if;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END drop_table_ifexist;
/

This procedure will drop a table of its own if the table exists. Table name can be queried from two other tables user_objects (collection of all objects owned by this user) and all_objects (collection of all objects privileged by this user).

No comments: