PL/SQL Cursor returning only last record

August 7, 2023

I am trying to fetch all records from my table using cursor but it’s only returning the last record. Here is code

DECLARE
V_NAME1 Students.V_NAME%TYPE;
 N_CLASS1 Students.N_CLASS%TYPE;
 
CURSOR C1 IS SELECT V_NAME,N_CLASS from students;
BEGIN
    OPEN C1;
    LOOP 
        FETCH C1 INTO V_NAME1,N_CLASS1;
        EXIT WHEN C1%NOTFOUND;
    END LOOP;
dbms_output.put_line(V_NAME1||N_CLASS1);
CLOSE C1;
END;
/

Also below is the table:

CREATE TABLE Students
(
  V_NAME VARCHAR(20),
N_CLASS NUMBER
);

And records are:

INSERT INTO Students values('A',10);
INSERT INTO Students values('B',20);
INSERT INTO Students values('C',30);

But I am just getting C30 as output

>Solution :

It fetches all rows, but displays only the last one because you misplaced dbms_output.put_line call.

SQL> DECLARE
  2     V_NAME1   Students.V_NAME%TYPE;
  3     N_CLASS1  Students.N_CLASS%TYPE;
  4
  5     CURSOR C1 IS SELECT V_NAME, N_CLASS FROM students;
  6  BEGIN
  7     OPEN C1;
  8
  9     LOOP
 10        FETCH C1 INTO V_NAME1, N_CLASS1;
 11
 12        EXIT WHEN C1%NOTFOUND;
 13        DBMS_OUTPUT.put_line (V_NAME1 || N_CLASS1);        --> here
 14     END LOOP;
 15
 16     -- dbms_output.put_line(V_NAME1||N_CLASS1);           --> not here
 17     CLOSE C1;
 18  END;
 19  /
A10
B20
C30

PL/SQL procedure successfully completed.

SQL>

Apart from that, consider using a cursor FOR loop as it is way simpler; you don’t have to create cursor variables, open the cursor, fetch from it, pay attention about exiting the loop nor close the cursor – Oracle does it all for you:

SQL> begin
  2    for cur_r in (select v_name, n_class from students) loop
  3      dbms_output.put_line(cur_r.v_name||cur_r.n_class);
  4    end loop;
  5  end;
  6  /
A10
B20
C30

PL/SQL procedure successfully completed.

SQL>