2 Replies Latest reply: Jul 26, 2013 9:49 AM by Frank Kulash RSS

    Getting error fetch returns more than requested number of rows

    GTS (DBA)

      Good Morning  SQL/PLSQL  Experts;

       

      I have written sample program for  PL/SQL  records.

      without cursors  - how can finish my error query (please see 2nd  program)

       

      SQL> DECLARE
        2  val_disp customers%rowtype;
        3  BEGIN
        4  select * into val_disp from customers where no=100;
        5  DBMS_OUTPUT.PUT_LINE(val_disp.no);
        6  DBMS_OUTPUT.PUT_LINE(val_disp.name);
        7  DBMS_OUTPUT.PUT_LINE(val_disp.mobile);
        8  DBMS_OUTPUT.PUT_LINE(val_disp.city);
        9  END;
      10  /
      100
      
      

       

      PL/SQL procedure successfully completed.

       

      Why I am getting an error here


      customers table  having 5 records.

       

      SQL> DECLARE
      2 val_disp customers%rowtype;
      3 i number :=1;
      4 BEGIN
      5 for i in 1..5 loop
      6 select * into val_disp from customers;
      7 DBMS_OUTPUT.PUT_LINE(val_disp.no);
      8 DBMS_OUTPUT.PUT_LINE(val_disp.name);
      9 DBMS_OUTPUT.PUT_LINE(val_disp.mobile);
      10 DBMS_OUTPUT.PUT_LINE(val_disp.city);
      11 end loop;
      12 END;
      13 /
      
      

      DECLARE

      *

      ERROR at line 1:

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

      ORA-06512: at line 6

       

      Thanks All

      please answer  this question  and clear my doubt.

        • 2. Re: Getting error fetch returns more than requested number of rows
          Frank Kulash

          Hi,

           

          Whenever you have a question, please post a compelte test script that the people who want to help you can run to re-create the problem and test their ideas.  If you can't show what the problem is using commonly available tables (such as those in the scott schema), then include CREATE TABLE and INSERT statements for your own tables.

          No matter where the input data comes from, post the output you want from that data.

          Always say which version of Oracle you're using (e.g. 11.2.0.2.0).

           

          See the forum FAQ: https://forums.oracle.com/message/9362002

           

          Do you really need to use PL/SQL?  If so, explain why.

          Oteherwise, just run your query

           

          select  *

          from    customers

          where   no  = 100;

           

          in SQL*Plus, or any other front end.

          If you really, really must do this in PL/SQL, here's a simple way:

           

          BEGIN

              FOR  d  IN (

                             SELECT  *

                             FROM    scott.dept

                             WHERE   deptno  > 10

                         )

              LOOP

                 dbms_output.put_line (d.dname); -- or whatever you need to do

              END LOOP;

          END;

          /

           

          The cursor d is defined in the FOR statement itself; you don't need a separate declaration.  In fact, if you do have a separate declaration, it will create a separate cursor, which will not be used in the FOR ... LOOP.

          This will be much slower than simply using SQL.