ORA-01422: exact fetch returns more.........

[hemanr]

Hi Gurus,

I get the error message

ORA-01422: exact fetch returns more than requested number of rows

on the following proc...

CREATE OR REPLACE procedure test_dyn2(
v_id_in in number) is
cursor c100 is
select id,quote_id from test_dyn;
v_id number;
v_quote_id varchar2(50);
begin
for currec in c100
loop
if v_id_in > currec.id then
insert into test_dyn_hold(id,quote_id) select id,
quote_id from test_dyn where id > v_id_in;
end if;
end loop;
end;
/


I need to populate the test_dyn_hold table with all the records from test_dyn where the id > IN parameter id.

How would I do this???

Thanks!

[pgoenka]

simple your select clause in the insert statement is returning more then one rows ..just check!!!

[Cookies]

your logic is somewhat flawed. try this:

CREATE OR REPLACE procedure test_dyn2(
v_id_in IN number
)
IS

v_id number;
v_quote_id varchar2(50);

cursor c100 is
select id,
quote_id
from test_dyn
where id > v_id_in;

BEGIN

OPEN C100;
FETCH C100 into v_id, v_quote_id;
WHILE C100%found loop

insert into test_dyn_hold(
id,
quote_id )
values (
v_id,
v_quote_id );

FETCH C100 into v_id, v_quote_id;
END loop;
CLOSE C100;
commit;
END;
/


*** let me know if that works ***

[ngdh]

Originally posted by pgoenka
simple your select clause in the insert statement is returning more then one rows ..just check!!!

Hi folks,

I presume this is data population is regulary occurence otherwise a simple bulk insert would do as you have already coded in your loop.

I agree that the logic may be flawed in that the insert would run for every ocurrence of "test_dyn" which I would produce duplicates in teh target table. However it is perfectly valid to have a multi row select supplying data to an insert.

Test

Using test_user (subset of dba_users as too bone idle to create test data ;D ) and test_user2 (empty)

CREATE OR REPLACE procedure
test_dyn2(v_id_in in number) is
cursor c100 is
select user_id, username from test_user;
v_user_id number;
v_username varchar2(30);
BEGIN
for currec in c100
LOOP
if v_id_in > currec.user_id then
insert into test_user2(user_id, username) select user_id,
username from test_user where user_id > v_id_in;
end if;
end loop;
end;
/
ACDEV>select count(1) from test_users
2 where user_id > 10;

COUNT(1)
----------
21


ACDEV>select count(1) from test_user2;

COUNT(1)
----------
0

ACDEV>exec test_dyn2(10);

PL/SQL procedure successfully completed.

ACDEV>select count(1) from test_user2;

COUNT(1)
----------
63

I must be missing something as I can't see any differences between your procedure and mine. It would work although the results are unlikely to be what you want.

Cheers

Nick

[jmodic]

Originally posted by ngdh
I must be missing something ....

Me too!

I can't see any single line of code in the original post that would cause "ORA-01422: exact fetch returns more than requested number of rows."

Hemanr, can you copy-paste the axact error message stack that you got when you run your procedure in SQL*Plus session? because I'm sure it is not the procedure it is raising that excaeption - it must be some insert trigger on test_dyn_hold (or something like this) that is causing this error.