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

CREATE PROCEDURE a_procAS    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;

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_DETAILSWHERE CLASS_ID= 'C';

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

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

DECLAREC_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.