Re: Cancelling a Query/Procedure

I think your problem is that where you commit. When you run a stored procedure and only have 1 commit at the end of your procedure then breaking it will result in a rollback.

The rollback will almost always take just as long as what the procedure has been running for.

What happens is that oracle will write all changes to the database into rollback segments and when you break your procedure oracle has to reapply all changes made to the database with the information it stored in these rollback segments.

Try it for yourself.
A commit is always almost instantanious because the changes has already been made, but a rollback means reapplying changes.

Your problem with the breaking of the procedure not always working is easily explained by this. When you break the procedure it actually first tries to do the rollback before it stops executing. Thus the time it takes to rollback is greater than the time it takes to complete the procedure and thus the commits happens before the rollback is complete. "Mike Fotiou" <mfotiou_at_magma.ca> wrote in message news:1C6k7.29070$w75.12181249_at_news3.rdc2.on.home.com...
> Has anyone had any success in cancelling a long running SQL statement or
> a stored procedure from a client tool, such as VB, using one of the
typical
> access methods?
>
> I am using MS ADO 2.5 and/or the latest version of Oracle Objects for OLE,
> both with most recent release of the Oracle OLE DB Provider. The database
> version is 8i on an NT platform.
>
> OO4OLE does not support cancellation of SQL statements, though you can
> cancel a stored procedure, however, the cancellation time takes almost as
> long as the full execution of the query (and often does not actually
cancel
> the procedure).
>
> ADO does allow cancellation of a SQL statement, but it is not effective
with
> Oracle (worked well with SQL server, no small surprise there). Again, the
> cancellation takes as long as the query itself.
>
> In both cases, I am running my SQL/procedures in non-blocking
(asynchronous)
> mode.
>
> Any insight into these issues would be GREATLY appreciated.
>
> Thanks
>
>
Received on Sun Sep 02 2001 - 14:42:41 CDT