CallableStatement * setObject

Hello all...hopefully someone out here can square me away.

I recall being able to pass an array of Strings to a stored procedure via CallableStatement's setObject method (inherited) at one point in my career (just over a year ago). At the time, the shop I was working for used SQLServer on the backend and a driver from inet. Now I'm at an Oracle shop and it seems like I can't get away with the same syntax (using a 9i driver)...

Ideally, I'd like to be able to write something along the lines of...

--------------------
CallableStatement cst = conn.prepareCall("{call mypackage.proc(?)}");
cst.setObject(1, new String[] {"foo", "bar"});
cst.execute();
--------------------

The first requirement is that I be able to use setObject to pass an array into PL/SQL. I can do this with Oracle-specific code but the purist in me won't have it.

The second requirement is that the size of that array be flexible. I'm up for bending on proprietary-ness here.

When I try to pull this off today, I'm told that I'm passing in the wrong *type* - I believe PL/SQL is expecting a VARRAY. That could be right, it could be wrong...I'm looking for any sort of feedback as to how to pull this off with Oracle on the backend.

Thanks guys/girls.

I have been through this same issue. Unfortunitely the solution I had to use contained the Oracle specific code using setArray with the Oracle Driver. Good luck in finding another way. I would be interested as well in knowing how to do it from a data base independent manner.