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: Works in SQL*Plus but not in PL/SQL

Re: Works in SQL*Plus but not in PL/SQL

From: Bill Hoernke <bhoernke_at_gartonworks.com>
Date: 5 Jun 2002 21:48:33 -0700
Message-ID: <6abceec.0206052048.7baaa637@posting.google.com>


Richard Kuhler <noone_at_nowhere.com> wrote in message news:<e5wL8.48603$R53.17239105_at_twister.socal.rr.com>...
> There are many places that subqueries aren't allowed in PL/SQL prior to
> 9i. In SQL Functions or as a select clause come to mind. In your case,
> you should be able to change that query to ...
>
> update rh_1 rp
> set gid =
> (select nvl(min(rp2.gid), rp.gid)
> from rh_1 rp1, rh_1 rp2
> where rp.gid = rp1.gid
> and rp1.dl = rp2.dl
> );
>
>
> Richard
>
> Bill Hoernke wrote:
> >
> > I have searched the group and found some similar things but I haven't
> > solved my problem yet. Any help would be appreciated.
> >
> > This update statement works in SQL*Plus, but it doesn't work when I
> > try to compile it in a PL/SQL Package.
> >
> > update rh_1 rp
> > set gid =
> > nvl((select min(rp2.gid)
> > from rh_1 rp1, rh_1 rp2
> > where rp.gid = rp1.gid
> > and rp1.dl = rp2.dl
> > ),gid);
> >
> > I get PLS-00103: Encountered the symbol "SELECT" when expecting one of
> > the following: ( - + ....
> >
> > Thanks,
> > Bill Hoernke
> > GartonWorks, Inc.

Thank you. Your suggestion works great. Received on Wed Jun 05 2002 - 23:48:33 CDT

Original text of this message

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