• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 891
  • Last Modified:

How to cancel Oracle query from VB.Net Windows Application

I have a straightforward windows application written using VB.Net, Visual Studio 2010 using the Oracle Data Provider.

Sometimes Users issue queries that take a long time to process and would like to cancel the query.

I would like to display a button above a Datagrid that says "Cancel" and when clicked, abort the query and kill the User request with Oracle.  TOAD currently uses this functionality but I don't know how they do it.

Any code snippets would be greatly appreciated.
0
BISCO3
Asked:
BISCO3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
If you are using ODP.Net try:

OracleCommand.Cancel

http://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleCommandClass.htm#DAFIEHHG

There is an example in that doc link.

If you aren't using ODP.Net, I would STRONGLY encourage you to start.
0
 
BISCO3Author Commented:
That's good for a scalar command but what about cancelling a Fill operation on a Data Adapter?
0
 
slightwv (䄆 Netminder) Commented:
I'm not a .Net guy.  I'm an Oracle DBA that knows enough .Net to be more than a little dangerous.

I think you have officially exceeded my .Net abilities!

That said:
1:  I found the link below that is likely a SQL Server solution but the commands should port to ODP.Net pretty easily:
http://social.msdn.microsoft.com/Forums/vstudio/en-US/ad4f5811-a08d-4342-bcf1-368ceac834fc/is-there-any-way-to-cancel-a-dataadapterfill-command-while-its-processing?forum=csharpgeneral

2:  A DBA tends to provide database centric solutions.  I beleive you can limit the execution time with resource management but I've never played with any of this:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN11857

3: Tag the select that is used in the fill with a unique 'comment':
select /* UNIQUE_COMMENT_1234567890 */ sysdate from dual;

Then on the app side, find the sid and serial# (for example, 1 and 123) of that exact select statement and execute the following SQL:
alter system kill session '1,123';
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now