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.
Win a copy of Real-World Software Development: A Project-Driven Guide to Fundamentals in Java this week in the Agile and Other Processes forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Liutauras Vilda
  • Knute Snortum
  • Bear Bibeault
Sheriffs:
  • Devaka Cooray
  • Jeanne Boyarsky
  • Junilu Lacar
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • salvin francis
Bartenders:
  • Tim Holloway
  • Piet Souris
  • Frits Walraven

Array as input to stored procedure.

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 :-)
 
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Morning came much too soon and it brought along a friend named Margarita Hangover, and a tiny ad.
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!