Solved

Passing list of object to Oracle Database Procedure

Posted on 2016-09-23
3
68 Views
Last Modified: 2016-10-07
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.
0
Comment
Question by:Melodi Roberts
  • 2
3 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 41812564
What I would do is have Java load the temporary table and then call the procedure.  Don't try and pass all of that data and then parse it, "pass" it by loading it into the table.
2
 

Author Comment

by:Melodi Roberts
ID: 41812625
Thank you for the feedback.

So it's more efficient to handle the inserts on the java side, and then execute the business logic against the global temp table in the database.  I was a bit worried about passing all that data in one fell swoop of which I have no control over or knowledge of the amount of data in advance....I'm guessing that could cause efficiency issues.  I can't find any info. on the limits to the size/amoutn of data passed in one call but....

Doing it within java gives the advantage of more control because, for example, I can do a loop through the prepared list <object>and then use the 'update batching' functionality to commit sets of records.

The thing I have to figure out now is where to provide the information on the status of each object processed.  I can either store this in the global temp, and then reference the table from the calling java, loop through it and do something with the status on the java side, e.g. output to a log file...or the database side, outputting to log file..I guess that is more driven by the functionality required.

btw-Is it common practice when downloading data from a third party server, to first load into a global temp table and then execute remaining logic in database?
0
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 41812643
The theoretical limit on what you can pass to Oracle would be about 2G.  That would be dealing with LOB datatypes.  They aren't as easy to work with as VARCHAR, but that is more severely limited.

The key is going to be making sure that your insert statement is using bind variables.  That is where your efficiency is going to come from.  This way the database isn't parsing your statement every time.  It uses the already parsed plan and just runs with your variable.

What you are describing is a pretty standard ETL type process.  Very common to load into a temporary table and then move to a permanent table.
2

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
couple of eclipse 5 35
how to install java on RHEL image on EC2 4 26
Trying to get a Linked Server to Oracle DB working 21 57
printf performancy 11 32
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now