How to interrupt the execution of a stored procedure while using OracleDataReader?

Question

Oracle

There is a client in c#, connects to the oracle subd via ODP.NET Managed. Cancellation of commands works, but if the execution reaches the Read method of the OracleDataReader class, then it becomes impossible to interrupt the execution. The stored procedure is executed for about 30-40 seconds (a report is built), if the cancellation request occurred before the call to the Read method of the OracleDataReader class, then everything is normally canceled. Forced closing (call of the close method) OracleDataReader, commands and connections (oracleconnection) does not help. It turns out that the actual execution of the query starts only when you try to read the data, because if in the stored procedure you first save all the data in the nested_table, and then return the ref cursor to the application from it, then everything is normally canceled, but it is very inconvenient to implement (and probably not very efficient).

Answer 1

It turns out that the actual execution of the query begins only when you try to read the data

Simply oracle receives request, thinks and then already starts to issue the data.
While he is thinking - the reader is "hanging" in anticipation ...
By the way, why not drive a piece of data mining into a background thread and either bang it on cancellation, or just forget ...?

Answer 2

OracleDataReader has asynchronous method variants. You can call ReadAsync instead of Read and pass your CancellationToken there.