passing input paramter of type list

[htenay]

I need to be able to define an array in Java and pass it on to SQL stored procedure.
I am new to store proc and found a lot of helpful tutorials on Google but found none that passes parameter of type list, process and return result.

My question is: I like to be able to pass input parameter of type list into stored procedure and pars the list, process it and return result set. It is an SQL stored procedure (in DB2) that is called with in Java application. The list of the input is built in the java code.


Here is one of the SP: input =zipCode (list). The idea is that if I need to get city name and state code for say 2000 zip codes, I just want to make one call, pass the 2000 zip codes and get the result.

The stored proc below works if I set zipCode to just one value (‘20000’).


CREATE PROCEDURE RETRIEVEZIPCODE ( IN zipCode VARCHAR(5))
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT ZIP_CODE, ZIP_CITY_NAME, ZIP_STATE_CODE
FROM ZIP_CODE_T
WHERE ZIP_CODE IN (zipCode);
-- Cursor left open for client application
OPEN cursor1;
END P1

Any idea how I can accomplish this? FYI – using temp table is not going to work for the client.

Thanks a lot!