"ORA-01001: Invalid cursor" in loop exit condition

Question

I have a requirement to print the employee names (from the employee table) in those departments which are fetched by the department cursor from the departments table.

I tried executing the following code.

declare
    type tn is ref cursor;
    deptemp_cur tn;
    v_dno DEPARTMENTS.DEPARTMENT_ID%type;
    v_dname departments.department_name%type;
    v_ename employees.first_name%type;
begin
    open deptemp_cur for select DEPARTMENT_ID,department_name from DEPARTMENTS where DEPARTMENT_ID<40;
    loop
        fetch deptemp_cur into v_dno, v_dname;
        open deptemp_cur for select first_name from employees where DEPARTMENT_ID=v_dno;
        loop
            fetch deptemp_cur into v_ename;
            dbms_output.put_line(v_dno||'  '||v_ename);
            exit when deptemp_cur%notfound;
        end loop;
        close deptemp_cur;
        exit when deptemp_cur%notfound; -- line 18
    end loop;
    close deptemp_cur;
end;

But I am getting an error at line 18 stating: Error report - ORA-01001: invalid cursor ORA-06512: at line 18 01001. 00000 - "invalid cursor" *Cause:
*Action:

Help me in rectifying this :P

Alex Poole · Accepted Answer · 2021-06-22 19:11:37Z

3

You are doing

exit when deptemp_cur%notfound;

immediately after

close deptemp_cur;

So when you check deptemp_cur%notfound that cursor is closed, and no longer valid.

You are re-using the same cursor variable; which is OK, except you're doing that at two levels, so they will trip over each other. If you want to use this construct then you can use two variables, something like:

declare
    type tn is ref cursor;
    deptemp_cur tn;
    emptemp_cur tn;
    v_dno DEPARTMENTS.DEPARTMENT_ID%type;
    v_dname departments.department_name%type;
    v_ename employees.first_name%type;
begin
    open deptemp_cur for
        select DEPARTMENT_ID,department_name
        from DEPARTMENTS
        where DEPARTMENT_ID<40;
    loop
        fetch deptemp_cur into v_dno, v_dname;
        -- test and exit straight after fetch, unless using bulk collect
        exit when deptemp_cur%notfound;

        -- use second cursor for inner loop
        open emptemp_cur for
            select first_name
            from employees
            where DEPARTMENT_ID=v_dno;
        loop
            fetch emptemp_cur into v_ename;
            -- test and exit straight after fetch
            exit when emptemp_cur%notfound;

            dbms_output.put_line(v_dno||'  '||v_ename);
        end loop;
        close emptemp_cur;
    end loop;
    close deptemp_cur;
end;

You can use sys_refcursor instead of declaring your own cursor type:

declare
    deptemp_cur sys_refcursor;
    emptemp_cur sys_refcursor;
    ...

This would be much simpler with cursor FOR loops, and could be done as a single cursor if you join the tables; or without PL/SQL at all. But presumably this is an exercise in these specific constructs.

answered Jun 22, 2021 at 19:11

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sasikumar Gunasekaran Over a year ago

Hi, thank you so much for this. I was not aware of using different cursor names for different tables. I thought a single cursor name would work for multiple tables since it is a ref cursor. And special thanks to your additional suggestions as well.

Collectives™ on Stack Overflow

"ORA-01001: Invalid cursor" in loop exit condition

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related