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 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.