How do I execute a "fire and forget" Oracle stored procedure in ODP.Net? [duplicate]

Question

I'm trying to resolve a thread blocking issue in an API that calls an Oracle DBMS's dbms_snapshot.refresh stored procedure. It is very long running in this case.

The existing code has what you might expect:

await cmd.ExecuteNonQueryAsync();

If this were a T-SQL DBMS, the command object would have a cmd.BeginExecuteNonQuery() method that would fire off the stored procedure without waiting for a return value. In ODP.Net's OracleCommand equivalent, this method does not exist.

How can I make the same call, but without the application parking and waiting for a return value?

Note: The DB is handled by a separate department; this must be a c# solution.

This is Oracle solution - see if you can use it (talk to another department?). Write a stored procedure which will call dbms_snapshot.refresh. Then you'd just schedule (using DBMS_SCHEDULER built-in package) newly written procedure and it would do the refreshing in the background and you won't have to wait for anything. — Littlefoot
– Littlefoot, Commented Jul 12, 2023 at 11:44
Unfortunately, that isn't an option. This is something that is a temporary measure until we complete migrating from an old application to a new one, and the DBAs won't approve temporary changes. Thus why I said I needed a C# solution. — CDove
– CDove, Commented Jul 12, 2023 at 11:48
I see. Unfortunately, I don't speak C#. I hope someone else will be able to assist. — Littlefoot
– Littlefoot, Commented Jul 12, 2023 at 11:49
Open a new thread with a dedicated connection and execute it in background. — Wernfried Domscheit
– Wernfried Domscheit, Commented Jul 12, 2023 at 11:51
@WernfriedDomscheit can you provide that as an answer with example? — CDove
– CDove, Commented Jul 12, 2023 at 12:06

nvoigt · Accepted Answer · 2023-07-12 12:10:49Z

0

Compared to the "Begin..." call, you could just remove the await

cmd.ExecuteNonQueryAsync();

That is the equivalent of the "Begin..." pattern call. However, neither is actually "fire & forget", because if you close the database connection and cancel the transaction, I guess the database will stop and roll back. So, you still need to "fire & wait", you are just free to do so asynchronously.

answered Jul 12, 2023 at 12:10

nvoigt

77.7k28 gold badges103 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I execute a "fire and forget" Oracle stored procedure in ODP.Net? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related