How to resolve ORA-27475: "JOB" must be a program or chain

Question

I have a couple of oracle procedures:

create or replace procedure receive_sms (p_to_date in date) 
is
..
end;

and

create or replace procedure send_sms (p_date in date) 
is
..
end;

and I want to create a chain based on these procedures and add steps to the chain and some rules:

BEGIN
  DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name            =>  'mobile_archive_chain',
   rule_set_name         =>  NULL,
   evaluation_interval   =>  NULL,
   comments              =>  NULL);
END;
/

--- define three steps for this chain. Referenced programs must be enabled.
BEGIN
 DBMS_SCHEDULER.DEFINE_CHAIN_STEP('mobile_archive_chain', 'send',  'inforpilla.send_sms');
 DBMS_SCHEDULER.DEFINE_CHAIN_STEP('mobile_archive_chain', 'receive', 'inforpilla.receive_sms');
END;
/

--- define corresponding rules for the chain.
BEGIN
 DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('mobile_archive_chain', 'TRUE', 'START send');
 DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('mobile_archive_chain', 'send COMPLETED', 'Start receive');
 DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('mobile_archive_chain', 'receive COMPLETED', 'END');
END;
/

--- enable the chain
BEGIN
 DBMS_SCHEDULER.ENABLE('mobile_archive_chain');
END;
/

--- create a chain job to start the chain daily at 1:00 p.m.
BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
   job_name        => 'start_archive',
   job_type        => 'CHAIN',
   job_action      => 'mobile_archive_chain',
   repeat_interval => 'freq=daily;byhour=15;byminute=3;bysecond=0',
   enabled         => TRUE);
END;
/

But checking the log table I see that the job failed with the ERROR code: 27475

SELECT * from USER_SCHEDULER_JOB_RUN_DETAILS ORDER BY LOG_DATE DESC

as suggested I remove the parameters form the procedure, but then I got these errors:

CHAIN_LOG_ID="201095"
CHAIN_LOG_ID="201095",STEP_NAME="RECEIVE", ORA-27475: "TEST.RECEIVE_SMS" must be a PROGRAM OR CHAIN
CHAIN_LOG_ID="201095",STEP_NAME="SEND", ORA-27475: "TEST.SEND_SMS" must be a PROGRAM OR CHAIN

Answer 1

There are couple of thing you need to make sure :

1. It is better to specify full job name in OWNER.JOBNAME. See who is owner of job

   select * from dba_scheduler_jobs where lower(job_name)='start_archive';
   

2. If start_date and repeat_interval are left null, then the job is scheduled to run as soon as the job is enabled.

3. The job runs as the person that schedules the job, you should not be scheduling jobs as SYS if you want to use current user rights!

References :-

Oracle Community

Ask Tom