CODEWITHSUNDEEP
plsql
Ali
Ali

Reputation: 3

Using cursor in procedure

Why am I getting the error below and please tell me what's wrong in the code. ORA-06511: PL/SQL: cursor already open

Code :

create or replace procedure student_info IS  
c_id student_details.student_id%type  ;      
c_name student_details.student_name%type;  
c_status student_details.student_status%type;

cursor stu_c1 IS  
select *  from student_details  
where student_status='Absent';  
begin  
open stu_c1;  
Fetch stu_c1 into c_id,c_name,c_status ;  
for rec in stu_c1 loop  
insert into student_data values (c_status,c_id,c_name);  
commit;  
end loop;   
CLOSE stu_c1;    
end;

Upvotes: 0

Views: 76

Answers (1)

a1ex07
a1ex07

Reputation: 37382

If you are using FOR loop with explicit cursor, you don't need open/fetch/close/handle NOTFOUND. 

...
begin  
--open stu_c1;   -- extra, not needed
--Fetch stu_c1 into c_id,c_name,c_status ;   -- extra, not needed
for rec in stu_c1 loop    -- that's enough
insert into student_data values (c_status,c_id,c_name);  
....
end loop;   
--CLOSE stu_c1;  -- not needed

Alternatively, you do open, fetch, close, handle NOTFOUND, but not use FOR ... IN.

Upvotes: 1

Related Questions