Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Using IF statements in Oracle
KPK wrote:
>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?
>
>
You can not do DDL in PL/SQL except as dynamic SQL.
But more imporantly ... you should not be attempting this. And even if you had some compelling reason to do it the commit work is meaningless other than to tell me that you learned SQL in some other product like SQL Server and thought that you could bluff your way into Oracle without bothering to read a book or web site, or take a class.
So, like so many others that have come before you, you can continue down this path pouring gasoline on things and then eventually blaming Oracle for the disaster you are making or you can stop this foolishness and learn Oracle.
-- Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damorgan_at_x.washington.edu (replace 'x' with a 'u' to reply)Received on Fri Sep 12 2003 - 19:14:31 CDT