Multiple line output in pl/sql

Question

I have a PL/SQL file that has a loop structure. The script is as follows.

SET SERVEROUTPUT ON
 declare 
  c_id employee.id%type;
  c_name employee.name%type;
  c_address employee.address%type;
  CURSOR c_employee is 
     SELECT id, name, address from employee;
 begin 
  open c_employee;
  LOOP
    FETCH c_employee into c_id, c_name, c_address;
    EXIT when c_employee%notfound;
    dbms_output.put_line(c_id||' '||c_name||' '||c_address);
  END LOOP;
  close c_employee;
  end;
  /

When I run this from SQLPlus I get only the details of the first row but not the rest. What am I doing wrong? How to get all the outputs for the loop.

Are the four rows in your table all committed - or did you insert three of them in another session and they just aren't visible to *this* session yet? — Alex Poole, Dec 29 '15 at 12:28
According to your comment on Yaron's answer, you appear to only have one record in that table. Please specify why you believe it has 4 records. — Jeffrey Kemp, Dec 30 '15 at 05:35

score -2 · Answer 1 · answered Dec 29 '15 at 10:18

-2

Try to convert your code to use a for loop instead of the open statement, like so -

for r_employee in c_employee
  LOOP
    dbms_output.put_line(r_employee.c_id||' '||r_employee.c_name||' '||r_employee.c_address);
  END LOOP;

Where r_employee is a variable of employee%type.

The way you currently wrote it does not iterate through the cursor, and this is why only the first row is presented.

answered Dec 29 '15 at 10:18

Yaron Idan

6,207
5
44
66

I don't understand why his code doesn't iterate through the cursor. – Nitish Dec 29 '15 at 10:26
Still not working. Its giving the same first line output. – Debdipta Halder Dec 29 '15 at 11:07
You don't need to declare `r_employee` as a variable with this type of cursor loop. – Alex Poole Dec 29 '15 at 12:30

score -2 · Answer 2 · edited Dec 29 '15 at 12:50

-2

Even though your code looks correct, it should iterate through all the row not just one. Try to use below snippet and run it in SQL plus if still single row then there may be some other issue.

SET SERVEROUTPUT ON
DECLARE
BEGIN
  FOR I IN
  (SELECT id, name, address FROM employee
  )
  LOOP
    dbms_output.put_line(I.ID||' '||I.name||' '||I.address);
  END LOOP;
END;
/

edited Dec 29 '15 at 12:50

answered Dec 29 '15 at 12:26

Avrajit Roy

3,233
1
13
25

1

This would be better as a comment, this does not answer the question. – Jeffrey Kemp Dec 30 '15 at 05:35
@Debdipta Halder - Please update with the solution for future searchers. – Gary_W Dec 31 '15 at 19:06

Multiple line output in pl/sql

2 Answers2