SQL & PL/SQL

Hi all,

As per my understanding, if we fetch all the rows from a sys_refcursor into a collection, within the pl/sql block, then it doesn't return any row to the calling program. Instead a fetch out of sequence error is encountered. This worked well. However, when I use a single fetch statement(in case the sys_refcursor contains a single row), I do not encounter "Fetch Out Of Sequence" error. It would be really helpful if anyone could please explain this behaviour.

Below is a sample code snippet that I ran in PL/SQL Developer:

CREATE OR REPLACE PROCEDURE test_cur_1(in_dept_id VARCHAR2,
out_cursor OUT SYS_REFCURSOR) IS
temp_count NUMBER;
temp_rec dept%ROWTYPE;

BEGIN
--This piece of code runs into a "Fetch Out Of Sequence" error, when trying to open up the sys_refcursor.
OPEN out_cursor FOR
SELECT *
FROM dept
WHERE dept_id = in_dept_id;--dept_id is the primary key

LOOP
EXIT WHEN out_cursor%NOTFOUND;
FETCH out_cursor
INTO temp_rec;
dbms_output.put_line('department name: '||temp_rec.dept_name);
END LOOP;

EXCEPTION
WHEN OTHERS THEN
OPEN out_cursor FOR
SELECT SQLCODE||'->'||SQLERRM FROM dual;
END;
------------------------------------


CREATE OR REPLACE PROCEDURE test_cur_2(in_dept_id VARCHAR2,
out_cursor OUT SYS_REFCURSOR) IS
temp_count NUMBER;
temp_rec dept%ROWTYPE;

BEGIN
--This piece of code does not run into a "Fetch Out Of Sequence" error. Instead the output sys_refcursor opens up for no rows to display.
OPEN out_cursor FOR
SELECT *
FROM dept
WHERE dept_id = in_dept_id;--dept_id is the primary key

FETCH out_cursor
INTO temp_rec;

dbms_output.put_line('department name: '||temp_rec.dept_name);

EXCEPTION
WHEN OTHERS THEN
OPEN out_cursor FOR
SELECT SQLCODE||'->'||SQLERRM FROM dual;
END;


Appreciate all your time!