Problem Canceling Locking Queries Using JDBC THIN and setQueryTimeout (Doc ID 552275.1)

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.

Symptoms

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.

create or replace function slow_query( p_wait number) return varchar2
is
  v_date1 date;
  v_date2 date;
begin
  select sysdate into v_date1 from dual;
  dbms_lock.sleep(p_wait);
  select sysdate into v_date2 from dual;
  return to_char(trunc((v_date2 - v_date1) *60*60*24)) || ' seconds delay';
end;
/

Changes

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

Cause

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