ORA-01422: exact fetch returns more.........
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

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

  1. #1
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262

    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

  2. #2
    Join Date
    Aug 2002
    Posts
    35
    simple your select clause in the insert statement is returning more then one rows ..just check!!!

  3. #3
    Join Date
    Oct 2002
    Posts
    182
    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

  4. #4
    Join Date
    May 2002
    Location
    Western Australia
    Posts
    233
    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

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  


Click Here to Expand Forum to Full Width