Drop table from oracle database if table exist in sql statement

database oracle drop-table
user3500109 picture user3500109 · Apr 5, 2014 · Viewed 66.3k times · Source

I have to use an SQL statement to drop a table, it will crash if the table doesn't exist. Is it possible to use IF statement to drop the table s.executeUpdate("DROP TABLE employee");

Answer

Mark J. Bobak picture Mark J. Bobak · Apr 5, 2014

Oracle does not support a construct like drop table if exists my_table, which is apparently legal syntax in MySQL (and possibly other RDBMSs).

In a .SQL script, where you're running DDL to DROP and/or CREATE various objects, the Oracle standard is to drop the object, and ignore the error in cases where the object does not exist. If you wish, you can write code to check if the object exists (see DBA_OBJECTS view) to only drop if it exists.

from the s.executeUpdate, I gather that you're doing this in Java? If it was me, I'd just do the drop and ignore any not exists error.

Hope that helps.

Related questions

How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
How can I get column names from a table in Oracle?

I need to query the database to get the column names, not to be confused with data in the table. For example, if I have a table named EVENT_LOG that contains eventID, eventType, eventDesc, and eventTime, then I would …

Read More