How can I send an array as an input parameter to a stored procedure from a Java program? How do I need to access in the Array in the stored procedure?
Thanks in Advance, Narayana :-)
Joined: Sep 08, 2004
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 ]