File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Stored Procedure Question 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 "Stored Procedure Question" Watch "Stored Procedure Question" New topic

Stored Procedure Question

Jennifer Sohl
Ranch Hand

Joined: Feb 28, 2001
Posts: 455
I have a bill of material maintenance program that I have implemented an error check into. Currently how it works, is it reads each part number that is listed on the bill and runs a stored procedure to get information about that part number to make sure there are no errors on it. The problem... some bills of material have 300+ part numbers on them. So that means I am calling my stored procedure 300+ times!
What I need to know, is how can I get information for all of the part numbers on the bill by running only one stored procedure?

I thought about creating an ArrayList of all the part numbers on the bill... but then I'm not really sure what to do with that. Is there any way I can pass that through a stored procedure?

Thanks for any help!
[ February 23, 2007: Message edited by: Jennifer Sohl ]
Niranjan Sarkar

Joined: Jan 17, 2006
Posts: 5
One cheeky way of doing is by appending all the item codes into a String (separated by "~") and then pass it on as a VARCHAR2 in the Stored Procedure. Of course the stored Procedure needs to be changed for this.
Sol Mayer-Orn
Ranch Hand

Joined: Nov 13, 2002
Posts: 311
For simple cases, i'd go with the above reply - just good old strings (sometimes I would make it a comma-separated list, just ready for the sql 'IN' clause). Probably not 'cutting edge' in terms of design phylosophy, but quick and fun to implement.

More generally, you might use arrays, if your database *and* jdbc driver support it.
For example, if you use oracle (there must be nicer examples on the web, but this one should provide enough key-words for later google searches):

However, note: on some cases, the use of Arrays required us to take more care. When using just plain strings, the system seemed more tolerant towards old drivers, missing database patches, crazy security add-ons , conflicts with tomcat connection pools, etc.

So if you decide to use Arrays, i'd recommend you make sure they work on your production environment (taking into account DB version, drivers, connection pool utilities, security software, etc).
[ February 27, 2007: Message edited by: Sol Mam-Orn ]
I agree. Here's the link:
subject: Stored Procedure Question
It's not a secret anymore!