Suppress ORA-00942 errors in ddl create scripts

Question

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.

Answer 1

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;

Answer 2

"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."

Ideally we should maintain our schema properly, using source control and configuration management best practices. In this scenario we know beforehand whether the schema we run our scripts against contains those tables. We don't get errors because we don't attempt to drop tables which don't exist.

However it is not always possible to do this. One alternate approach is to have two scripts. The first script just has the DROP TABLE statements, prefaced with a friendly

PROMPT  It is safe to ignore any ORA-00942 errors in the following statements

The second script has all the CREATE TABLE statements and leads off with

PROMPT  All the statements in this script should succeed.  So investigate any errors

Another option is to use the data dictionary:

begin
    for r in ( select table_name from user_tables )
    loop
        execute immediate 'drop table '||r.table_name
                    ||' cascade constraints';
    end loop;
end;

Be careful with this one. It is the nuclear option and will drop every table in your schema.