Remove the MULTIPLE BOXES AND STORE in 1 VARIABLE - ORIGINAL PROCEDURE

Question

I am working on ORACLE STORED PROCEDURES and I have doubts. I have a query that retrieves more than 1 row, and I want to save all these 3 row values ​​in 1 variable. Can someone please help me with this.

My QUERY query looks like this:

SELECT STUDENT_NAME FROM STUDENT.STUDENT_DETAILS WHERE CLASS_ID= 'C';

Here this query retrieves 3 names

Jack, Jill, Rabbit

I want all these 3 names to be stored in 1 variable ie C_NAMES. And after that I use this variable in the next steps of my procedure.

Can anyone help me with this.

I would greatly appreciate your time and efforts.

Thanks in advance,

Vrinda :)

Answer 1

 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; 

Answer 2

Depending on your version of Oracle (> = 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 version of Oracle is inferior to 11G (11.2), look here

Answer 3

To do this, you need a cursor:

 DECLARE CURSOR stud_cur IS SELECT STUDENT_NAME FROM STUDENT.STUDENT_DETAILS WHERE CLASS_ID= 'C'; l_stud STUDENT.STUDENT_DETAILS%ROWTYPE; BEGIN OPEN stud_cur; LOOP FETCH stud_cur INTO l_stud; EXIT WHEN stud_cur%NOTFOUND; /* The first time, stud_cur.STUDENT_NAME will be Jack, then Jill... */ END LOOP; CLOSE stud_cur; END; 

Answer 4

Hello everyone and Thank you for your time. I resolved this issue 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 use this in a stored procedure or PLSQL, you just need 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 helping people.