PL/SQL using fetching data in LOOPING

Question

Table : 1.)Test 2.)Position

First table

 //TEST
A#
---------------
1
2
3

Second table:

//Position
A#       POSITION
------------------
1           GM
1         DIRECTOR
2          DOCTOR
3          HELLO
3           GM

when i use the following pl/sql in my sqlplus

DECLARE
   c_a# test.A#%TYPE;
   c_pos position.position%TYPE;
   CURSOR c_app IS
       SELECT t.a#,p.position from test t
       INNER JOIN position p ON t.a#=p.p#;
BEGIN
   OPEN c_app
   LOOP
       FETCH c_app into c_a# , c_pos;
       DBMS_OUTPUT.PUT_LINE( c_a# || ':' || c_pos );
   END LOOP;
   CLOSE c_app;
END;
/

here is the output:

1:GM
1:Director
2:Doctor
...
...

Expected output:

1:GM,Director
2:Doctor
3:HELLO,GM

is there anything wrong in my looping?

Answer 1

You can try one thing. use collect function. It will fetch the details as well as print it in the needed format.