Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: VB, Oracle, Asynchronous execution of procedures

Re: VB, Oracle, Asynchronous execution of procedures

From: Chris L. <diversos_at_uol.com.ar>
Date: 27 Oct 2005 17:13:28 -0700
Message-ID: <1130458408.599839.233560@g44g2000cwa.googlegroups.com>


Thanks for all the responses. Oracle version is 9.2.0.4.0 Enterprise with Partitioning Option.

Turns out, I had a wrong concept of what "autonomous_transaction" is and what it accomplishes. Now after some reading I know what's its use.

Will look into the DBMS_JOB suggestion and hope it works.

Billy: Thanks for taking so much time to answer. However you hopefully realize it's simpler than what you described. (You went to such a level of detail, I'm surprised you didn't mention CPU registers nor posted some assembler code samples :)

What I want to do is tell the DB engine, "Start this package/procedure. I don't care if it takes minutes or days for it to finish. Just start it". Of course I know those procedures don't return data so I don't need to wait for them to finish. I thought there was a simple way to tell Oracle "run this command in a different independent session." Autonomous_transaction isn't the way to go.

Greetings
CAL Billy wrote:
> 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
Received on Thu Oct 27 2005 - 19:13:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US