This discussion is archived
2 Replies Latest reply: Jul 26, 2013 7:49 AM by Frank Kulash RSS

Getting error fetch returns more than requested number of rows

GTS (DBA) Journeyer
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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.

Legend

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