469,338 Members | 8,076 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

Does an "IF Exists" clause exist in Oracle?

127 100+
Hello All,

I remember in MySQL we do have "IF EXISTS", 'IF NOT EXISTS" clause/keyword in the DDL statements (CREATE,DROP etc.,) to avoid unnecessary bombing.

Do we have such facility in Oracle? If not is there any other easy way to achieve it? I am using Oracle 10g. I have been searching through google and also in oracle.com but unable to get it out.

Thanks,
Raghavan alias Saravanan M.
Nov 19 '08 #1
7 58864
Pilgrim333
127 100+
Hi,

In Oracle you can use exists in the where clause. Other options are the IN and NOT IN in the where clause.

You can find more info on this topic over here conditions

Pilgrim
Nov 19 '08 #2
amitpatel66
2,367 Expert 2GB
Hi,

In Oracle you can use exists in the where clause. Other options are the IN and NOT IN in the where clause.

You can find more info on this topic over here conditions

Pilgrim

One CANNOT USE EXISTS and IN and NOT IN in DDL statements (CREATE, DROP)

@OP,

If you want to check whether the particular object exist or not before creating the new one or before dropping the old one, then you can do so using a PLSQL way. Just check the sample code below:

Expand|Select|Wrap|Line Numbers
  1. declare
  2. CURSOR C1 is SELECT table_name FROM all_tables where table_name = 'EMP';
  3. BEGIN
  4. FOR I IN c1 LOOP
  5. EXECUTE IMMEDIATE 'DROP TABLE '||I.table_name;
  6. END LOOP;
  7. --After dropping all the tables you can recreate then like this:
  8. EXECUTE IMMEDIATE 'CREATE TABLE EMP(emp_no NUMBER,empname VARCHAR2(100),salary NUMBER,hire_date DATE,deptno NUMBER)';
  9. END;
  10.  
Nov 19 '08 #3
itsraghz
127 100+
Thank you Pilgrim33 and amitpatel for having replied immediately.

I do agree with the alternatives for "really checking" before you do with your DDL statements. But it should be at the cost of this quite-lengthy-and-expensive operation?

In MySQL, we have,

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE IF EXISTS MyTable (..);
Don't we have a similar-and-easy way to accomplish the same in Oracle?

Thanks,
Raghavan alias Saravanan M.
Nov 19 '08 #4
amitpatel66
2,367 Expert 2GB
Which oracle version you are using?

And are you going to us DDL statement manually or from any other program?
Nov 19 '08 #5
Pilgrim333
127 100+
Oooops, replied a bit too soon, without reading, thought OP meant the SQL version of IF EXISTS.

But anyhoo Raghavan, you can't accomplish it with a statement, you'll have to write some pl/sql code. And writing the code takes you more time then manually checking if the table exists and creating a new table.

Pilgrim.
Nov 19 '08 #6
itsraghz
127 100+
Which oracle version you are using?

And are you going to us DDL statement manually or from any other program?
Thanks amit. It was for writing a SQL Script when setting up the new module for the existing database instance. There are times/situations wherein we need to atler the existing tables according to the new module. That's the reason I just had a thought compared to what MySQL gives this facility.
Nov 19 '08 #7
itsraghz
127 100+
Oooops, replied a bit too soon, without reading, thought OP meant the SQL version of IF EXISTS.

But anyhoo Raghavan, you can't accomplish it with a statement, you'll have to write some pl/sql code. And writing the code takes you more time then manually checking if the table exists and creating a new table.

Pilgrim.
Thank you very much Pilgrim.

You are absolutley right and I do agree with the complexity its gonna take than the manual verification. Anyways, I just have to go with the plain, non-conditional version of sql script by bearing the overheads.
Nov 19 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Yansky | last post: by
3 posts views Thread by moshe koren | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.