Problem Canceling Locking Queries Using JDBC THIN and setQueryTimeout
Last updated on MAY 19, 2017
Applies to:JDBC - Version 10.2.0.1 to 10.2.0.5 [Release 10.2]
Information in this document applies to any platform.
Attempting to simulate a slow query using a locking call such DBMS_LOCK.SLEEP and using a JDBC Statement to run this query for the purpose of testing the Statement.cancel or the Statement.setQueryTimeout.
You notice that while the query that exceeds the requested timeout does raise the expected ORA-01013, however the response time for canceling the query indicates that the exception what not raised until after the the full query length has completed.
For example if a query takes 20 seconds to execute and a setQueryTimeout is set for 5 seconds, the SQLException ORA-01013: user requested cancel of current operation will occur after 20 seconds.
select sysdate into v_date1 from dual;
select sysdate into v_date2 from dual;
return to_char(trunc((v_date2 - v_date1) *60*60*24)) || ' seconds delay';
This issue is specific to JDBC/thin for drivers version prior to 11g.
Testing was done using a Linux x86-32bit Oracle RDBMS 10.2.0.3.
JDBC client using JDK 1.5
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms