Hi Tom,
I need your help in fixing this.
I have created the job which should call a shell script when triggered. But unfortunately I am getting error as test_job must be a job.
Below are the scripts which I have used to create the job.
Let me know if I have missed something here.
Thanks in advance.
SYS>BEGIN
2 DBMS_SCHEDULER.create_program
3 (
program_name => 'test',
program_type => 'EXECUTABLE',
program_action => '/scripts/AUTOMATION/GENERIC_REFRESH_SCRIPT/mail.sh',
4 5 6 7 number_of_arguments => 0,
enabled => TRUE,
8 9 comments => 'Test Program'
);
10 11 end;
/ 12
PL/SQL procedure successfully completed.
SYS>BEGIN
DBMS_SCHEDULER.create_schedule (
schedule_name => 'test_schedule',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
comments => 'Test.');
END;
/
2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.
SYS>BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS (
job_class_name => 'test_job_class'
);
END;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
SYS>BEGIN
2 DBMS_SCHEDULER.create_job (
3 job_name => 'TEST_JOB',
program_name => 'test',
4 5 schedule_name => 'test_schedule',
job_class => 'test_job_class',
6 7 enabled => TRUE,
8 comments => 'Job defined by an existing program and schedule and assigned toa job class.');
END;
9 10 /
PL/SQL procedure successfully completed.
SYS>select JOB_NAME , STATE from dba_scheduler_jobs where JOB_NAME like 'TEST_JOB' or JOB_NAME like 'test_job';
no rows selected
SYS>BEGIN
DBMS_SCHEDULER.RUN_JOB(
JOB_NAME => 'TEST_JOB',
USE_CURRENT_SESSION => FALSE);
END;
/ 2 3 4 5 6
BEGIN
*
ERROR at line 1:
ORA-27475: "SYS.TEST_JOB" must be a job
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 2
Kind Regards,
Sailesh
You've not defined a repeat interval in your schedule!
This means the job will run once then drop itself. If you look in user_scheduler_job_run_details you should see you job having just run:
begin
dbms_scheduler.create_program(
program_name => 'test',
program_type => 'EXECUTABLE',
program_action => '/scripts/AUTOMATION/GENERIC_REFRESH_SCRIPT/mail.sh',
number_of_arguments => 0,
enabled => true,
comments => 'Test Program'
);
end;
/
begin
dbms_scheduler.create_schedule(
schedule_name => 'test_schedule',
start_date => systimestamp,
repeat_interval => null,
end_date => null,
comments => 'Test.'
);
end;
/
begin
dbms_scheduler.create_job_class(
job_class_name => 'test_job_class'
);
end;
/
begin
dbms_scheduler.create_job(
job_name => 'TEST_JOB',
program_name => 'test',
schedule_name => 'test_schedule',
job_class => 'test_job_class',
enabled => true,
comments => 'Job defined by an existing program and schedule and assigned toa job class.'
);
end;
/
select * from (
select job_name, status from user_scheduler_job_run_details
order by log_date desc
)
where rownum = 1;
JOB_NAME STATUS
TEST_JOB FAILED
If you want it to stick around, you need to define this. Or submit it disabled so you can enable & start it when you want.
begin
dbms_scheduler.set_attribute(
'test_schedule',
'repeat_interval',
'FREQ=DAILY'
);
end;
/
begin
dbms_scheduler.create_job(
job_name => 'TEST_JOB',
program_name => 'test',
schedule_name => 'test_schedule',
job_class => 'test_job_class',
enabled => true,
comments => 'Job defined by an existing program and schedule and assigned toa job class.'
);
end;
/
select job_name, next_run_date
from dba_scheduler_jobs
where job_name like 'TEST_JOB'
or job_name like 'test_job';
JOB_NAME NEXT_RUN_DATE
TEST_JOB 10-JUN-2017 01.56.27.800000000 -07