Re: Using IF statements in Oracle

Originally posted by Kpk

> Hello: I am trying to write a script that will alter some schema in

> an Oracle 9i database. The following is a sample of my script:

>

> declare lngNumItems number(7,0);

>

> begin

> lngNumItems := 0;

> Select count(*) into lngNumItems from gasanalysistb;

>

> if (lngNumItems > 0) then

> alter table gasanalysistb add mycol number(3,0) default
> 100;

> commit work;

> end if;

> end;

>

> Oracle gives me the following error when I run this:

>

> declare lngNumItems number(7,0);

>

> begin

> lngNumItems := 0;

> Select count(*) into lngNumItems from gasanalysistb;

>

> if (lngNumItems > 0) then

> alter table gasanalysistb add mycol number(3,0) default
> 100;

> commit work;

> end if;

> end;

>

> ORA-06550: line 8, column 4:

> PLS-00103: Encountered the symbol "ALTER" when expecting one of the

> following:

>

> begin case declare exit for goto if loop mod null pragma

> raise return select update while with <an identifier>

> <a double-quoted delimited-identifier> <a bind variable> <<

> close current delete fetch lock insert open rollback

> savepoint set sql execute commit forall merge

> <a single-quoted SQL string> pipe

>

> What am I doing wrong?

It looks like you are from Sql server world.

First of all, there has to be a very good reason to run DDL's (Data Definition Language) inside PLSQL because it is avoided under normal circumstances.

Secondly, to run DDL inside PLSQL, you use dynamic sql via Oracle supplied package called dbms_sql. Learn about it at http://tahiti.oracle.com

Regards

/Rauf

--
Posted via http://dbforums.com