aspose file tools*
The moose likes JDBC and the fly likes J2ee app gets stuck at a stored procedure call Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "J2ee app gets stuck at a stored procedure call" Watch "J2ee app gets stuck at a stored procedure call" New topic
Author

J2ee app gets stuck at a stored procedure call

anushka pancham
Greenhorn

Joined: Aug 23, 2004
Posts: 7
J2ee application using struts gets stuck at a stored procedure call and never comes out of it. The connetion is never released to the pool. It takes the system down. I am using a cursor in the stored procedure. Can anybody suggest what could be causing this?
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Possibly an infinite loop or an extremely inefficient query in your stored procedure. The latter is not uncommon during development. Can you post your SP along with an explanation of what it is doing and and idea of the data volumes on the tables involved?

Jules
Prashant Jain
Greenhorn

Joined: Aug 19, 2004
Posts: 29
Along with Julian's suggestions,if I was in your position I would also look at the fact

1. Are you using multithreading?
2. If so ..is there a select <table> for update , on the same table being run by another thread?

If you are using connection pooling, you could consider debuging your application setting pool size to 1.

It would be good if you could show your stored prc as well as the java code accessing it.

Prashant Jain
prashantjainn@yahoo.com
anushka pancham
Greenhorn

Joined: Aug 23, 2004
Posts: 7
My stored procedure is:




And the way it is called in the Actionseervlet is:



These are the details of the code. I went through the code a lot of times but never found any hole in that.

[edited to add code tags]
[ August 25, 2004: Message edited by: Jeanne Boyarsky ]
anushka pancham
Greenhorn

Joined: Aug 23, 2004
Posts: 7
How to set a time out on a connection in the connection pools.
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Anushka,

Can you please edit your post and put your original code, with all the indentation intact, inside the UBB CODE tags? It's impossible to read at the moment. Can you please also provide the volume info I asked for?

How long does your SP take to execute when you execute it from SQL*Plus or TOAD with the same parameters, rather than from Java?

Thanks

Jules
anushka pancham
Greenhorn

Joined: Aug 23, 2004
Posts: 7
Julian,

I do not know what tags you are talking about . I cut pasted the code from toad...
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Can you see how all your indentation has disappeared in your post? That makes it really hard to try and diagnose your problem. If you look next to the Instant Graemlins on the "Post A Reply" page (or even edit) there are a set of buttons that allow you to mark up your post, kind of HTML-style. Use the CODE button and put your TOAD code between the tags it produces. People should then be able to read your code without too much effort.

Jules
anushka pancham
Greenhorn

Joined: Aug 23, 2004
Posts: 7
anushka pancham
Greenhorn

Joined: Aug 23, 2004
Posts: 7
I posted the stored procedure and the java code used for calling it. PLease let me know if you find something.
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
There doesn't seem to be anything wrong with the Java code (that would cause the problem). What happens when you run the stored procedure directly in Oracle, outside Java?

Jules
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Under what conditions does your problem occur? I mean what path through the stored procedure is being taken when it locks up? As far as I can see, when l_count = 1 or l_count = 0 the SP should complete with no problems. The only dubious bit of code is the following:

The only condition under which the loop will exit is when l_css_status = 'CLIENT SURVEY COMPLETE'. If the cursor doesn't retrieve a record with exactly that status then you'll get an infinite loop; it will never exit.

I suspect that's your problem. If you run your cursor SQL outside the Java, filling in the required request_id parameter, you should be able to verify that.

Jules
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: J2ee app gets stuck at a stored procedure call
 
Similar Threads
Spring hibernate issue
Synchronizing an EJB method
When is exclusive lock released
Select Statement Stored Procedures in MS sql server not returning anything...
Transaction Control for a method.