Re: How can I specify more rows in the exact fetch ?

"Matthieu DEGLIN" <mdeglin_at_remove-me.partner.auchan.com> wrote in message news:<adqfve$40c$1_at_reader1.imaginet.fr>...
> Hello,
>
> How can I specify more rows in the exact fetch ?
> ( It's the way given by the Oracle documentation to solve an ORA-01422)
>
> Thanxs.
>
> Matthieu DEGLIN

Matthieu, in English the documentation is telling you to rewrite your query or change the number of rows requested.

You did not post the SQL but I would expect an exact fetch error on a select into, a subquery where the relational operator is equal, or a column select where mutilple rows were returned.

A select into is an exact fetch and as such must return one and only one row. If you expect more than one row you need to use a cursor, exception logic, or perhaps 'and rownum = 1' where the first row returned will do.

Using pl/sql
begin
select col1 into variable from table_name where some_condition; exception
  when no_data_found then

       logic for missing data
  when too_many_rows then

       logic for multiple rows/duplicates end;

OR
select col1 into variable from table_name where some_condition and rownum = 1;

Another possiblility is that you failed to provide the full key for a multi-column primary or unique key and got back more rows than you expected. Naturally this condition would be fixed by providing the missing columns.

Oh, yes from oerr
$ oerr ora 01422
01422, 00000, "exact fetch returns more than requested number of rows" // *Cause: The number specified in exact fetch is less than the rows returned.
// *Action: Rewrite the query or change number of rows requested

HTH -- Mark D Powell-- Received on Fri Jun 07 2002 - 15:18:18 CDT