Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_SCHEDULER
DBMS_SCHEDULER [message #227587] Wed, 28 March 2007 13:25 Go to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
BEGIN
  dbms_scheduler.create_job(
  job_name   => 'BULKLOAD_PRE_PACK',
  job_type   => 'STORED_PROCEDURE',
  job_action => 'BULKLOAD_EXPORT',
  start_date => 'TO_DATE(''28-MAR-2007 18:30:00'',''DD-MON-YYYY HH24:MI:SS'')',
  repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=18; BYMINUTE=30',
  enabled    => FALSE,
  comments   => 'Creates data for Special Charge Voucher');
END;

i have created the above code to schedule the procedure BULKLOAD_EXPORT to run starting today 28-MAR-2007 at 18:30:00 from Mon-Fri at the same time of 18:30:00. when i tried it throws an error "ORA-01858: a non-numeric character was found where a numeric was expected".

[Updated on: Thu, 29 March 2007 07:27]

Report message to a moderator

Re: DBMS_SCHEDULER [message #227589 is a reply to message #227587] Wed, 28 March 2007 13:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
start_date => TO_DATE('28-MAR-2007 18:30:00','DD-MON-YYYY HH24:MI:SS'),
might work better for you
Re: DBMS_SCHEDULER [message #227594 is a reply to message #227587] Wed, 28 March 2007 13:59 Go to previous messageGo to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
Thanks a lot and it works. I thought the start_date is the same as in DBMS_JOB that I have to enclosed it all in single quotes.

BEGIN
  dbms_scheduler.enable('BULKLOAD_PRE_PACK');
END;


I had also enabled the job and check the all_scheduler_jobs to be sure that it is enabled.

[Updated on: Thu, 29 March 2007 07:28]

Report message to a moderator

Re: DBMS_SCHEDULER [message #227596 is a reply to message #227587] Wed, 28 March 2007 14:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
When all else fails, you could alwys simply Read The Fine Manual
http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm
Re: DBMS_SCHEDULER [message #227788 is a reply to message #227596] Thu, 29 March 2007 07:25 Go to previous messageGo to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
The job was scheduled and was supposed to create some files. However, it appears to be it did not run as scheduled. I have looked to the all_scheduler_jobs and it shows that the job was enabled. I have also looked to these two tables all_scheduler_job_log and all_scheduler_job_run_details but there are no rows.
Re: DBMS_SCHEDULER [message #227807 is a reply to message #227788] Thu, 29 March 2007 08:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
check your parameter job_queue_processes
Re: DBMS_SCHEDULER [message #227956 is a reply to message #227807] Fri, 30 March 2007 07:44 Go to previous messageGo to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
Thank you Frank. I have checked and the job_queue_processes is currently set at 10.

[Updated on: Fri, 30 March 2007 07:44]

Report message to a moderator

Re: DBMS_SCHEDULER [message #227979 is a reply to message #227956] Fri, 30 March 2007 09:18 Go to previous message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
I re-submitted the job and it is now working after the job_queue_process is set to 10.
Previous Topic: INSERT DATA USING PROCEDURE IS POSSIBLE (merged)
Next Topic: count difference between tuples
Goto Forum:
  


Current Time: Fri Dec 09 23:13:11 CST 2016

Total time taken to generate the page: 0.06559 seconds