How can I know when a stored procedure finishes execution?

[Beth Lampard]

Hello everyone!

I've got an Oracle stored procedure which I am calling from a Java applet. This procedure takes more than 20 mins to finish and returns no results.
Is there any way to know when the procedure ends? (Prefferably without having to alter the procedure's code).

The procedure does a lot of delete/update/insert queries to several tables.
Up until now I have tried this:

 boolean rs; 
synchronized (this){
        						 try {
        							 rs = cStmt.execute(); //executes statement
        							 while (!rs){ //loop while nothings returned
        								 try {
											wait(5000); //wait 5 secs and then recheck
        								 } catch (InterruptedException e) {
											// TODO Auto-generated catch block
											e.printStackTrace();
        								 }
        								 // After it is finished change panel to notify user
        									 resize(400,330);
        									 cl.show(cardPanel, "2");
        									 con.close();
        									 break;
        								
        							} 
        							 
        						 } catch (SQLException e) {
        							 JOptionPane.showMessageDialog(ScansApplet.this, e.getMessage() 
        	            					 ,"Error while running CTA_REPORT_CALLS", 0);
        						 }   

but with no effect.

Please share any ideas if any...!

[Kai Witte]

Hello,

I believe that execute blocks. More logging statements would certainly help you.

Kai

[Jan Cumps]

Correct me if I'm wrong, but
I believe that

rs = cStmt.execute(); //executes statement  

waits until the statement is executed.

If your procedure takes 20 minutes, your code would stop at this line 4 for 20minutes.
If your execute() would have returned a rs, you would now immediately after executing line 4.
If execute() returned null, rs will stay null.
No piece of code you have put below line 4 will change rs.

Regards, Jan

[Jan Cumps]

and welcome to JavaRanch!

[Beth Lampard]

Thank you everyone for your replies and welcomes

Correct me if I'm wrong, but I believe that

rs = cStmt.execute(); //executes statement

waits until the statement is executed.

It does, that's the main reason the statement execution is in a thread.
I was under the impression that rs becomes true when the stored procedure ends execution.
It doesn't though. In my case it returns false but it will do!

I believe that execute blocks. More logging statements would certainly help you.

True! I could use logging statements and check these to see when the stored procedure ends but it costs a lot!
If we assume that the logs are a table I would have to every so often run a query there to check if the procedure ended.
Doesn't sound like the best solution to me.


Thank you for your help both!