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?

This can be doen without using cursors. Do you really need to use cursors? — Arnab Bhagabati
– Arnab Bhagabati, Commented May 22, 2014 at 10:27

Avrajit Roy · Accepted Answer · 2014-05-22 12:02:29Z

1

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

answered May 22, 2014 at 12:02

Avrajit Roy

3,3131 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ravi. · Accepted Answer · 2014-05-22 11:02:50Z

0

I'm not sure which environment you're using because Oracle have different string aggregation function for 10G and 11G release.

For 10G you should consider using WM_CONCAT function. Below is the sample code which you're trying to achieve through cursor

DECLARE
   CURSOR C_APP
   IS
      SELECT   T.A#, WM_CONCAT (P.POSITION)
          FROM TEST T INNER JOIN POSITION P ON T.A# = P.P#
      GROUP BY T.A#;

   C_A#    TEST.A#%TYPE;
   C_POS   POSITION.POSITION%TYPE;
BEGIN
   OPEN C_APP;

   LOOP
      FETCH C_APP
       INTO C_A#, C_POS;
      EXIT WHEN C_APP%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (C_A# || ':' || C_POS);
   END LOOP;
   CLOSE C_APP;
END;

For 11G you can use listagg function. Below is the sample code

DECLARE
   CURSOR C_APP
   IS
      SELECT T.A#,
          LISTAGG(P.POSITION,',') WITHIN GROUP (ORDER BY P.POSITION) 
     FROM TEST T INNER JOIN POSITION P ON T.A# = P.P#
   GROUP BY T.A#;

   C_A#    TEST.A#%TYPE;
   C_POS   POSITION.POSITION%TYPE;
BEGIN
   OPEN C_APP;

   LOOP
      FETCH C_APP
       INTO C_A#, C_POS;
      EXIT WHEN C_APP%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (C_A# || ':' || C_POS);
   END LOOP;
   CLOSE C_APP;
END;

Make sure you have set serveroutput on in order to display the result.

answered May 22, 2014 at 11:02

Ravi.

6722 gold badges10 silver badges21 bronze badges

3 Comments

user3664490 Over a year ago

always face the problem numeric value too small ORA-06502

user3664490 Over a year ago

it stated line 11 . so any solution ?

Ravi. Over a year ago

@user3664490 I'm not sure what your table definition looks like. Most probably it's coz of your Position column datatype varchar range exceeding the limit after returning multiple value for A# column. Otherwise I can't seem to think of any other reason why the above code isn't working

user3278460 · Accepted Answer · 2014-05-22 10:30:57Z

0

for 11g you can use

CURSOR c_app IS
       SELECT t.a#, listagg(p.position, ', ') WITHIN GROUP(order by t.a#) over(partition by t.a#) from test t
       INNER JOIN position p ON t.a#=p.p#;

answered May 22, 2014 at 10:30

user3278460

Comments

user2204412 · Accepted Answer · 2014-05-22 10:34:36Z

0

Not sure, but , dont you have to close this line?

OPEN c_app;

answered May 22, 2014 at 10:34

user2204412

Comments

Rock 'em · Accepted Answer · 2014-05-22 10:44:20Z

0

Have a look at this. DBMS_OUTPUT might help you

answered May 22, 2014 at 10:44

Rock 'em

1872 gold badges2 silver badges8 bronze badges

Collectives™ on Stack Overflow

PL/SQL using fetching data in LOOPING

5 Answers 5

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related