Take the 2-minute tour ×

I need to populate a single-dimension array with a result set from a select query.

I have created the array type using: create or replace type UR.array_traveler_id as BIGINT array[];

and in the stored proc I am using the following SELECT INTO query:

SELECT TRAVELER_ID bulk collect 
INTO arraylist 
where TRAVELERPROFILEID = p_travelerid;

I have declared the arraylist as: DECLARE arraylist UR.array_traveler_id;

I dont know what is the problem but DB2 is not allowing me to use BULK COLLECTION.

The error which I am getting is "An unexpected token "collect" was found following.....SQLSTATE=42601"

Please suggest a way around.

share|improve this question

1 Answer 1

The BULK COLLECT clause is only valid in PL/SQL context. You should be running DB2 9.7 fix pack 1 or later, the database must be created in the Oracle compatibility mode, and the procedure must be written in PL/SQL, not DB2 SQL PL.

share|improve this answer
then what sould i do? –  saptarshi May 14 '13 at 16:21
That depends on which of the prerequisites are not met. –  mustaccio May 14 '13 at 16:27
I am using DB2 pack 2. I have to populate an array from the resultset of a select statement. Please suggest? –  saptarshi May 15 '13 at 7:58

Your Answer


