Solved

How to cancel Oracle query from VB.Net Windows Application

Posted on 2014-09-17
3
599 Views
Last Modified: 2014-09-29
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
Comment
Question by:BISCO3
  • 2
3 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 

Author Comment

by:BISCO3
Comment Utility
That's good for a scalar command but what about cancelling a Fill operation on a Data Adapter?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now