SQL-02112 SELECT ... INTO returns too many rows --- how to use the SELECT_ERROR option??

[Dave Sutton]

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

[Todd Barry]

>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.

Just add on the ROWNUM qualifier:

select col1
into var1
from table1
where col2 = something
and rownum = 1;

-Todd

[Dave Sutton]

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 and the ROWNUM kicks in before the ordering is taken
into account.

Any further ideas??

Dave

"Todd Barry" <todd...@earthlink.net> wrote in message
news:qglm5tge3nc0oipoi...@4ax.com...

[sig...@my-deja.com]

A good solution but it might still cause a ora-01403, no data found,
error. This means you have to handle that exception.
Another way is to declare a cursor with an 'order by' clause and then
fetch this cursor. A bit more work but if you make this a standard way
of working you'll get used to it quite fast.

regards,
BertJan Meinders

In article <qglm5tge3nc0oipoi...@4ax.com>,

Sent via Deja.com
http://www.deja.com/

[Dave Sutton]

Thanks for the info guys....I take it there is no way to set precompiler
options inline within a PL\SQL block.

Dave

<sig...@my-deja.com> wrote in message news:93hbdr$cgd$1...@nnrp1.deja.com...

[Todd Barry]

>I've been able to use this method on most of my queries but on some the
>ordering is critical and the ROWNUM kicks in before the ordering is taken
>into account.
>
>Any further ideas??

If the order is important, then you will need to do a cursor
open/single fetch/close sequence to get the single value for which you
are looking.

-Todd

[David Fitzjarrell]

In our last gripping episode "Dave Sutton"

Precompiler options apply only to the PRO* series of precompilers. YOu
can set these options should you be coding in C/C++, FORTRAN, COBOL,
ADA, etc. and utilize the appropriate PRO* precompiler. I am not
certain which precompilers are still available or supported, but I do
know that the PRO*C/C++ precompiler is alive and well.

-
David Fitzjarrell
Oracle Certified DBA

[Greg Stark]

"Dave Sutton" <dpsu...@marchsystems.co.uk> writes:

> 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 and the ROWNUM kicks in before the ordering is taken
> into account.
>
> Any further ideas??

in 8i you can do select * from (select ... order by ...) where rownum == 1;

In 8.0.5 there's no way unless you're willing to count on the ordering that
the query plan guarantees. Specifically, if you can ensure the query is doing
an index range scan then you can be sure you're getting the first record in
that range. That's not unlikely given that rownum normally kicks Oracle into
first_rows mode so merge joins et al aren't common.

--
greg