Toad World® Forums

TOAD error while changing the STATE of a SUBCHAIN step


#1

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


#2

I can reproduce this. It doesn’t look like I was doing anything wrong according to Oracle docs, but I did some googling and found this:

community.oracle.com/…/1771413

which means that in the ‘alter running chain’ command, I need to specify the job_name as this (where JDORLON is my schema name):

begin

sys.dbms_scheduler.alter_running_chain(

job_name => ‘JDORLON.JOB1.STEP1’,

step_name => ‘STEP2’,

attribute => ‘STATE’,

value => ‘NOT_STARTED’);

end;

That seems to do the trick. I’ll see what I can do with it in Toad. Thanks.


#3

Update: This has been fixed in 12.6.0.9 beta.