File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

J2ee app gets stuck at a stored procedure call

 
anushka pancham
Greenhorn
Posts: 7
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 823
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 29
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How to set a time out on a connection in the connection pools.
 
Julian Kennedy
Ranch Hand
Posts: 823
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Julian,

I do not know what tags you are talking about . I cut pasted the code from toad...
 
Julian Kennedy
Ranch Hand
Posts: 823
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
anushka pancham
Greenhorn
Posts: 7
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 823
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 823
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic