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

From: Ken Denny <ken_at_kendenny.com>
Date: Fri, 07 Jun 2002 20:02:36 GMT
Message-ID: <Xns9226A57153AAFkendenny@65.82.44.7>

Daniel Morgan <dmorgan_at_exesolutions.com> wrote in news:3D00D94A.761CC389_at_exesolutions.com:

> Matthieu DEGLIN wrote:

>>
>> Here are the informations to help you help me solve the error :
>>
>> DECLARE
>> *
>> ERROR at line 1:
>> ORA-01422: exact fetch returns more than requested number of rows
>> ORA-06512: at line 77
>>
>> That error happens when executing a bloc of the following type via
>> SQLPLUS under Oracle 8:
>>
>> DECLARE
>>
>> CURSOR My_CUR IS
>> SELECT My_code, my_date, rowid
>> FROM My_TABLE;
>>
>> R_My_Cur My_CUR%ROWTYPE;
>>
>> BEGIN
>> OPEN My_CUR
>>
>> LOOP
>> FETCH My_CUR INTO R_my_CUR;
>> EXIT WHEN My_CUR%NOTFOUND;
>>
>> ...[Process my_Code and update it if necessary ]
>>
>> END LOOP;
>> CLOSE My_CUR;
>> commit;
>> END;
>>
>> So Here is my question again:
>>
>> 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

> 
> This is quite unusual. Do you have any parallel processing going on?
> 
> Does the above example cause the problem or only a version with a more
> complex cursor and code that you have snipped?
> 
> Have you attempted to isolate the exact line that, when executed,
> causes the error? I am thinking that what you snipped and the fact that
> your cursor definition is not the one you are really using may be
> hiding the actual culprit.
> 
> Daniel Morgan

Like Daniel said, the code you show here should have no problem that could cause that error. You probably were drawn to this code because of the word "fetch". It may not be an actual "fetch" statement which is causing the problem. This problem is most often caused by a statement of the form

SELECT column INTO variable FROM table WHERE ...

and there is more than one row in the table that satisfies the "where" clause.

-- 
Ken Denny
http://www.kendenny.com/

Experience is something you don't get until just after you need it.

Received on Fri Jun 07 2002 - 15:02:36 CDT