7 Replies Latest reply: Jan 16, 2013 12:58 AM by 976410 RSS

    Strange Issue

    976410
      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
          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
            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
              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
                Why this issue not came in past ?
                • 5. Re: Strange Issue
                  976410
                  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
                    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
                      Thanks You Very Much. Now i understand full story. Thanks again.