We're using QTP 11 to test a custom web site that requires a query from an Oracle 11g database. We're seeing QTP fail when the attempting to open the query with a 'user requested cancel of current operation' error. My experience with this in the past led us to increase the odbc timeout value (from a standard 300 all the way to 180,000 and tried a '0' for no timeout).
However, QTP seems to stop the query within 20-25 seconds no matter what the odbc timeout value is and I'm wondering if there is some other default setup value within QTP that is cancelling the query. QTP can handle simpler queries that don't take more than 20-25 seconds. SQL Developer will execute the query in about 60 seconds.
Our trouble shooting steps so far are testing it on multiple physical and one virutal machine, having our database group create a view and verifying the query executes in a SQL tool.
The relevant code is below and the timeout happens on the last line where QTP tries to execute and open the query.
However, QTP seems to stop the query within 20-25 seconds no matter what the odbc timeout value is and I'm wondering if there is some other default setup value within QTP that is cancelling the query. QTP can handle simpler queries that don't take more than 20-25 seconds. SQL Developer will execute the query in about 60 seconds.
Our trouble shooting steps so far are testing it on multiple physical and one virutal machine, having our database group create a view and verifying the query executes in a SQL tool.
The relevant code is below and the timeout happens on the last line where QTP tries to execute and open the query.
Code:
Set con = CreateObject("ADODB.Connection")
Const adUseClient = 3
con.CursorLocation = adUseClient
con.ConnectionTimeout = 180000
conString = "dsn=" & serverName & "; driver={Oracle in OraClient10g_home1}; uid=" &dbUserName & "; pwd=" & dbPassword
con.Open conString
sQuery = "select p.policy_num " &_
"from "&dbName&".policy p " &_
"left outer join "&dbName&".application a on p.policy_id = a.policy_id " &_
"inner join "&dbName&".APPLICATION_LOB_RISK_UNIT AR on a.app_id = AR.APP_ID " &_
"where AR.REQUESTED_AMT_OF_INS < 250000 " &_
"and a.INS_PRODUCT_CD = 'A' and p.policy_status_cd = 'A' and p.next_renew_expire_action_cd <> 'E' and a.ins_product_cd = 'A' and a.app_status_cd = '4' and a.rate_method_cd = '1' " &_
"and not exists (select 1 from "&dbName&".cancellation_request cr where p.policy_id = cr.policy_id) " &_
"and not exists (select 1 from "&dbName&".loss_file lf where p.policy_id = lf.policy_id and lf.file_status_cd <> 'C') " &_
"and not exists (select 1 from "&dbName&".application a1 where a1.app_type_cd in ('EN', 'RI') and a1.app_status_cd <> '4' and a1.policy_id = p.policy_id) " &_
"and rownum < 10 " &_
"group by p.policy_num, p.policy_id, a.app_id, a.app_type_cd, a.app_status_cd, p.next_renew_expire_action_cd order by p.policy_num desc"
Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open sQuery, con