Unable to pass array to a Stored Procedure in DB2

Question

I am unable to call a stored procedure which has an input parameter as an integer array.

The stored procedure declaration is as follows

      CREATE OR REPLACE PROCEDURE TESTSCHEMA.TESTARRAY
      (IN CHECKSTATUS INTEGER,
      IN JOBID INTARRAY)

The array was declared like this

      CREATE TYPE INTARRAY AS INTEGER ARRAY[]@

When I try to call the procedure using

      CALL TESTSCHEMA.TESTARRAY( 1 , array[21,22,23] )@

I get the following error -

An unexpected token "ARRAY[" was found following "ARRAY[". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.63.123 SQL Code: -104, SQL State: 42601'

I cannot seem to find any other way to do this? Can anyone please help with this?

Also need to find a way to pass the array in Java later.

Answer 1

SQL PL arrays can only be used in SQL PL context. You'll need to declare a variable of the INTARRAY type and call your procedure, using that variable, from a compound SQL statement:

db2inst1@blusrv:~> db2 "create type INTARRAY AS INTEGER ARRAY[]"
DB20000I  The SQL command completed successfully.
db2inst1@blusrv:~> db2 "create or replace procedure testarray(in checkstatus integer, in jobid intarray) begin call dbms_output.put_line('testarray'); end"
DB20000I  The SQL command completed successfully.
db2inst1@blusrv:~> db2 set serveroutput on
DB20000I  The SET SERVEROUTPUT command completed successfully.
db2inst1@blusrv:~> db2 "begin declare v intarray; set v =  array[21,22,23]; call testarray(1,v); end"
DB20000I  The SQL command completed successfully.

testarray