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

Question

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 :)

How are you going to be using the values later? You could look at a ref cursor, a PL/SQL table... depends how you want to pass them around and/or reference them. — Alex Poole, Jun 06 '13 at 16:58
I have to use that C_NAMES in a clob to replace values all that replacing code is done I just want all the names in C_NAMES variable as of now. — vrivrivri, Jun 06 '13 at 17:06

score 22 · Accepted Answer · edited Oct 26 '16 at 19:21

22

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;

edited Oct 26 '16 at 19:21

ErikL

2,031
6
34
57

answered Jun 06 '13 at 16:53

the_slk

2,172
1
11
10

2

I think there is a need of adding '%TYPE' to 'names_t' after **TYPE names_ntt IS TABLE OF names_t;** – SophisticatedUndoing Oct 13 '15 at 10:02
2

Your code is not 100% correct, it is giving an error the user above me says. You need to use `TYPE names_ntt IS TABLE OF names_cur%ROWTYPE;` And remove `names_t names_cur%ROWTYPE;`. – Tenzin Nov 13 '15 at 10:05

Ederson · Answer 2 · 2013-06-06T18:00:49.580

5

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

edited Jun 06 '13 at 18:00

answered Jun 06 '13 at 16:36

Ederson

133
4

I am working on TOAD FOR ORACLE, This code is showing : ORA-00923: FROM keyword not found where expected – vrivrivri Jun 06 '13 at 16:43
For that I assume your Oracle version is inferior to 11G. Have a look at my EDIT, I believe the link will help you out. – Ederson Jun 06 '13 at 16:49
It must be 11.2G. Same problem: http://stackoverflow.com/questions/5569633/oracle-sql-listagg-function – Ederson Jun 06 '13 at 18:00
weird, I executed the exactly same query(except for the Student schema) and it worked like charm. This is the result: Bunny,Jack,Jill – Ederson Jun 06 '13 at 18:42
Oh I have to put the Schema name because I am executing the procedure from different Schema that's why..some other solution please – vrivrivri Jun 06 '13 at 19:26

vrivrivri · Answer 3 · 2013-06-19T14:57:20.260

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.

score 2 · Answer 4 · answered Jun 06 '13 at 16:27

2

You'll need a cursor for that:

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;

answered Jun 06 '13 at 16:27

vc 74

37,131
7
73
89

Hey Thanks for the prompt reply but I tried this code and its showing --PLS-00302: component 'STUDENT_DETAILS' must be declared --PLS-00320: the declaration of the type of this expression is incomplete or malformed – vrivrivri Jun 06 '13 at 16:40

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

4 Answers4

Linked