RE: Asynchronus PL/SQL - Webservices Call

  • From: "Subbiah, Nagarajan" <Nagarajan.Subbiah@xxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 29 Sep 2005 13:11:37 -0400

Excellent! and that is very useful. How about if there are multiple jobs in
the job_queue? Lets say there are 2 transaction happened within 10 secs and
updates the same record. So there are 2 jobs queued to run. When the job
queue process wakes up after 60 seconds, Will those jobs run in a sequential
order assuming I do have only one oracle job queue process? How will be the
behavior with multiple job queue processes? My requirement is those jobs
should run in a sequential order in order to retain the latest update as the
web services will populate these records into the different destination

-----Original Message-----
From: Kennedy, Jim [mailto:jim_kennedy@xxxxxxxxxx] 
Sent: Thursday, September 29, 2005 12:54 PM
To: Nagarajan.Subbiah@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Asynchronus PL/SQL - Webservices Call

To answer your question on submitting an Oracle Job within a trigger:
The job won't run until you commit.  If you rollback then the job submission
is rolled back also.  This is very useful.  I have seen it used for sending
email from within a trigger.  The email will only get sent if the
transaction commits.


From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Subbiah, Nagarajan
Sent: Thursday, September 29, 2005 9:29 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: Asynchronus PL/SQL - Webservices Call

We have a requirement to call a PL/SQL procedure in asynchronous mode with
in the transaction.  The Procedure1 is a trigger of a table which calls a
web service with in the procedure Procedure2 using 'utl_http'.
What are the options to call the Procedure2 as not part of the transaction
OR is there any way to call the 'utl_http' call asynchronously?
There are few options we consider:

*       One way handle this is as part of the application and storing the
value in a table and schedule a procedure in the oracle job to handle this. 

*       Other way is to submit a job in the oracle jobs with in a trigger to
call the procedure 2. But The question is: How does oracle handle the job
which is submitted the part of the trigger? Will that be considered as part
of the transaction? 

*       Third way is to use the Oracle streams and using queues the same
mechanism of Oracle jobs.

If there any simple way to achieve this.
Thanks in Advance.

Other related posts: