Passing in array parameter to an Oracle stored procedure

[John Lin]

I've searched the forum first for what I need before creating this post. I've found some similar posts but without satisfactory solutions. So please bear with me for bringing this up again.

I need to pass an array of Java objects to a callable statement to execute an Oracle stored procedure. I use Oracle 10g and was able to write the code to do just that by using a couple of classes in the "oracle.sql" package which essentially is Oracle JDBC extension. The Oracle classes I had to use were "oracle.sql.ARRAY" and "oracle.sql.ArrayDescriptor".

Although the code I wrote can do what I need it to do, what I'm really looking for is a standard JDBC way of passing an array of Java objects to a callable statement without having to resort to any vendor specific extensions.

Thanks in advance for the help.
Jiang Lin

[Scott Selikoff]

You can't do it. I'm guessing you want to pass an array into a string with text such as "WHERE ID IN (...)" and use the arbitrary length array to accomplish this. No answer in the API, you have to write something yourself to handle arrays.

[John Lin]

Scott,

Thanks for the reply.

To clarify, no, I'm NOT trying to pass an array into a string.

A SQL developer has created a collection type on the Oracle side. There is a stored procedure whose input parameter is of this collection type. What needs to be done on the Java side is to call the stored procedure with an array of Java objects as the input parameter. The Oracle JDBC driver will convert Java object array to the collection type created on the DB side.

This is something that's doable. As I said in my original post, I was able to write the Java code to do just that. But I don't particularly like what I have written primarily because I had to use some classes in "oracle.sql" package which basically is Oracle JDBC extension.

What I'm looking for is doing exactly what I was able to do but in a standard JDBC way without using any DB vendor specific extensions.

Thanks,
Jiang Lin

[Scott Selikoff]

No problem, so many people ask about arbitrary length strings, I'm used to that being the reason.

Can you post your Oracle code? I'd like to see exactly what you're trying to do. Chances are if the regular API didn't work and the Oracle-specific code did, you're likely to be unable to find a better solution. One thing I will say is that having a Java application that is truly portable to any database is nearly impossible, especially in JDBC. While Object-Relational mapping tools can shield you from a lot, its non-trivial to have an application whose SQL text is fully portable. In your example, someone would have to re-write the stored procedure for a different database which having done, is not easy.

I don't like database-specific extensions either to be honest, but if it makes your code work and you don't have any actual plans to switch databases, they can be overlooked. The best advice I can say is isolate such issues so that you know where they are, such as putting them in a single class and/or using a interface/factory pattern so you could swap a new class in for a different database down the road without affecting the rest of the application.

[John Lin]

Thanks Scott again for your reply. I agree with pretty much everything you said.

The situation I'm in is that the application I'm currently working on can be run in either online mode or offline mode. As the result, it uses two different database vendors -- Oracle for online mode and a locally installed database from a different vendor for offline mode. That's why it's kinda important for me to be able to write the database access code without any vendor specific extensions.

My code snippet goes like this:

        ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("UDT_VARCHAR_ARRAY", aConnection);
        String[] strings = new String[] { "s1", "s2" };
        ARRAY sqlArrays = new ARRAY(arrayDescriptor, aConnection, strings);
        CallableStatement callableStatement = aConnection.prepareCall("{call MY_STORED_PROC(?)}");
        callableStatement.setArray(1, sqlArrays);
        callableStatement.executeUpdate();

[Anil Kumar Saha]

Hi,

Have a look into my blog entry.

http://agilej.blogspot.com/2009/05/passing-oraclesqlarray-to-stored.html

It might help.

[John Lin]

Anil,

Thanks for your reply. I followed your link to your blog and saw the sample code which was written by using Oracle JDBC extension.

I was a bit confused -- if you have read my original post, you would know that I already got my code working by using Oracle JDBC extension and trying not to have to use that extension was the very reason why I created this thread in the first place. Since your code shown on your blog is full of calls to Oracle JDBC extension, I was wondering if you posted a wrong link by mistake.

On a separate note, I noticed that in your code you chose to use "oracle.sql.STRUCT" and "oracle.sql.StructDescriptor" to handle a user defined type on the Java side. As you may have already known, such UDTs could also be handled simply by using standard JDBC API "java.sql.SQLData" ... etc without using any database vendor specific extensions. (Of course, Oracle claims better performance to use its own extension, as any one would expect...)

Thanks,
Jiang Lin