This week's book giveaway is in the Agile and Other Processes forum. We're giving away four copies of Real-World Software Development: A Project-Driven Guide to Fundamentals in Java and have Dr. Raoul-Gabriel Urma & Richard Warburton on-line! See this thread for details.
I was researching this myself recently, and found the resources suggested by Avi in a previous thread here useful. This describes using the oracle ArrayDescriptor and ARRAY classes to call stored procedures with an oracle collection parameter. Note you actually need to create a type in your schema for the parameter in order to call it from JDBC. This isn't a big issue, but what I did find a problem is that you cannot create a synonym for a schema-level type. This means that if you have users that access tables and stored procedures via synonyms, in order to use a stored procedure with an array parameter your ArrayDescriptor must hard-code the owner, eg:
Does anyone know of a way around this? I guess you could somehow write a stored procedure that gave you the owner of the type and prefix it that way, but it still isn't very good..
An easier way is to use the OracleCallableStatement.setPlsqlIndexTable method, which allows you to set a PL/SQL indexed table parameter directly by passing in your array of a supported type. Unfortunately this in only available with OCI JDBC drivers, not JDBC thin drivers, so if you need to support both (as my company does) you need to use oracle arrays...
Here's a little code snippet for using index-by-tables (calling a stored procedure with one index-by-table parameter of varchar2):
[ November 23, 2004: Message edited by: Mohammed Dilsard ]
Morning came much too soon and it brought along a friend named Margarita Hangover, and a tiny ad.