DEFINE_CHAIN_RULE.CONDITION - Multiple with SQL Syntax
575402May 27 2008 — edited May 28 2008Is it possible to make the condition based on an event such as a particular Step succeeding AND based on a particular hour or day? I'm able to create a condition that evaluates day of the week (to_char(sysdate,'DY') <> 'SAT') which works fine on its own and runs the appropriate steps and programs. I can also have a condition that a particular Step succeeds. Obviously, I can have a condition where several steps succeed. However, when I add the two conditions together:
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'C_NIGHTLY_PROCESSING'
,condition => 'ASP_STEP_5 Succeeded and to_char(sysdate,''DY'') <> ''SAT'''
,action => 'start ASP_STEP_9'
,rule_name => 'ASP_RULE_2');
END;
/
However, when I run the above I get the following errors:
ORA-25448: rule MDSDBA.ASP_RULE_2 has errors
ORA-00920: invalid relational operator
ORA-06512: at "SYS.DBMS_ISCHED", line 1244
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1257
ORA-06512: at line 3
In my environment, this chain is executed multiple times a day based on two distinct schedules (AM versus PM). If Saturday, additional parms are added when the proc is executed. I've tried putting the to_char within its own parens, I've tried using a comma instead of the and, etc.
If there is no solution to the above, I could write my CREATE_PROGRAM call as a PL/SQL block, have it determine the day of the week, and then pass in the appropriate parms. Any suggestions would be appreciated. Thanks.