RE: Asynchronus PL/SQL - Webservices Call

From: Kennedy, Jim <jim_kennedy_at_mentor.com>
Date: Thu, 29 Sep 2005 10:30:52 -0700
Message-ID: <EF25DB6D87DD1A469C80A312C63C3B4C049358E2@SVR-ORW-EXC-07.mgc.mentorg.com>

You are going to have to test it. I wouldn't depend upon the order of adding to the job queue. I would make sure the job itself handles the tasks in the correct order.
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 10:12 AM To: 'oracle-l_at_freelists.org'
Subject: 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

Received on Thu Sep 29 2005 - 12:33:12 CDT