Win a copy of Pro Spring MVC with WebFlux: Web Development in Spring Framework 5 and Spring Boot 2 this week in the Spring 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Liutauras Vilda
Sheriffs:
  • Rob Spoor
  • Bear Bibeault
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh

Array as input to stored procedure.

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic