Passing array RecordList to stored procedure

, ,
1

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.

Thanks,

2

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

3

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.

Thanks

4

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.
[url=“http://www.sqlteam.com/downloads/sp_parsearray.sql”]http://www.sqlteam.com/downloads/sp_parsearray.sql[/url]
http://technet.oracle.com
Good Luck and sorry that I couldnt give you an answer.
Thanks

5

Hi
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 pub.db.call flow service of webMethods supports. Doesnt found an array type.

Can Anyone help?

6

Hello,

Yemi Bedu

7

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.

8

Hello,

Yemi Bedu

9

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 pub.db.call flow service…in webMethods 4.6
I created a java service and it worked.

10

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:

Databaseracle

Adapter :JDBC

Thanks
Kumar

11

Hi
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.
ex CREATE OR REPLACE PROCEDURE procName(Array IN RECORDARRAY,sError OUT VARCHAR2) IS
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.

Thanks
Deepali