PL/SQL (MOSC)

MOSC Banner

SYS_REFCURSOR returns incorrect number of rows ?

in PL/SQL (MOSC) 2 commentsAnswered

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);

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center