Does an "IF Exists" clause exist in Oracle?

[itsraghz]

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.

[Pilgrim333]

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

[amitpatel66]

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:

declare
CURSOR C1 is SELECT table_name FROM all_tables where table_name = 'EMP';
BEGIN
FOR I IN c1 LOOP
EXECUTE IMMEDIATE 'DROP TABLE '||I.table_name;
END LOOP;
--After dropping all the tables you can recreate then like this:
EXECUTE IMMEDIATE 'CREATE TABLE EMP(emp_no NUMBER,empname VARCHAR2(100),salary NUMBER,hire_date DATE,deptno NUMBER)';
END;
 

[itsraghz]

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,

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.

[amitpatel66]

Which oracle version you are using?

And are you going to us DDL statement manually or from any other program?

[Pilgrim333]

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.

[itsraghz]

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.

[itsraghz]

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.