CODEWITHSUNDEEP
oracle-databaseoracle11gdbms-scheduler
Bixxman
Bixxman

Reputation: 1

Error ORA-25448 when creating job chain rule using dbms_scheduler.define_chain_rule

When I attempt to create the following oracle chain rule:

BEGIN
   SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
     chain_name   =>   'MIGRATE_EISDB_CHAIN',
     condition    =>   ':MIGRATION_STEP_1.state=''RUNNING'' AND select decode(TIME_COMPLETED, null, 0, 1) from MIGRATION_PROGRESS where MIGRATION_STEP = "MIGRATE_AEP_TO_EP" > 0',
     rule_name    =>   'continue_migration',
     action       =>   'START MIGRATION_STEP_2',
     comments     =>   'continue the migration - step 2');
END;

/

The chain, steps and the first rule were created successfully, but I get this error when creating the second rule with a condition:

Error report -
ORA-25448: rule EISYSNEW1.CONTINUE_MIGRATION has errors
ORA-00936: missing expression
ORA-06512: at "SYS.DBMS_ISCHED", line 1646
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1619
ORA-06512: at line 2
25448. 00000 - "The %s %s.%s has errors."
*Cause: An attempt to load the specified rule or expression failed due
to errors in the rule or expression.
*Action: Check the rule or expression and retry the operation.

Question:

Why is this happening?? Oracle's documentation says that this condition should be legal. What am I missing in the expression for this condition?? FYI: It does create when I omit the part after the 'AND'.

condition    =>   ':MIGRATION_STEP_1.state=''RUNNING'' AND select decode(TIME_COMPLETED, null, 0, 1) from MIGRATION_PROGRESS where MIGRATION_STEP = "MIGRATE_AEP_TO_EP" > 0',

Upvotes: 0

Views: 857

Answers (1)

Luke Woodward
Luke Woodward

Reputation: 64959

I don't see where Oracle's documentation says that the condition is legal. It doesn't look too legal to me.

You need to make the nested SELECT a subquery, by wrapping it in parentheses. Unless MIGRATE_AEP_TO_EP is a column name, you may also want to replace the double-quotes around MIGRATE_AEP_TO_EP to single quotes. As the rule itself is a string literal, you will need to escape each of the single quotes with a second single quote, as you have with RUNNING:

 condition    =>   ':MIGRATION_STEP_1.state=''RUNNING'' AND (select decode(TIME_COMPLETED, null, 0, 1) from MIGRATION_PROGRESS where MIGRATION_STEP = ''MIGRATE_AEP_TO_EP'') > 0',

Upvotes: 0

Related Questions