The moose likes JDBC and Relational Databases and the fly likes Array as input to stored procedure. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Array as input to stored procedure." Watch "Array as input to stored procedure." New topic

Array as input to stored procedure.

Narayana Rao Buddepu

Joined: Aug 02, 2004
Posts: 2
Hi All,

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 :-)
Dilshad Marikar
Ranch Hand

Joined: Sep 08, 2004
Posts: 32
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:

ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor( "OWNER_USER.NUM_ARRAY", conn );

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 ]
I agree. Here's the link:
subject: Array as input to stored procedure.
It's not a secret anymore!