I am trying to setup an Access 97 application that will view data in read-only mode from an Oracle 220.127.116.11.0 Database. If I use the SQL Plus 8.0 tool, the queries run fine and return the proper results. However, if I use the same SQL statement for a passthrough query in Access, I get Error: ORA-1013 User requested cancellation of current operation. This happens almost everytime after 55 seconds of running. No matter what the query statement is. I have set the query timeout to 3600 seconds to make sure that there was plenty of time allocated since I am pulling from a large table. Here is a sample of one of the failing queries:
SELECT SUM(CHESS_PROD.NET_DET.ORD_QTY) as "TotalOrder" FROM CHESS_PROD.NET_DET WHERE CHESS_PROD.NET_DET.ORD_QTY > 100000;
However, if I use a standard select query without the SUM, it comes back fine.
Does anybody have any suggestions or guidance, it would be GREATLY appreciated. I do not really have the approval to use stored procedures on the Oracle server. So I do need to at all costs find a way to use passthrough queries in Access to do some pretty complicated tasks.
Set SQLNET.EXPIRE_TIME in SQLNET.ORA to 0. With expire time set to low (>0) values it can give this error, it is disabled by setting it to 0.
I did change the timeout to be equal to 0, but it comes back with the same error message. Any other suggestions?
I can use VBA for Access does anybody have any examples of how to connect up to Oracle using VBA?
Setting QueryTimeout to 0 is another suggestion.
Does ODBC tracing or SQLNET tracing provide any usefull information? For SQLNET tracing, put TRACE_LEVEL_CLIENT = 16 in your SQLNET.ORA. Don't forget to remove it after tracing, it can produce large tracefiles.
Set Query Timeout to 0 and it works now!!!!
I thought I tried that in the past, but I've tried so many people's suggestions that I probably had a multitude of settings to change to get it to work :)