dbms_job from a procedure [message #356371] |
Thu, 30 October 2008 09:32  |
robwolters
Messages: 3 Registered: November 2005
|
Junior Member |
|
|
Hi,
I have written a procedure called by a DBMS_JOB. The DBMS_JOB is not run at regular interval but instead is re-scheduled inside the procedure. For some reason it seems to do the first update that I have in the block but does not seem to do the second commit which follows the DBMS_JOB.NEXT_DATE. This means that the job disappears from the DBA_JOBS table. I can't understand why.
This is basically what the code looks like minus some unimportant bits:
CASE
WHEN v_test = '1' THEN
v_timecor:=7200;
update parameter
set waarde = v_timecor
where param_code = 'TIMECOR';
commit;
.
.
.
dbms_job.next_date(26, to_date(v_vmaand,'DD-MON-YYYY HH:MI:SS'));
commit;
WHEN v_test....
Hope someone can help.
Cheers....
|
|
|
Re: dbms_job from a procedure [message #356408 is a reply to message #356371] |
Thu, 30 October 2008 11:25   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
How do you know it has executed the dbms_job but it didn't do a commit? Is there any way you can prove it to us by writing a test case.
Regards
Raj
|
|
|
Re: dbms_job from a procedure [message #356593 is a reply to message #356408] |
Fri, 31 October 2008 06:24  |
robwolters
Messages: 3 Registered: November 2005
|
Junior Member |
|
|
Hi Raj,
Good question. Basically I have simulated this from the command line. When I run the dbms_job.next_date manually from sqlplus and commit the updated job appears in the dba_jobs table. In the same situation I do not commit, it's gone. So I am presuming the same thing is happening here.
I'm no pl/sql guru by the so if there is anyway of doing further debugging I'm all ears.
Cheers,
Rob.
|
|
|