This discussion is archived
7 Replies Latest reply: Jan 15, 2013 10:58 PM by 976410 RSS

Strange Issue

976410 Newbie
Currently Being Moderated
Hi,

I have a function in oracle giving
ORA-01422: exact fetch returns more than requested number of rows
error.

But when i ran the sql used in function manually it is returing only one row.

My Function is as follows :

FUNCTION getCurrentInstanceReference(src_sys_ident_ref in VARCHAR2, rit_id in NUMBER, srcs_id in NUMBER) RETURN VARCHAR2
IS
current_rii_id VARCHAR2(100);
BEGIN
IF (rit_id = 4)
THEN
RETURN '';
END IF;

select inst_ref
into current_rii_id
from risk_identifier_now rin
where rin.source_sys_ident_item_ref = src_sys_ident_ref
and rin.rit_id = rit_id
and rin.srcs_id = srcs_id;
RETURN current_rii_id;
END getCurrentInstanceReference;

select getCurrentInstanceReference('AIM-00005',280,10260) from dual;
ORA-01422: exact fetch returns more than requested number of rows

But when i ran the query used in function manually it is returning only one row.

select rin.inst_ref
from gor.risk_identifier_now rin
where rin.source_sys_ident_item_ref = 'AIM-00005'
and trim(rin.rit_id) = 280
and rin.srcs_id = 10260;

When i crosscheck i found that function checking only one condition i.e where rin.source_sys_ident_item_ref = src_sys_ident_ref not all why ??

Please assist.

Thanks in Advance
Jagat

Edited by: 973407 on 15-Jan-2013 22:01
  • 1. Re: Strange Issue
    Marwim Expert
    Currently Being Moderated
    Hello Jagat,

    this is the forum for the tool {forum:id=260}. Your question should be asked at {forum:id=75}

    Regards
    Marcus
  • 2. Re: Strange Issue
    yoonas Expert
    Currently Being Moderated
    Hi,


    >
    and rin.rit_id = rit_id
    and rin.srcs_id = srcs_id
    >

    this will match the same column value, change the parameter name to something like(p_rit_id) and try.

    >
    source_sys_ident_item_ref = src_sys_ident_ref
    >

    Here you are not using column name as it is, thats the reason

    Regards
    Yoonas
  • 3. Re: Strange Issue
    976410 Newbie
    Currently Being Moderated
    Thanks Yoonas, Its solved the Problem.

    But i am not sure why just sudden we got this issue. This function is there from last 3 years and never we got any issue. Please advice.

    Thanks
    Jagat

    Edited by: 973407 on 15-Jan-2013 22:51
  • 4. Re: Strange Issue
    976410 Newbie
    Currently Being Moderated
    Why this issue not came in past ?
  • 5. Re: Strange Issue
    976410 Newbie
    Currently Being Moderated
    Thanks Yoonas, Its solved the Problem.

    But i am not sure why just sudden we got this issue. This function is there from last 3 years and never we got any issue. Please advice.

    Thanks
    Jagat

    Edited by: 973407 on 15-Jan-2013 22:52
  • 6. Re: Strange Issue
    yoonas Expert
    Currently Being Moderated
    may be the 'field source_sys_ident_item_ref' has been unique sofar, but now it got a duplicate. Querying with source_sys_ident_item_ref itself was giving only one record but now thats not the case.

    That could be the reason you did not realise it yet. As long it runs fine we tend to think everything is fine :)

    If someones answer is correct or helpful, please mark it accordingly
  • 7. Re: Strange Issue
    976410 Newbie
    Currently Being Moderated
    Thanks You Very Much. Now i understand full story. Thanks again.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points