Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DEFINE_CHAIN_RULE.CONDITION - Multiple with SQL Syntax

575402May 27 2008 — edited May 28 2008
Is 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 25 2008
Added on May 27 2008
3 comments
3,296 views