visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: Oracle :: "if not exists" SQL statement in Oracle ?
By: tiscaliBE Belgium  Date: 30/12/2004 12:57:36  English  Points: 20 Status: Answered
Quality : Excellent
Hi,

How do I execute these queries in Oracle ?
(I'm using 8i and 9i, if this matters)

create table if not exists table_name (val int);
drop table if exists table_name;

They give "ORA-00922: missing or invalid option" and "ORA-00933: SQL command not properly ended" errors respectively.

Thanks.
By: VGR Date: 30/12/2004 13:04:44 English  Type : Answer
three solutions, equally dirty :

1) just drop the table, if it doesn't exist you won't be able to delete it ; you may receive an error or exception, though, so you've to handle it cleanly

2) select count(*) as nb from dba_tables where table_name='my_table'
or
select count(*) as nb from all_tables where table_name='my_table' and owner='you'
PS : the "AS" keyword, althought standard SQL, may be refused by Oracle ;-)

3) use a stored procedure :
execution :
@create-table.sql

definition :
DECLARE
t_name VARCHAR2 (100);
BEGIN
SELECT table_name
INTO t_name
FROM dba_tables
WHERE UPPER (table_name) = 'your_table_name';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXECUTE IMMEDIATE ('CREATE TABLE your_table_name(definition)');
END;
/

You may even create the indices, this way.

An other dirty workaround for clumsy Oracle compared to the powerful MySql database ;-))
By: tiscaliBE Date: 03/01/2005 09:05:06 English  Type : Comment
ok. I used the first method and ignored any error ;-)

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 293.001890 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page