Passing array RecordList to stored procedure

Has anyone passed an array type parameter between IS and a stored procedure? The listed sqlTypes in the docs do not include anything resembling an array. Aside from passing and tokenizing a string, I was hoping for a cleaner way to do this.


Could you please tell a little bit more about what you are trying to do?
and info like what database etc.

I have a list of values (of variable length) that I want to pass to a stored procedure. The stored proc will loop thru the list, finding a record that contains the list item. So basically it is a validation requirement that the list of values (originating from an xml element) actually exist in the oracle database. Say you have a list of invoice numbers and you want to pass them to a stored proc to find out if records that contain these numbers exist in a particular table.


Hi Will,
After googling for this topic, I find that this is certainly possible. What kind of database driver are you using?
Here are some links that I found on the i’net.
Good Luck and sorry that I couldnt give you an answer.

Does anyone of you found the solution of this problem.?
I am also facing a similar problem.
Need to call to a Pl/Sql stored procedure that accepts a Variable varchar array as input.
When i browsed through the sqlTypes the flow service of webmethods supports. Doesnt found an array type.

Can Anyone help?


Yemi Bedu

Thanks for replying…
The backend is Oracle…
In Oracle we do have varaible array…so my problem is I want to pass an aary of values to an Oracle stored procedure that accepts a variable array from Webmethods.

For sql server there is nothing as variable array so it is true that for passing array of values into SQL server stored procedure we use XML.


Yemi Bedu

Yemi Thanks for replying…
My actual question was that is it possible to call the stored procedure that accepts a varaiable array as input through flow service…in webmethods 4.6
I created a java service and it worked.

Could please provide more idea or steps to do about how do we pass arraylist as input to store procedure from IS .
More Details of DB and Adapter are:


Adapter :JDBC


At oracle end the following steps are required
1.Create a Type in Oracle
ex. CREATE OR REPLACE type recordarray as VARRAY(10000) of varchar2(250)
2.Create a Stored procedure that takes as an input the variable of the created type.
3. In the procedure create a loop over the type.

At Webmethods IS end following steps are required

  1. Create a java service that takes as an input a string list.
  2. Create a string array in the java service as
    String anArrayIn = IDataUtil.getStringArray( pipelineCursor, “InputstringListName” );
  3. Register a thin driver
  4. Create a connection object.
  5. Create variable of the type oracle.sql.ArrayDescriptor & oracle.sql.Array
    oracle.sql.ArrayDescriptor anArrayDescriptor = new oracle.sql.ArrayDescriptor(“Db Type name”,con);
    oracle.sql.ARRAY anARRAYin = new oracle.sql.ARRAY(anArrayDescriptor, con, "ArrayCreatedInJavaService);
  6. Call the stored procedure using oracle.jdbc.OracleCallableStatement
  7. Use setArray function to pass the array to the strored procedure
    cstmt.setARRAY(2, anARRAYin)

Do let me know in case you require any other information.