Re: [Q]: background query processing in Oracle?

In article <3fk5hs$1lq_at_www.interramp.com> bendermac_at_interramp.com writes:

>
> Hi:
> I am looking for a way of somehow "detaching" the execution of SQL code from
>
> the client's process, namely some way of allowing the code to execute in the
>
> background. Sybase supports this feature. How about Oracle?
>
> Thanks.
> Asim
> BMC
>
>

Asim,

I've come across similar requirements a couple of times. You do not mention which platform or O/S you're particularly interested in nor your precise requirement, however, I guess what you're after is a server process that you can fire a SQL statement at without blocking your client process. The results of the SQL are picked up at a later stage when they are available. I assume freeing up the client process from the SQL execution is the whole point of the exercise here.

Taking UNIX as an example it is certainly possible to design and code a SQL server (but don't call it that for christ sake). However, you are looking at a relatively non-trivial bit of code that in the most generic case is almost certainly going to involve UNIX IPC of sorts and Dynamic SQL Method 4. Not for the faint-hearted but immensely useful. A simpler version might execute one of a number of static SQL statements but with different values for the bind variables. This is less generic and would allow you to avoid Method 4. You're sort of into mini-TP monitor ground then.

Beware. There are all kinds of pitfalls waiting to catch you out. Assuming you know all about Dynamic SQL and UNIX IPCs (Message Queues, Named Pipes, UDP Sockets etc..) there are still a large number of decisions to make.

(1) Your server process will need its own Oracle connection. Do you want all clients to run their SQL as the same user? This is what's going to happen with a single server process for all clients unless you make new connections on the fly (not recommended).

(2) How are you going to pick up your results from the server? You have some synchronization issues here.

(3) How many clients are going to be writing to your server? If each Server is single threaded it's going to become a bottleneck if clients are running elaborate SQL all over the place.

etc... The list goes on.

Just wanted to give you a taste of the kind of area you're entering if you want this kind of functionality. Obviously it all depends on exactly how complex your requirement is. You may just be talking about forking/execing your client process and using pipes!

Hope this gives you a little insight into the issues. If you want to elaborate on your problem I'll try and help further.

Karl

Karl Hampson                                            Karl_at_rico.demon.co.uk