Hi all,
I am trying to create a array using db2 pl/sql stored procedure. Here is the sample code.

CREATE TYPE phonenumbers AS VARCHAR(12) ARRAY[1000]
-- Procedure find_customers searches for numbers in
-- numbers_in that begin with the given area_code,
-- and reports them in numbers_out.
-- Phone numbers are strings of the form 416-413-9394

CREATE PROCEDURE find_customers(
IN numbers_in phonenumbers,
IN area_code CHAR(3),
OUT numbers_out phonenumbers)
BEGIN
SET numbers_out =
(SELECT ARRAY_AGG(T.num)
FROM UNNEST(numbers_in) AS T(num)
WHERE substr(T.num, 1, 3) = area_code);
END

I am using IBM data studio software and trying to run this code in the specified software. The problem is that it is indicating the error on line no 1 i.e. CREATE TYPE phonenumbers AS VARCHAR(12) ARRAY[1000]. The error is "statement_terminator missing". Wen i give semicolon as statement terminator, stil it gives the same error. Is there any other way to declare or use the arrays in pl/sql stored procedure. Or is there any other alternative to pass multiple values into a variable as input. Please help.

You cannot use the semicolon as the statement terminator because of the Stored Procedure definition. You need to use something else. Most people use the @. You also need to tell Data Studio what the terminator character is.

Andy