Servlet threads hang after till the execution of a Stored Procedure

Posted on 2008-10-15
Medium Priority
Last Modified: 2013-12-07
Hi Experts,
Am doing an Export DataPump procedure call from Java Servlet which executes the PL SQL spanning a new Thread. After this thread starts up, until it finishes executing the StoredProcedure, the servlet doesnot accept any calls. When i click on any links from my jsp, it just hangs until the Thread finishes completion of the Export Operation.

I checked if its a limitation of Oracle, by executing the Stored Procedure from command prompt and trying to access different links from jsp. It works perfectly fine. The issue is only when calling the StoredProcedure from java.

The below are the threads i tried but found no help.

I have implemented Connection Pooling. Iam running Tomcat as the server and ORacle as the database. Both Tomcat 6.0 and Oracle 11i are in the same windows box. It works fine for other queries and procedures except the Export and Import Procedures which takes a longer time.

Iam using the latest ojdbc5.jar as jdbc driver.
Please help on this issue.
public void run() {
private synchronized void startExport(){
// forming the query
// getting connection
java.sql.CallableStatement cs;
cs = con.prepareCall(query);
System.out.println(" $$$$$$$$$$$$$$$$$$$$$$$$$$$$$ START ARCHIVE ");
System.out.println(" $$$$$$$$$$$$$$$$$$$$$$$$$$$$$ END ARCHIVE ");

Open in new window

Question by:p_vishnu7
  • 2

Expert Comment

ID: 22729029
from the main thread of your business class
if you are calling this thread then this should work.....
Do you want it as a backend process?
Do you want to show the ststus of the execution on the status bar?
Export import take long time as the amount of data is huge. and the time required has nothing to do with connection pooling

Author Comment

ID: 22752273
Thanks for your reply.
Yes, am calling from a main thread. As you had said, i want to run it as a background process(Thread) as Export and Import DataPump will take more time. For now iam not displaying the progress. Over time i want to do it. But what i want to achieve is, when the Export is started, i want this to go as a background process meanwhile the servlet should allow the user to click on other pages in the application. But even this does not happen. When i put some trace statements, i could see the thread calls the execute method of the DataPump and hangs there. When i click on other pages any lines of code before there is an execute statements (rather any database calls) prints and waits before trying to run the execute statement. I strongly feel the socket between the Tomcat (or what so ever) to Oracle connection puts a lock so that any other concurrent database operations are not allowed. Please correct me if am wrong.
I appreciate your time to answer this issue. Thanks.

Author Comment

ID: 22947451
Thanks to amodmulay: for effort made to answer this question.  We were using the book example (or sun java example) of Connection Pooling, please see site:- http://java.sun.com/developer/onlineTraining/Programming/JDCBook/conpool.html

The book example of public synchronized void reapConnections() has the below code
 if((conn.inUse()) && (stale >conn.getLastUse()) &&  (!conn.validate())) {

but in our code it was changed to

if (!conn.validate() || stale > conn.getLastUse()){

Changing back to the Sun's code solved this issue.

Accepted Solution

Computer101 earned 0 total points
ID: 22971016
PAQed with points refunded (500)

EE Admin

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Media Temple is thrilled to announce the launch of our new Partner Program, specifically designed to empower digital agencies and adtech platforms by offering white-glove support and exclusive hosting enhancements to optimize their sites and their c…
The Super Bowl is just days away. Millions of advertising dollars will be spent in just a few hours to drive people to websites around the globe. Optimizing your site in anticipation of a big event like this (and the traffic surges that follow) will…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

619 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