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

Quote:

Originally Posted by 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

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

 
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,

Expand|Select|Wrap|Line Numbers

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

Quote:

Originally Posted by amitpatel66

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

Quote:

Originally Posted by 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.

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.

Does an "IF Exists" clause exist in Oracle?

re: Does an "IF Exists" clause exist in Oracle?

re: Does an "IF Exists" clause exist in Oracle?

re: Does an "IF Exists" clause exist in Oracle?

re: Does an "IF Exists" clause exist in Oracle?

re: Does an "IF Exists" clause exist in Oracle?

re: Does an "IF Exists" clause exist in Oracle?

re: Does an "IF Exists" clause exist in Oracle?

Similar Oracle Database bytes

/bytes/development

/bytes/it

/bytes/about