drop table if exists

Martin Preiss

Some other RDBMS - for example MySQL and postgreSQL: PostgreSQL: Documentation: 9.4: DROP TABLE - support a "drop table if exists" command that avoids an error if you try to drop a table that does actually not exists. I think that's sometimes useful.

ctrieb

A nice idea, but not really necessary. You can write a simple error routine to solve this problem in actual ORACLE DB Version.

Martin Preiss

A nice idea, but not really necessary. You can write a simple error routine to solve this problem in actual ORACLE DB Version.

yes, "not really necessary" is the fitting category - but on the other hand it should be quite cheap to implement.

Pravin Takpire

not required as it errors out

abhinivesh.jain

Getting error is more logical. Table or view doesn't exist error comes even when someone tries to select from non-existent table so it absolutely makes sense to get the same error when someone tries to drop it.

Thanks,

Abhi

top.gun

Isn't this what BEGIN/EXCEPTION is for?

Rather than get an error, you just go to the exception, and handle the missing object there.

Sven W.

Isn't this what BEGIN/EXCEPTION is for?

Rather than get an error, you just go to the exception, and handle the missing object there.

Not really. BEGIN / EXCEPTION is plsql or at least an anonymous block in SQL.

DROP TABLE is sql. It is not allowed to write this inside a plsql block. So exception handling for that is really tricky.

The usual way to solve that is to switch SQL*PLUS error handling off before the drop and on after the drop. Which is a nuisance.

typical example from certain installation scripts:

whenever sqlerror continue;

drop table abc;

whenever sqlerror exit failure;

create table abc ...

@CTrieb: Do you have another simple way how to solve that?

pnosko

It would greatly simplify writing installation scripts.

Lukas Eder

A nice idea, but not really necessary. You can write a simple error routine to solve this problem in actual ORACLE DB Version.

That workaround is really a pain if you're writing large migration scripts that might run several times depending on how you deploy things... Many things aren't really necessary. For instance, AVG(x) is not necessary because it can be calculated as SUM(x) / COUNT(*), but it's still super useful, no?

Lukas Eder

not required as it errors out

I get really anxious when my complex installation scripts have errors in them. I'd like to have no error at all or meaningful errors, no errors I can "safely ignore" (after manual and tedious introspection)

Lukas Eder

A few databases already support such syntax, including PostgreSQL and MySQL. Note that this isn't limited to only DROP TABLE statements, but it could work with pretty much any DDL statement:

• DROP <object> [ IF EXISTS ]
• ALTER <object> [ IF EXISTS ] ...
• ALTER <table> [ IF EXISTS ] ADD <column> [ IF NOT EXISTS ]
• ALTER <table> [ IF EXISTS ] DROP <column> [ IF EXISTS ]
• CREATE <object> [ IF NOT EXISTS ]

I would find this really really useful!