HTTP Requests and Reponse Wait Issue
I have a problem that I am trying to solve using Java stored procdures instead of PL/SQL.
I have a procedure that selects a number of rows, say, 1000. For each row, I need to post a HTTP request over the internet to an IP address which then sends a response which I need to process in to the database.
I am having trouble understanding how Oracle will deal with waiting on the response from the remote server.
In particular, imagine row 1 of these 1000 has a quite long response time back from the server. Do all the 999 remaining rows have to wait??
I tried using UTL_HTTP.REQUEST in this procedure to do this call but I suppose that this call will wait. While the response will typically be fast, I am concerned that all the other rows will take ages.
Ideally I would like to send out the HTTP request and forget about it, freeing up the remaining 999 rows for processing so they can all send their own HTTP requests out.
So, I decided not to use UTL_HTTP.REQUEST but to use Java stored procedures instead so that I can use the Java .net URL object as I thought maybe I could just open a connection to the IP and write down it without worrying about the reponse. I do not know whether this is the best way to do this though as then I do not get a reponse. I would need to have a separate listener to listen to incoming messages.
The other thing was in how the JVM works. It seems that for each instance of the Java class, this class acts as though it were in a totally separate JVM. That means no static database connections to police for multithreading issues. Can I assume that 2 Java instances running in the JVM that both want to access the same table column for update at the same time are properly scheduled by Oracle?
Has anyone done anything similar to this? I would be very interested to know. Mainly I would like a solution for sending HTTP requests and not waiting on a response so that potentially many requests (1000+) can go out without delay.
Many thanks for your time,
Allistair