Re: VB, Oracle, Asynchronous execution of procedures

Chris L. wrote:
> How could I launch a package/procedure from VB and avoid the
> client-side wait for the procedure to finish?
>
> I've tried setting up an insertion trigger on a dummy table, then
> invoking the procedure from the trigger (the trigger has the "pragma
> autonomous_transaction")
>
> No success so far. VB inserts the record, the trigger starts the
> procedure, but VB waits for the procedure to finish despite the pragma.
> How could I solve this?

Chris, an Oracle session is single threaded. Period.

Nothing you can do, can make an Oracle session do more than one command (SQL or PL/SQL) at a time.

For a client to multi-thread on Oracle, a new Oracle session has to be created in each thread. A full new session - not just a new SQL handle on the same session handle or "database" handle.

To make an Oracle call a non-blocking call, the statement handle has to be executed in a thread. Threads are created (on Windows) using the Win32 API CreateThread call.

But it gets more complex than just this. Do you create the session handle and statement handle in the main thread and have the client thread use it? If so, then only a single client thread can ever exist as there is only a single (serialised) Oracle session that can be used.

Or do you pass the db auth & connection details to thread, and have that thread create a new session and do its thing? In which case the client is truly multi-threaded (and capable of erroneously spawning 100's of session on the database, causing the DBA to hunt you down with his lead pipe).

And that's just for starters. :-) If the threads return data (e.g. SELECTs was run), they need to notify the main thread of it.. and they need to pass the data to the main thread. But only a single client thread can talk to the main thread at a time (e.g. access a grid to dump data into) - the main thread (like all threads) can also only do a single thing at a time. Thus you not only need to use IPC (Inter Process Communication) for the threads to talk to the main thread, you need something like semaphores for the child threads to stream data back to the main thread.

Alternatively, you need to run each thread with its own window handle and objects (like a grid) for it to write its data into (e.g. a MDI window app).

You also need to be very careful with transaction processing. Using multi-threading could cause a business transaction to span threads.. and that is not supported from the database side (unless you use Oracle Workspace). Normal transaction processing is local to that thread (as that thread has its own Oracle session).

Creating a multi-threading application requires careful though and design.. and coding. It is much more than simply trying to make a database call an async call in order "not to show an hourglass" in the client. (despite what the client API to that database provides for you - that API still need to take care of all the above issues I've raised).

--
Billy