Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Using IF statements in Oracle

Re: Using IF statements in Oracle

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 12 Sep 2003 17:14:31 -0700
Message-ID: <1063412057.109008@yasure>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US