Home » SQL & PL/SQL » SQL & PL/SQL » avoid error message when drop a non-existing table
avoid error message when drop a non-existing table [message #434570] Wed, 09 December 2009 14:52 Go to next message
vdurieu
Messages: 4
Registered: December 2009
Junior Member
hello

when I try to drop a table that doesn't exists, I receive a error message. Is it possible to avoid that message ?

in mysql I know the command : create table x if exists
does it exists something similar in oracle ?
thanks
Re: avoid error message when drop a non-existing table [message #434571 is a reply to message #434570] Wed, 09 December 2009 14:55 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>does it exists something similar in oracle ?
No

You should not be CREATE TABLE & DROP TABLE as part of application code.

Any/all tables should be created during application installation & remain static between application release updates.
Re: avoid error message when drop a non-existing table [message #434588 is a reply to message #434570] Wed, 09 December 2009 18:20 Go to previous messageGo to next message
mikesmithers
Messages: 2
Registered: December 2009
Location: United Kingdom
Junior Member
Hi,

I know that I find it quite annoying running a table creation script where I've had to start with a drop table on the assumption that I'll be running it on an environment more than once ( in Dev or Test, for example).

One way of avoiding this is to create a PL/SQL procedure to do the drop for you.

NOTE - I'm assuming here that you want to drop/create a table in the schema of the user you are logged on as.

First the procedure -

CREATE OR REPLACE PROCEDURE pr_dropit( pa_table_name IN VARCHAR2) AS

l_dummy PLS_INTEGER;
CURSOR c_exists IS
SELECT 1
FROM user_tables
WHERE table_name = UPPER(pa_table_name);

l_command VARCHAR2(50);

BEGIN
OPEN c_exists;
FETCH c_exists INTO l_dummy;
IF c_exists%FOUND THEN
--
-- Table exists in the schema of the user we're connected as
--
l_command := 'DROP TABLE '||UPPER(pa_table_name);
EXECUTE IMMEDIATE l_command;
END IF;
CLOSE c_exists;
END pr_dropit;
/

Assume you have a table called mytab that you want to drop/create. You can simply call this procedure in an anonymous PL/SQL block or just exec from SQL*Plus...

BEGIN
pr_dropit('mytab');
END;
/

If the table exists, it'll get dropped. If not, the procedure just won't do anything.
Obviously, if you're going to use this in a production environment you'll probably want to add some error handling etc, but I hope this is of some help.

Mike
Re: avoid error message when drop a non-existing table [message #434589 is a reply to message #434588] Wed, 09 December 2009 18:31 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>If the table exists, it'll get dropped.
Except when it is referenced by a Foreign Key.
Re: avoid error message when drop a non-existing table [message #434590 is a reply to message #434589] Wed, 09 December 2009 19:20 Go to previous messageGo to next message
mikesmithers
Messages: 2
Registered: December 2009
Location: United Kingdom
Junior Member
Perfectly correct.
In the scenario I'm thinking of (i.e. you're scripting the creation of several tables), you'll be creating/dropping them in the appropriate order.

Thus, rather than doing drop table then immediately creating it, you'd drop the tables in dependency order - i.e. children first then parent ( in RI terms). You would then have the next section of your script creating the tables in the opposite order ( parents first, then children) as, assuming you define the FKs on the children in the table creation script, you need to have the parents there before they get run to prevent the script falling over.

The same would apply to FK constraints applied to existing applications tables against the ones you're creating - i.e. you'll have to create the parent table before the FK, and you'd be dropping the FK before the parent table.

Furthermore, I'd assume that all of the DDL statements are being done before any DML, to avoid the possibility of stuff being "inadvertently" committed...and then having to unpick all of the DML changes manually instead of simply issuing a rollback.

Does that make more sense ?
Re: avoid error message when drop a non-existing table [message #434591 is a reply to message #434590] Wed, 09 December 2009 19:27 Go to previous message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>Does that make more sense ?
Whole thread is much ado about NOTHING.
Your code is a solution in search of a problem.
Just ignore the nag-o-gram when your issue DROP TABLE which did not exist.
Previous Topic: UTL_SMTP inline and attachment issue
Next Topic: where clause and execution plan
Goto Forum:
  


Current Time: Sun Sep 25 14:31:18 CDT 2016

Total time taken to generate the page: 0.11480 seconds