Q:

Fetch MULTIPLE ROWS and STORE in 1 VARIABLE - ORACLE STORED PROCEDURE

I am working on ORACLE STORED PROCEDURES and I have a doubt.
I have a query which fetches more than 1 row and I want to store all those 3 row's values in 1 Variable.
Can anybody please help me with this.
My QUERY goes like this :
SELECT STUDENT_NAME
FROM STUDENT.STUDENT_DETAILS
WHERE CLASS_ID= 'C';
Here this query fetches 3 names

Jack,
  Jill,
  Bunny

I want all those 3 names to be stored in 1 variable i.e C_NAMES.
And after that I am using that variable in further steps of my procedure.
Can anyone please help me with this.
I would highly appreciate your time and effort.
Thanks in advance,
Vrinda :)

A:

CREATE PROCEDURE a_proc
AS
    CURSOR names_cur IS
        SELECT  student_name
        FROM    student.student_details
        WHERE   class_id = 'C';

    names_t  names_cur%ROWTYPE;
    TYPE names_ntt IS TABLE OF names_t%TYPE; -- must use type
    l_names  names_ntt;
BEGIN
    OPEN  names_cur;
    FETCH names_cur BULK COLLECT INTO l_names;
    CLOSE names_cur;

    FOR indx IN 1..l_names.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(l_names(indx).student_name);
    END LOOP;
END a_proc;

A:

Depending on your Oracle version(>= 11G(11.2)), you can use LISTAGG:
SELECT LISTAGG(STUDENT_NAME,',')  WITHIN GROUP (ORDER BY STUDENT_NAME)
FROM STUDENT.STUDENT_DETAILS
WHERE CLASS_ID= 'C';

EDIT:
If your Oracle version is inferior to 11G(11.2), take a look here

A:

Hi all and Thank you for your time.
I have resolved the question and all thanks to Ederson.
Here is the solution :
SELECT WM_CONCAT(STUDENT_NAME) 
FROM STUDENT.STUDENT_DETAILS WHERE CLASS_ID= 'C';

Now if you are using this in a stored procedure or PLSQL you just have to create a variable and use SELECT INTO with it and print the variable.
Here is the code
DECLARE

C_NAMES VARCHAR2(100);

BEGIN

   SELECT WM_CONCAT(STUDENT_NAME) INTO C_NAMES
   FROM STUDENT.STUDENT_DETAILS WHERE CLASS_ID= 'C';

  dbms_output.put_line(sname);

END;

Thanks again for your help people.

