Passing parameters of data type ARRAY to Java routines

You can pass ARRAY data type parameters to and from Java™ procedures.

About this task

When your stored procedure is created with the ARRAY data type parameters, your stored procedure can accept or return a variable number of input data or return data of the same data type with a single parameter.

For example, you can pass all the names of students in a class to a procedure without knowing the number of students with a single parameter.


To pass a parameter of type ARRAY:

  1. The ARRAY data type must be already defined. To define an array type, the CREATE TYPE statement must be executed.
  2. The procedure definition must include a parameter of the defined type. The following CREATE PROCEDURE statement example accepts a user-defined ARRAY data type IntArray:


CREATE PROCEDURE inArray (IN input IntArray)
EXTERNAL NAME 'MyProcs:MyArrayProcs!inArray';
In the procedure definition, the array parameter is typed as java.sql.Array. Within the procedure, the argument is mapped to a Java array using the getArray() method, as shown in the following example. Notice the use of Integer rather than int (or other primitive types) for arrays.
static void inArray(java.sql.Array input)
Integer[] inputArr = (Integer [])input.getArray();
int sum = 0;
for(int i=0, i < inputArr.length; i++)
sum += inputArr[i];