OK, so I have a variant on that. I have a query which executes perfectly in under two seconds on an Oracle server accessed through Oracle SQL developer to test the query syntax and results with ROWNUM <= 10. I cut and paste the exact query through to Access, set it to pass-through, set the timeout to 240 seconds, and still get a time-out error. So my question is, what causes such a delay in a pass-through query from Access via ODBC? Obviously it is not taking all that long to run the real work on Oracle.
The SQL code is:
SELECT
H.SEQ_CLAIM_ID,
H.CLAIM_NUMBER,
H.PRIMARY_SVC_DATE,
H.TOTAL_BILLED_AMT,
H.TOTAL_ALLOWED_AMT,
H.TOTAL_NET_AMT,
D.LINE_NUMBER,
D.SUB_LINE_CODE,
D.DETAIL_SVC_DATE,
D.PROCEDURE_CODE,
D.PROCEDURE_MODIFIER,
D.QUANTITY,
D.BILLED_AMT,
D.ALLOWED_AMT,
D.NET_AMT,
D.CLAIM_STATUS,
D.PROCESSING_STATUS,
D.ADJUDICATION_METHOD,
D.POST_DATE,
D.CHECK_DATE
FROM
DORIS.PROFSVC_CLAIM_HEADER H,
DORIS.PROFSVC_CLAIM_DETAIL D
WHERE
D.SEQ_CLAIM_ID = H.SEQ_CLAIM_ID AND
H.SEQ_CLAIM_ID IN
(SELECT DISTINCT SEQ_CLAIM_ID
FROM DORIS.PROFSVC_CLAIM_DETAIL
WHERE SUB_LINE_CODE = 'A' AND
ROWNUM <= 10 AND
DETAIL_SVC_DATE >= DATE '2010-01-01')
ORDER BY H.SEQ_CLAIM_ID,
D.LINE_NUMBER,
D.SUB_LINE_CODE,
D.POST_DATE,
D.CHECK_DATE;
And I get the dreaded 01013 from Oracle.
What this error message means?
ODBC--call failed.
[Oracle][ODBC][Ora]ORA-01013: user requested cancel of current operation
(#1013)
Thank you.
On Tuesday, July 28, 2009 1:47 PM CliffordBas wrote:
Hi Joe,
It can mean that a lengthy operation/query has timed out on the Oracle
side. You could try unchecking the "Enable Query Timeout" option in the ODBC
DSN configuration. If that does not make any difference and the reason is a
timeout, it probably is caused by the Oracle server's query timeout setting.
In which case you will need to contact your Oracle administration. There
could be other reasons, for all I know. You could try doing the query or
operation in a different manner.
Hope that helps,
Clifford Bass
"Joe Roberto" wrote:
