Skip to Main Content
  • Questions
  • Unable to run the job which is already created

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, sailesh.

Asked: June 08, 2017 - 12:25 pm UTC

Last updated: June 09, 2017 - 12:48 pm UTC

Version: 11.2.0.3.14

Viewed 1000+ times

You Asked

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

and Chris said...

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks Team

sailesh jaiswal, June 09, 2017 - 9:41 am UTC

Hi Team,

Thanks for the detailed explaination.

The actual requirement is :

We were checking for a way where in we can call the shell script from PL/SQL block or job, then I came across this.

we want to integrate this PL/SQL block with oracle APEX so that the users can trigger it from GUI

But here if we define repeat interval as FREQ=DAILY, it may run daily. In our case it should run only when we call it by passing parameters through ORACLE APEX.

Is there any way we could achieve this task ?

Thanks,
Sailesh
Chris Saxon
June 09, 2017 - 12:48 pm UTC

When you submit the job, set:
enabled => false

Then enable it when you want to run it.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library