[SOLVED] Suppress ORA-00942 errors in ddl create scripts

Suppress ORA-00942 errors in ddl create scripts

Say you generate ddl to create all your db tables etc via Hibernate SchemaExport etc. What you get is a script which starts with drop statements at the beginning. Not a problem, as I want this. But running this script produces a crapload of ORA-00942 errors running on an Oracle db.

Since they're not really errors if the tables just didn't exist yet, I'd like my create script to be error free when it executes so it's easy to determine what (if any) failed.

What are my options? I DO want drop statements generated since tables may or may not exist yet, but I don't want a million ORA-s coming back at me that I have to check (to determine if they're actual errors) just because it couldn't drop a table that's brand new.

Solution

If you get a script of drop statements, and Hibernate won't do it for you then wrap the DROP TABLE statements in an IF to test if the table exists before dropping it:

IF EXISTS(SELECT NULL 
            FROM TABLE_XYZ) THEN
  DROP TABLE TABLE_XYZ;
END IF;