Open cursor timeout problem
604931Oct 24 2007 — edited Oct 25 2007Hi at all I've got a big problem with timeout in when calling a function
I wrote a function that open a ref cursor.
OPEN d_ref_cursor FOR d_statement;
RETURN d_ref_cursor;
A need to set a timeout over this function so I wrote in Java this code:
CallableStatement cstmt = con.prepareCall(call);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.setQueryTimeout(10);
cstmt.execute();
In my opinion the execute timeout should stop the execution of the call because its time cost is more than 10 seconds, but the strange things is that the execute method return instantly.
After execute method I call:
ResultSet rs = (ResultSet) cstmt.getObject(1);
When I call getObject(1) I wait for 40 seconds, this is probably the query d_statement execution time, but I want to block this kind of situation.
The problem is that when I use CURSOR this is impossible.
For example if I've a function with execution time of 30 seconds that return a VARCHAR2 it stops when the 10 seconds of timeout is elapsed, because the execute method didn't finish before the real execution of the query.
Anyone khow how to solve this problem of timeout in case of use a CURSOR ??
Thank you at all