Home » SQL & PL/SQL » SQL & PL/SQL » dbms_job from a procedure (9.2.0.2)
dbms_job from a procedure [message #356371] Thu, 30 October 2008 09:32 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Table Variables
Next Topic: Select tables with more than one row
Goto Forum:
  


Current Time: Sat Feb 08 20:15:27 CST 2025