Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL-02112 SELECT ... INTO returns too many rows --- how to use the SELECT_ERROR option??
Many thanks...I'll give that a try.
"TurkBear" <noone_at_nowhere.com> wrote in message
news:c2qm5tgt0vlubk5nova6dhla166s6l1mjl_at_4ax.com...
>
> In those cases you can use a subquery:
>
> select a,b from (select a,b,c from someplace order by c) where rownum < 2
INTO
> :....
>
>
> ( or something like that, I am away from my syntax cheat-sheet
>
>
> "Dave Sutton" <dpsutton_at_marchsystems.co.uk> wrote:
>
> >Thanks very much for this.
> >
> >I've been able to use this method on most of my queries but on some the
> >ordering is critical.
> >
> >Any other ideas??
> >
> >Dave
> >
> >"Mark Wagoner" <mwagoner_at_iac.net> wrote in message
> >news:93fk2o$17hr$1_at_genma.iac.net...
> >> Hi Dave,
> >>
> >> You could try adding AND ROWNUM < 2 so you only get one row. They
won't
be
> >> any particular order so, if there are more than, you can't tell which
one
> >> you will get.
> >>
> >> Mark
> >>
> >> "Dave Sutton" <dpsutton_at_marchsystems.co.uk> wrote in message
> >> news:93fg6q$pfu$1_at_newsreaderg1.core.theplanet.net...
> >> > Hi,
> >> >
> >> > Can anyone help me with this one?
> >> >
> >> > I'm getting the following error when executing one of my stored
procedures:
> >> >
> >> > SQL-02112 SELECT ... INTO returns too many rows
> >> >
> >> >
> >> > I just require the first record that is returned by the select
statement
and
> >> > wish to discard the rest.
> >> >
> >> > The Oracle documentation suggests the following work-around:
> >> >
> >> > Cause: A SELECT ... INTO statement returned more rows than can be
stored
in
> >> > the host variable provided.
> >> >
> >> > *****Use the precompiler option SELECT_ERROR=NO.******
> >> >
> >> >
> >> > However, I can't find any way of setting this option. I would prefer
to
set
> >> > the option in-line within the stored procedure but will settle for
anything
> >> > I can get!
> >> >
> >> >
> >> > Hope you can help.
> >> >
> >> > Thanks in advance.....
> >> >
> >> >
> >> > Dave Sutton
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
>
Received on Wed Jan 10 2001 - 04:11:07 CST