Using dbms_scheduler to run a program

Asked Jul 15 '15 at 08:40

Active Jul 15 '15 at 08:45

Viewed 1,300 times

I am trying to run a program using scheduler job.My code goes as follows:

--Program
BEGIN
  dbms_scheduler.create_program (
    program_name    =>  'firstprogram',
    program_type    =>  'PLSQL_BLOCK',
    program_action  =>  'BEGIN Execute immediate ''Create table xyz(id int, name varchar2(10))''; end;',
    enabled         =>  TRUE,
    comments        =>  'First program');

END;
/
--Schedule
BEGIN
  DBMS_SCHEDULER.create_schedule (
    schedule_name   => 'firstschedule',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=14; byminute=25',
    end_date        => NULL,
    comments        => 'Repeats daily');
END;
/
--Job
BEGIN
  dbms_scheduler.create_job (
    job_name      =>  'firstjob',
    program_name  =>  'firstprogram',
    schedule_name =>  'firstschedule',
    enabled       =>  TRUE,
    comments      =>  'Use firstprogram and FirstSchedule');
END;
/

All these code blocks compile without showing any errors. But the table xyz is not created in the specified time. Can anyone explain me why? I am using Oracle.

edited Jul 15 '15 at 08:45

Lalit Kumar B

47,486
13
97
124

asked Jul 15 '15 at 08:40

hsuyaa

You have scheduled it to run daily at `14:25`, did it run at least once? – Lalit Kumar B Jul 15 '15 at 08:45
It was 14:23 when I compiled the program. The table xyz should be created at 14:25, but it didn't happen. – hsuyaa Jul 15 '15 at 08:57
Check the view `dba_scheduler_jobs`. – Lalit Kumar B Jul 15 '15 at 09:05
It exists there with the enabled set to TRUE. – hsuyaa Jul 15 '15 at 09:14
There are other columns `RUN_COUNT`, `FAILURE_COUNT` etc. – Lalit Kumar B Jul 15 '15 at 10:04
select * from user_scheduler_job_log; select * from user_scheduler_job_run_details; – Arkadiusz Łukasiewicz Jul 15 '15 at 10:18
I don't understand what you guys are suggesting. – hsuyaa Jul 15 '15 at 10:56
Run the query select * from user_scheduler_job_run_details; and check if there are details regarding your scheduled job. or if you use sql developer check in the scheduler for your scheduled job. If it is not found then it does not created. – Srinivas V. Jul 15 '15 at 13:22
The system time varies with the database server time. Maybe this was the problem? And which time should I use to create the schedule? – hsuyaa Jul 20 '15 at 05:11

Using dbms_scheduler to run a program

0 Answers0