Passing array RecordList to stored procedure

[Will_Kriski]

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,

[vinod.ravi.20798]

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

[Will_Kriski]

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

[vinod.ravi.20798]

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

[Deepali_Saini]

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?

[Yemi_Bedu]

Hello,

Did you download the sql code? In reading the first lines, I see that they do not actually pass and array. They pass a long string list and a delimiter. The stored procedure actually does the initial splitting of string for a psuedo array. Unless you have the code to handle make the array on the DB side, you may what to recode your SP to be more utility like to work on the array on the WM side. One link on google(Transact SQL):
Arrays and Lists in SQL Server

And one to tell you why you can’t do it some other ways (.NET):
How to pass array of values into SQL Server stored procedure using XML and Visual Basic .NET

Information with ODP and .Net:
How to: Bind an Array to an ODP.NET Database Command

Hopefully some of these links along with my posting thought can help you. Good day.

Yemi Bedu

[Deepali_Saini]

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]

Hello,

You can check out the Oracle Developer Network. I did a google search for “java array oracle stored procedure” and came up with this on the first link:
Returning a Java Array into PL/SQL via a Java Stored Procedure and vice versa

Inside this was another link that led me to:
passing arrays into pl/sql stored procedures

From here, you will need to sign up for the ODN, but its free and easy. That will also allow you to get to this link:
Oracle8i JDBC Developer’s Guide and Reference

With all this, you can at least feel like you are headed in the right direction. I would ofcourse suggest that you go to Developer documents for your version (9i, 10g, …) for getting up to date information.

Also, these are not clean integration neutral approaches for getting your result in WM, but they help to initiate fast solutions that can have possible robust future expansions. You have also see if WM have tips for this kind of thing. Good day.

Yemi Bedu

[Deepali_Saini]

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.

[kumarch]

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

[Deepali_Saini]

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