How to populate a single-dimension array with result of a query FOR DB2?

Question

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 
FROM UR.APPLIEDPROFILE 
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.

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.