TOAD works fine when changing the STATE of a CHAIN step but it does not work when changing the state of a subchain.
Here are the steps to replicate error:
First create a scheduler program SP2. The only thing this program does is to raise error.
BEGIN
SYS.DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => ‘SP2’
,program_type => ‘PLSQL_BLOCK’
,program_action => ‘begin raise_application_error(-20001,’‘subchain restart testing…’’); end;’
,number_of_arguments => 0
,enabled => TRUE
);
END;
/
Next, define a chain called CHAIN2. It calls the scheduler program SP2 defined above
BEGIN
SYS.DBMS_SCHEDULER.CREATE_CHAIN (
chain_name => ‘CHAIN2’
);
SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => ‘CHAIN2’
,step_name => ‘STEP2’
,program_name => ‘SP2’);
SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => ‘CHAIN2’
,condition => ‘TRUE’
,action => ‘START “STEP2”’
,rule_name => ‘R1’);
SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => ‘CHAIN2’
,condition => ‘STEP2 SUCCEEDED’
,action => 'END ’
,rule_name => ‘R2’);
SYS.DBMS_SCHEDULER.ENABLE
(name => ‘CHAIN2’);
END;
/
Next, define a chain called CHAIN1. It calls the CHAIN2 defined above.
BEGIN
SYS.DBMS_SCHEDULER.CREATE_CHAIN (
chain_name => ‘CHAIN1’);
SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => ‘CHAIN1’
,step_name => ‘STEP1’
,program_name => ‘CHAIN2’);
SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => ‘CHAIN1’
,condition => ‘TRUE’
,action => ‘START “STEP1”’
,rule_name => ‘R3’);
SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => ‘CHAIN1’
,condition => ‘STEP1 SUCCEEDED’
,action => 'END ’
,rule_name => ‘R4’);
SYS.DBMS_SCHEDULER.ENABLE
(name => ‘CHAIN1’);
END;
/
Next, create a job called JOB1. It calls the CHAIN1 defined above.
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => ‘JOB1’
,start_date => sysdate
,job_type => ‘CHAIN’
,job_action => ‘CHAIN1’
);
END;
/
Next, run the job
BEGIN
SYS.DBMS_SCHEDULER.RUN_JOB(JOB_NAME => ‘JOB1’, USE_CURRENT_SESSION => FALSE);
END;
/
Of Course, the job will error out.
In Toad, Go to CHAIN2 Run Info and try to set STEP2 state to “NOT STARTED”. I get the following error:
ORA-27432: step STEP2 does not exist for chain CHAIN1
ORA-06512: at “SYS.DBMS_ISCHED”, line 4921
ORA-06512: at “SYS.DBMS_ISCHED”, line 2264
ORA-01403: no data found
ORA-06512: at “SYS.DBMS_SCHEDULER”, line 2074
ORA-06512: at line 2