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

Answer 2

The problem could be in the chain, and chain steps names. Try to use upper case for all the job, chain, and chain steps or enclose the names with double quotes: " "

In oracle handles identifiers as case-insensitive and converts to upper case. Hence, oracle was unable to find your chain name. Same applies to chain steps, rules, etc

Also, the procedures "send" and "receive" expect parameters (p_to_date) and these parameters are not passed during chain step calls. This will cause a problem. There's no direct way for passing chain parameters. Have a look at this post: community.oracle.com/message/1459336#1459336

Answer 3

I think you have to use DBMS_SCHEDULER.CREATE_PROGRAM to create a program object and then pass the name of that object into DBMS_SCHEDULER.DEFINE_CHAIN_STEP. It looks like the third argument to DEFINE_CHAIN_STEP is a program name and you define that program name with a call to CREATE_PROGRAM.

Manual url:

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72235