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 ]
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.
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.
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 ]