Oracle learning

[b]How to Drop a Database Table Only If It Already Exists[/b]
1. MySQL was kind enough to make it very straightforward to drop an existing table. To drop the unfortunately-named table 'Drop_Me', run the following query -
DROP TABLE IF EXISTS Drop_Me

2. MS SQL makes it slightly harder to drop a table if it exists. In order to accomplish this, you will need to use an IF statement and the EXISTS function -

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'Drop_Me') AND type = (N'U'))
DROP TABLE Drop_Me

3. Oracle takes the complexity of dropping a table if it exists to the next level. In this piece of sample code, you need to know the owner of the table, which happens to be 'Me' -

DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM all_tables WHERE table_name='Drop_Me' AND owner='Me';
IF v_count = 1 THEN
EXECUTE IMMEDIATE 'DROP TABLE Me.Drop_Me';
END IF;
END;

[color=blue]Get the suggestion from forum[/color]
I would simply DROP the TABLE and then handle the exception if it
doesn't exist (assuming a big long script):
DECLARE
..
BEGIN
..
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE schema.table';
EXCEPTION
WHEN OTHERS THEN NULL;
END;
..
END;
Or if you want to handle individual exceptions instead of blindly
handling all of them like above:
DECLARE
table_notexists EXCEPTION;
PRAGMA EXCEPTION_INIT(table_notexists,-942);
..
BEGIN
..
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE schema.table';
EXCEPTION
WHEN table_notexists THEN NULL;
WHEN OTHERS THEN RAISE;
END;
..
END;
If you are always going to DROP the TABLE if it exists then the above
piece of code should work. If there are other conditions involved then
other code would be required.