468,133 Members | 1,420 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,133 developers. It's quick & easy.

Cursor Error

75
---When I run following code

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3.       v_evct   NUMBER(20);
  4.       v_evcd    NUMBER(20);
  5.       v_class      VARCHAR2(100);
  6.        cursor c1 is select * from input_info;
  7. BEGIN
  8.       FOR r1 in c1
  9.       LOOP
  10.           SELECT sum(EV_COST),EV_ID,EV_ATTR   INTO
  11.           v_evct,v_evcd,v_class                   
  12.             FROM cost_test GROUP BY EV_ID,EV_ATTR  ;
  13.      END LOOP;
  14. END ;
  15.  
  16.  
--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
Nov 16 '07 #1
11 2510
amitpatel66
2,367 Expert 2GB
---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
Nov 16 '07 #2
amitpatel66
2,367 Expert 2GB
---When I run following code

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3.       v_evct   NUMBER(20);
  4.       v_evcd    NUMBER(20);
  5.       v_class      VARCHAR2(100);
  6.        cursor c1 is select * from input_info;
  7. BEGIN
  8.       FOR r1 in c1
  9.       LOOP
  10.           SELECT sum(EV_COST),EV_ID,EV_ATTR   INTO
  11.           v_evct,v_evcd,v_class                   
  12.             FROM cost_test GROUP BY EV_ID,EV_ATTR  ;
  13.      END LOOP;
  14. END ;
  15.  
  16.  
--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?
Nov 16 '07 #3
orajit
75
Thanks for ur reply

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2.       v_evct   NUMBER(20);
  3.       v_evcd    NUMBER(20);
  4.       v_class      VARCHAR2(100);
  5.        CURSOR c1 IS SELECT * FROM input_info;
  6. BEGIN
  7.       FOR r1 IN c1
  8.       LOOP
  9.           SELECT SUM(EV_COST),EV_ID,EV_ATTR   INTO
  10.           v_evct,v_evcd,v_class                   
  11.             FROM cost_test
  12.              Where accnum=r1.acc_num  GROUP BY EV_ID,EV_ATTR  ;
  13.      END LOOP;
  14. END ;
  15.  
still m getting that error any solution
Nov 16 '07 #4
orajit
75
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
Expand|Select|Wrap|Line Numbers
  1. SQL> declare 
  2.   2    v_empno number;
  3.   3    cursor c1 is select * from emp;
  4.   4  begin 
  5.   5    for r1 in c1
  6.   6     loop 
  7.   7      select empno into v_empno from emp where ename=r1.ename;
  8.   8  end loop;
  9.   9  end;
  10.  10  /
  11.  
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 7
Nov 16 '07 #5
amitpatel66
2,367 Expert 2GB
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
Expand|Select|Wrap|Line Numbers
  1. SQL> declare 
  2.   2    v_empno number;
  3.   3    cursor c1 is select * from emp;
  4.   4  begin 
  5.   5    for r1 in c1
  6.   6     loop 
  7.   7      select empno into v_empno from emp where ename=r1.ename;
  8.   8  end loop;
  9.   9  end;
  10.  10  /
  11.  
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.
Nov 16 '07 #6
orajit
75
[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
Nov 19 '07 #7
amitpatel66
2,367 Expert 2GB
Threads merged for better management of the forum

MODERATOR
Nov 19 '07 #8
orajit
75
cud you please send me solution for above code ...m still not able to solve the above error and need your help for that ,,
Nov 19 '07 #9
amitpatel66
2,367 Expert 2GB
[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!!
Nov 19 '07 #10
orajit
75
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 ..
Nov 19 '07 #11
amitpatel66
2,367 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE 
  3.     TYPE emp_no IS VARRAY(100) OF emp.empno%TYPE;
  4.     v_empno emp_no;
  5.     CURSOR c1 IS SELECT * FROM emp;
  6.   BEGIN 
  7.      SELECT empno BULK COLLECT INTO v_empno FROM emp WHERE ename=r1.ename;
  8. FOR I IN v_empno.FIRST..v_empno.LAST LOOP
  9. DBMS_OUTPUT.PUT_LINE(v_empno);
  10. END LOOP;
  11.   END;
  12.  
  13.  
Nov 19 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Matthew Houseman | last post: by
3 posts views Thread by robert | last post: by
4 posts views Thread by Oliver Stratmann | last post: by
reply views Thread by Prashanth | last post: by
2 posts views Thread by dave | last post: by
1 post views Thread by Søren Larsen | last post: by
2 posts views Thread by Florian Lindner | last post: by
27 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.