Wait whila a job is running [message #305711] |
Tue, 11 March 2008 12:10  |
callimaco0082
Messages: 18 Registered: March 2008
|
Junior Member |
|
|
Hi all.
My problem is the following:
I have a PL/SQL "main" procedure taht has the task to execute a job.
The job can't be scheduled because it contains a procedure that is based on users requests and so it needs to be executed through a procedue with DBMS_JOBS.RUN.
The "main" procedure, than, does other operations that need job results.
The problem is that while the job is running the "main" procedure continues to execute its code and so the execution fails because there aren't job results to be used.
I found that there is DBMS_LOCK.SLEEP function but this will suspend the entire session and so also the job and not only the procedure execution. Isn't it?
Any suggestion?
Thanks to all will help.
|
|
|
Re: Wait whila a job is running [message #305712 is a reply to message #305711] |
Tue, 11 March 2008 12:15   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Why do you create a job with dbms_jobs, and not simply call the other procedure directly from the main one.
Then the main one will wait till the call is complete.
|
|
|
|
|
|
|
|
Re: Wait whila a job is running [message #305988 is a reply to message #305711] |
Wed, 12 March 2008 08:36   |
callimaco0082
Messages: 18 Registered: March 2008
|
Junior Member |
|
|
The solution I used is simply this:
Your session either executes the job or your procedure but not both at the same time.
So if it executes the job (that is dbms_job.run) then you are waiting for its end.
And if it executes the procedure (you run the job in background) then sleep only halts your procedure.
Posted by Michel.
Happy now, Kevin?
|
|
|
|
Re: Wait whila a job is running [message #306007 is a reply to message #305711] |
Wed, 12 March 2008 09:48   |
callimaco0082
Messages: 18 Registered: March 2008
|
Junior Member |
|
|
Hi Kevin,
The "S" on DBMS_JOBS was a mistake. I was referring to DBMS_JOB.
The problem originally was that in my "main" procedure there was a part in which a job was created and than run.
I didn't know if code execution of the procedure was faster then running the job.
If the procedure continues executing its code while the job is still running the results will be wrong.
So, I asked if there's some function (like wait) that will stop the procedure until the job was completed.
Now all works good thanks to Michel post.
Regards.
|
|
|
|