Unable To Create Rules With Many Conditions IN OSM Administrator - Getting Error ORA-01461 (Doc ID 985462.1)

Symptoms

User would like to check up to 80 conditions using 1 rule. User would like to create a rule that checks if a specified field contains any of the 80 possible values.

Eg: Field name is Product_No. The rule will be Product_no = 1 OR Product_no = 2 OR Product_no = 3 etc up to 80.

However when  about 30 conditions are reached and a save of the rule is attempted, an error is thrown out with the following message: An Oracle database problem has occurred. OMS will shut down.

Error message:

In function: ExecSQL Details: insert into om_rule_source (rule_id, rule_text, rule_source, CARTRIDGE_ID ) values (10456, 'begin if (om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','a')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','v')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','c')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','d')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','e')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','f')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','g')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','h')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','i')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','j')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','k')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','l')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','m')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','n')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','o')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','p')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','q')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','r')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','s')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','t')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','u')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','v')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','w')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','x')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','y')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','z')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','1')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','2')='true' And om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','5')='true' Or om_ordinst_value_pkg.om_contains_text(:order_seq_id,25080,:coord_set_id,'=','6')='true' ) then :rule_result := 'true'; else :rule_result := 'false'; end if; end;', 'ROWCOUNT:30!OBJECT:DATA;RELATION:0;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:a;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:v;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:c;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:d;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:e;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:f;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:g;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:h;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:i;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:j;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:k;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:l;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:m;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:n;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:o;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:p;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:q;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:r;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:s;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:t;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:u;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:v;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:w;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:x;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:y;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:z;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:1;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:2;!OBJECT:DATA;RELATION:1;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:5;!OBJECT:DATA;RELATION:2;ORDERNODEID:25080;DICTID:12939;BNAME:service/waitlist_service_order_created_flag;OP:=;ATTRTYPE:atText;VALUE:6;!', 5) ORA-01461: can bind a LONG value only for insert into a LONG column

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.