CODEWITHSUNDEEP
oracle-databaseplsqloracle11goracle-sqldeveloper
Marouane Lakhal
Marouane Lakhal

Reputation: 784

PL/SQL select into a nested array

I'm trying to select values from my table and stock them into an array to manipulate them. this is what i have done.

DECLARE 
   TYPE student IS TABLE OF VARCHAR2(20);
   s student := student();
   n number := 1;

BEGIN
   FOR i IN (SELECT name from HR.STUDENT) loop
   s(n) := i.name;
   n := n + 1;
   end loop;

end;

when i do this i get this error in SQL Developer

An in-limit subscript was greater than the count of a varray or too large for a nested table.

So i'm asking is this the right way to get a my table values into the student Type? any hints ?

i'm new to PL/SQL so i maybe saying wrong things. correct me when i do so. Thank you.

Upvotes: 0

Views: 2343

Answers (1)

Chrisrs2292
Chrisrs2292

Reputation: 1094

Any easier way to accomplish this would be:

SELECT name
BULK COLLECT INTO s
FROM   HR.STUDENT;

With no need for the loop at all.

The reason the error occurs (I think) is that you are trying to access and index of the table which is not yet available. I believe you need to manually call extend prior to the access, which would then make the next index of the array available e.g.

FOR i IN (SELECT name from HR.STUDENT)
LOOP 
  s.extend;
  s(n) := i.name;
  n := n+1;
END LOOP;

Also I believe you need to have s students := students(); - you seem to be missing a colon.

Upvotes: 1

Related Questions