RE: Asynchronus PL/SQL - Webservices Call

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 database.  

Thanks,
Raja.

-----Original Message-----
From: Kennedy, Jim [mailto:jim_kennedy_at_mentor.com] Sent: Thursday, September 29, 2005 12:54 PM To: Nagarajan.Subbiah_at_aetn.com; oracle-l_at_freelists.org 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.
Jim

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

Hi,  

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.  

Raja.

--
http://www.freelists.org/webpage/oracle-l