-
ORA-01422: exact fetch returns more.........
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!
Hemant
-
simple your select clause in the insert statement is returning more then one rows ..just check!!!
-
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 ***
- Cookies
-
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
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|