SYS_REFCURSOR returns incorrect number of rows ?
Looping thru a SYS_REFCURSOR leads to one more result than expected. With one doulet.
EXAMPLE: (happens in all of our databases, standard DBs, CDBs, PDBs, on Solaris, Exadata ...
CREATE TABLE TEST (NUM NUMBER);
INSERT INTO TEST (NUM) VALUES (1);
INSERT INTO TEST (NUM) VALUES (2);
COMMIT;
DECLARE
result NUMBER;
CURSOR cur_std IS SELECT * FROM TEST;
cur_ref SYS_REFCURSOR;
c_sql VARCHAR2(99) := 'SELECT * FROM TEST';
BEGIN
FOR x IN cur_std
LOOP
DBMS_OUTPUT.PUT_LINE(x.num);
END LOOP;
DBMS_OUTPUT.NEW_LINE;
OPEN cur_ref FOR c_sql;
LOOP
FETCH cur_ref INTO result;
DBMS_OUTPUT.PUT_LINE(result);