troubleshooting Question

Passing list of object to Oracle Database Procedure

Avatar of Melodi Roberts
Melodi Roberts asked on
JavaOracle Database
3 Comments1 Solution149 ViewsLast Modified:
As part of learning java, I have written java to loop through a list of an <object> and call an oracle database procedure for each object, passing individual values from the object rather than just the entire object.  It runs fine.  It does what it needs to do and seems fairly efficient, but I may redesign it for improved efficiency, improved modularity, and perhaps an improved design for error handling, etc.

I could redesign it to use an oracle type, and pass the list of object to the database, store all values in a global temp table, and then finish all other processing from that point based on data in the global temp table.  

This object list could be in the size of one to thousands.  I will not know in advance and it may grow in the future.  

Should I be concerned with the size of the list I am passing to the database?   Should I instead pass in batch sets using update batching functionality?  I know there is also the option to insert into the table from the java, too.  I am exploring options and the benefits and drawbacks of each right now.

I am reading through many websites, and specifically these to learn more: http://betteratoracle.com/posts/26-passing-arrays-between-java-and-oracle-procedures
http://docs.oracle.com/cd/B28359_01/java.111/b31224/oraperf.htm#CHDCCEHD

Thank you for any feedback or direction on what I should be considering with the design.
ASKER CERTIFIED SOLUTION
johnsone
Senior Oracle DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros