Home » SQL & PL/SQL » SQL & PL/SQL » Wait whila a job is running
Wait whila a job is running [message #305711] Tue, 11 March 2008 12:10 Go to next message
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 Go to previous messageGo to next message
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 #305713 is a reply to message #305711] Tue, 11 March 2008 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
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.

Maybe the best for you is to investigate in dbms_alert to signal between your processes.

Regards
Michel
Re: Wait whila a job is running [message #305738 is a reply to message #305711] Tue, 11 March 2008 15:11 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, Michel points out your need to do some research. Not to be picky, but:

There is no DBMS_JOBS database package supplied by Oracle. There is only DBMS_JOB.

So either this is a spelling error and you ment DBMS_JOB in which case Michel is on the money once again, or someone in your company created a new package of their own and foolishly called it DBMS_JOBS so as to intentionally confuse people.

I would ask, which is it?

As Michel indicates, this would be an excellent time for you to find out about serialization of events in the database.

Quote:
DBMS_ALERT (has a couple of "WAIT" procedures you can use to wait for events to happen)
DBMS_LOCK (lets you create your own locks and thus queue up on a resource)
ADVANCED QUEUING (essentially message between processes, this is more involved, but also potentially more fun)

There might be other options but the lights dim. You can start your research here:


http://youngcow.net/doc/oracle10g/appdev.102/b14258/d_alert.htm
http://youngcow.net/doc/oracle10g/appdev.102/b14258/d_lock.htm
http://www.akadia.com/services/ora_advanced_queueing.html

Good luck, Kevin
Re: Wait whila a job is running [message #305975 is a reply to message #305711] Wed, 12 March 2008 08:13 Go to previous messageGo to next message
callimaco0082
Messages: 18
Registered: March 2008
Junior Member
Thanks to all!

I found the solution in your suggestions.
Re: Wait whila a job is running [message #305977 is a reply to message #305975] Wed, 12 March 2008 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Which one? Please post it for future readers.

Regards
Michel
Re: Wait whila a job is running [message #305986 is a reply to message #305711] Wed, 12 March 2008 08:34 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
It always amazes me how so many posters think it is about them. Come on callimaco0082, what did you do to solve your problem. Other people will be reading this thread too looking for answers. You have an opportunity to help them now. So, post the solution you choose and tell us why you picked it.

Kevin
Re: Wait whila a job is running [message #305988 is a reply to message #305711] Wed, 12 March 2008 08:36 Go to previous messageGo to next message
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 #306001 is a reply to message #305711] Wed, 12 March 2008 09:32 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thankyou for coming back. But now I am confused. You originally posted this comment:

Quote:
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.


Michel pointed out that if you were using dbms_job.run then the main procedure would have to wait. This means the original description of the problem you gave in fact could not be happening. So... was there really a job that continued to run? Please explain and I will be happy. I must be missing something.

If this was just a mis-communication and in fact all you needed was confirmation of DBMS_JOB.RUN and its behaviour then all is well. I am not trying to be hard here, just trying to get a clear conclusion to the thread. For example, we still don't know if DBMS_JOB(s) was a typo or if there is in fact a home grown package you were using called DBMS_JOBS rather than using the Oracle supplied package DBMS_JOB.

Thanks, Kevin
Re: Wait whila a job is running [message #306007 is a reply to message #305711] Wed, 12 March 2008 09:48 Go to previous messageGo to next message
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.
Re: Wait whila a job is running [message #306025 is a reply to message #305711] Wed, 12 March 2008 10:28 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Excellent, thanks budd.

Kevin
Previous Topic: Scheduled Job doesn't start
Next Topic: concatenation of a string and an autoincremented sequence
Goto Forum:
  


Current Time: Mon Feb 10 03:13:45 CST 2025