Cursor Error

[orajit]

---When I run following code

 
DECLARE
      v_evct   NUMBER(20);
      v_evcd    NUMBER(20);
      v_class      VARCHAR2(100);
       cursor c1 is select * from input_info;
BEGIN
      FOR r1 in c1
      LOOP
          SELECT sum(EV_COST),EV_ID,EV_ATTR   INTO
          v_evct,v_evcd,v_class                   
            FROM cost_test GROUP BY EV_ID,EV_ATTR  ;
     END LOOP;
END ;
 
 

--I got this error
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 9

--cud u please tell me how to avoid this above error

[amitpatel66]

---When I run following code

DECLARE
v_evct NUMBER(20);
v_evcd NUMBER(20);
v_class VARCHAR2(100);
cursor c1 is select * from input_info;
BEGIN
FOR r1 in c1
LOOP
SELECT sum(EV_COST),EV_ID,EV_ATTR INTO
v_evct,v_evcd,v_class
FROM cost_test GROUP BY EV_ID,EV_ATTR ;
END LOOP;
END ;

--I got this error
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 9

--cud u please tell me how to avoid this above error

Please enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

I remember this is third time I am doing it for you and you have been already informed before to use CODE tags.

MODERATOR

[amitpatel66]

---When I run following code

 
DECLARE
      v_evct   NUMBER(20);
      v_evcd    NUMBER(20);
      v_class      VARCHAR2(100);
       cursor c1 is select * from input_info;
BEGIN
      FOR r1 in c1
      LOOP
          SELECT sum(EV_COST),EV_ID,EV_ATTR   INTO
          v_evct,v_evcd,v_class                   
            FROM cost_test GROUP BY EV_ID,EV_ATTR  ;
     END LOOP;
END ;
 
 

--I got this error
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 9

--cud u please tell me how to avoid this above error

This error is becuase your SELECT query inside the loop is returning more than one row.

one more thing i notice is that you are using a FOR LOOP CURSOR, but not using any of the cursor values with in the loop then why you need to use a CURSOR here?

[orajit]

Thanks for ur reply

DECLARE
      v_evct   NUMBER(20);
      v_evcd    NUMBER(20);
      v_class      VARCHAR2(100);
       CURSOR c1 IS SELECT * FROM input_info;
BEGIN
      FOR r1 IN c1
      LOOP
          SELECT SUM(EV_COST),EV_ID,EV_ATTR   INTO
          v_evct,v_evcd,v_class                   
            FROM cost_test
             Where accnum=r1.acc_num  GROUP BY EV_ID,EV_ATTR  ;
     END LOOP;
END ;
 

still m getting that error any solution

[orajit]

I have tried that code for empno
still m getting that error ... cud u plz tell me how to avoid this error to get desired output ....

Thanks

SQL> declare 
  2    v_empno number;
  3    cursor c1 is select * from emp;
  4  begin 
  5    for r1 in c1
  6     loop 
  7      select empno into v_empno from emp where ename=r1.ename;
  8  end loop;
  9  end;
 10  /
 

declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 7

[amitpatel66]

I have tried that code for empno
still m getting that error ... cud u plz tell me how to avoid this error to get desired output ....

Thanks

SQL> declare 
  2    v_empno number;
  3    cursor c1 is select * from emp;
  4  begin 
  5    for r1 in c1
  6     loop 
  7      select empno into v_empno from emp where ename=r1.ename;
  8  end loop;
  9  end;
 10  /
 

declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 7

Make use of primary key column in the where condition.
ename cannot be a primary key becuase many peoples can have same name.
Using empno in WHERE column will solve the error.

[orajit]

[HTML]
DECLARE
v_empno NUMBER;
CURSOR c1 IS SELECT * FROM emp;
BEGIN
FOR r1 IN c1
LOOP
SELECT empno INTO v_empno FROM emp WHERE ename=r1.ename;
END LOOP;
END;[/HTML]

when I execute this above code i am getting following error...
ORA-01422: exact fetch returns more than requested number of rows

cud you please tell how to execute above code... any alternative code for above code..

Thanks

[amitpatel66]

Threads merged for better management of the forum

MODERATOR

[orajit]

cud you please send me solution for above code ...m still not able to solve the above error and need your help for that ,,

[amitpatel66]

[HTML]
DECLARE
v_empno NUMBER;
CURSOR c1 IS SELECT * FROM emp;
BEGIN
FOR r1 IN c1
LOOP
SELECT empno INTO v_empno FROM emp WHERE ename=r1.ename;
END LOOP;
END;[/HTML]

when I execute this above code i am getting following error...
ORA-01422: exact fetch returns more than requested number of rows

cud you please tell how to execute above code... any alternative code for above code..

Thanks

If you look at the prevoius POSTS I have asked to use empno which is a primary key column in WHERE clause will resolve your issue.

Please try that and POST back in case of any further issues!!

[orajit]

Thanaks for ur answer.....But i dont want to use primary key for empno .. In my emp table empno has repeated values and if i used the primary key constraint it wont give me priveleges to do that ...

Could u please tell me whitout using primary key on empno (repeated values) how could I do this ..

[amitpatel66]

Thanaks for ur answer.....But i dont want to use primary key for empno .. In my emp table empno has repeated values and if i used the primary key constraint it wont give me priveleges to do that ...

Could u please tell me whitout using primary key on empno (repeated values) how could I do this ..

Try below code:

 
DECLARE 
    TYPE emp_no IS VARRAY(100) OF emp.empno%TYPE;
    v_empno emp_no;
    CURSOR c1 IS SELECT * FROM emp;
  BEGIN 
     SELECT empno BULK COLLECT INTO v_empno FROM emp WHERE ename=r1.ename;
FOR I IN v_empno.FIRST..v_empno.LAST LOOP
DBMS_OUTPUT.PUT_LINE(v_empno);
END LOOP;
  END;