How to Specify Array variable in plsql

Question

Can any one please help me, how to assign result of select query to a array variable since result can be an array of values.

The term you are looking for is: "Oracle bulk collect". Search the internet and you will find lots of examples. — Rene
– Rene, Commented May 29, 2012 at 10:49

Bob Jarvis - Слава Україні · Accepted Answer · 2012-05-29 13:50:15Z

2

There are a couple of different approaches you could take to get data into your array. The first would be a simple loop, as in the following:

DECLARE
  TYPE NUMBER_ARRAY IS VARRAY(100) OF NUMBER;

  arrNums  NUMBER_ARRAY;
  i NUMBER := 1;
BEGIN
  arrNums := NUMBER_ARRAY();

  FOR aRow IN (SELECT NUMBER_FIELD
                 FROM A_TABLE
                 WHERE ROWNUM <= 100)
  LOOP
    arrNums.EXTEND;
    arrNums(i) := aRow.SEQUENCE_NO;
    i := i + 1;
  END LOOP;
end;

Another, as suggested by @Rene, would be to use BULK COLLECT, as follows:

DECLARE
  TYPE NUMBER_ARRAY IS VARRAY(100) OF NUMBER;

  arrNums  NUMBER_ARRAY;
BEGIN
  arrNums := NUMBER_ARRAY();
  arrNums.EXTEND(100);

  SELECT NUMBER_FIELD
    BULK COLLECT INTO arrNums
    FROM A_TABLE
    WHERE ROWNUM <= 100;
end;

Share and enjoy.

edited May 29, 2012 at 13:50

answered May 29, 2012 at 10:59

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to Specify Array variable in plsql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related