This discussion is archived
8 Replies Latest reply: Oct 28, 2008 5:34 AM by 442199 RSS

Problem in creating Rule Class

637711 Newbie
Currently Being Moderated
I am trying to build a Rules Application..I am totally a novice in this..Following the examples given in documentation i am trying to do it..
These are the steps i have donr till now..
CREATE or REPLACE TYPE Account_History AS OBJECT (
Account_Id VARCHAR2(20),
Transaction_Month DATE,
AvgCash_NonCashTxnRatio FLOAT);

CREATE or REPLACE TYPE Account_Current AS OBJECT (
Account_Id VARCHAR2(20),
Transaction_Month DATE,
Cash_oNonCashTxnRatioCur_Mnth FLOAT);

BEGIN
 DBMS_RLMGR.CREATE_EVENT_STRUCT (EVENT_STRUCT => 'AccntCompEvent'); 
DBMS_RLMGR.ADD_ELEMENTARY_ATTRIBUTE (
                       EVENT_STRUCT => 'AccntCompEvent',
                       ATTR_NAME => 'Acnt_Hist',
                       TAB_ALIAS => RLM$TABLE_ALIAS('STATISTICS_MV'));
DBMS_RLMGR.ADD_ELEMENTARY_ATTRIBUTE (
                       EVENT_STRUCT => 'AccntCompEvent',
                       ATTR_NAME => 'Acnt_Curr',
                       TAB_ALIAS => RLM$TABLE_ALIAS('CURRENT_RATIO'));
END;


BEGIN
   dbms_rlmgr.create_rule_class (
               rule_class    => 'Account_Rules',
               event_struct  => 'AccntCompEvent',
               action_cbk    => 'RuleExecute',
               rslt_viewnm   => 'RuleExecuteView',
               actprf_spec   => 'Action1  VARCHAR2(20),
                                 Action2  VARCHAR2(20)',
               rlcls_prop    => '<composite equal="Acnt_Hist.Account_Id, Acnt_Curr.Account_Id"/>');
END;
But when i am trying to create the Rule Class, i get error saying that
Error report:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "EXFSYS.DBMS_RLMGR", line 686
ORA-06512: at line 2
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:
What is the problem in the code..Can anyone help me fix it..Please..
I want to form a rule which checks if the Cash_oNonCashTxnRatioCur_Mnth is greater than 5 times the value in AvgCash_NonCashTxnRatio..how do i form a rule for this...Please give me some suggestions..
Can anyone suggest me some good links where i can get more about Oracle Rules Manager..
  • 1. Re: Problem in creating Rule Class
    442199 Explorer
    Currently Being Moderated
    Hello,

    Which version of RDBMS are you using ? I tried the script on 10.2.0.4 and 11.1 with no problem. If you are not on these version, can you try shortening the attribute name and see if the problem goes away.

    For the given rule class, the rule condition you mentioned is as follows (I assumed you meant to define the tables instead of the object types).
      <condition>
         <and join=" Acnt_Hist.AvgCash_NonCashTxnRatio*5 < Acnt_Curr.Cash_oNonCashTxnRatioCur_Mnth" >
            <object name="Acnt_Hist"/>
            <object name="Acnt_Curr"/>
         </and>
      </condition>
    The Rules Manager documentation is a good start. You can also find an UI tool in the downloads section at
    http://www.oracle.com/technology/products/database/rules_manager/index.html

    Hope this helps,
    -Aravind.
  • 2. Re: Problem in creating Rule Class
    637711 Newbie
    Currently Being Moderated
    I am using Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production...
    How do u use the Rule UI...There were instructions about placing JAR files... After putting that JAR file, will i be able to Rules Manager in Enterprise DB Console...
  • 3. Re: Problem in creating Rule Class
    637711 Newbie
    Currently Being Moderated
    I did try the rule, which u had mentioned but when i try to insert the rule to my rule table, it gives error saying that
    Error starting at line 1 in command:
    INSERT INTO ACCOUNT_RULES (rlm$ruleid, RLM$RULEDESC, rlm$rulecond)
    VALUES ('Rule1', 'DepositFollowedbyATMWithdrawalatODDTime',
    '<condition>
         <and join=" Acnt_Stats_Hist.AvgCash_NonCashTxnRatio*5 < Acnt_Stats_Curr.Cash_oNonCashTxnRatioCur_Mnth" >
            <object name="Acnt_Stats_Hist"/>
            <object name="Acnt_Stats_Curr"/>
         </and>
      </condition>
    ')
    Error report:
    SQL Error: ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00244: invalid use of less-than ('<') character (use &lt;)
    Error at line 2
    ORA-06512: at "RULES_MANAGER.RLM$VALIDATE_RULE_107363", line 92
    ORA-04088: error during execution of trigger 'RULES_MANAGER.RLM$VALIDATE_RULE_107363'
    31011. 00000 -  "XML parsing failed"
    *Cause:    XML parser returned an error while trying to parse the document.
    *Action:   Check if the document to be parsed is valid.
    So i changed the '<' symbol in the condition and replaced with ?lt and also tried &lt. But for both it gave me xml parser error... How do i fix it???
    Error starting at line 1 in command:
    INSERT INTO ACCOUNT_RULES (rlm$ruleid, RLM$RULEDESC, rlm$rulecond)
    VALUES ('Rule1', 'DepositFollowedbyATMWithdrawalatODDTime',
    '<condition>
         <and join=" Acnt_Stats_Hist.AvgCash_NonCashTxnRatio*5 ?lt Acnt_Stats_Curr.Cash_oNonCashTxnRatioCur_Mnth" >
            <object name="Acnt_Stats_Hist"/>
            <object name="Acnt_Stats_Curr"/>
         </and>
      </condition>
    ')
    Error report:
    SQL Error: ORA-00911: invalid character
    ORA-06512: at "EXFSYS.EXF$VALIDATE_196", line 18
    ORA-04088: error during execution of trigger 'EXFSYS.EXF$VALIDATE_196'
    ORA-06512: at "RULES_MANAGER.RLM$VALIDATE_RULE_107363", line 92
    ORA-04088: error during execution of trigger 'RULES_MANAGER.RLM$VALIDATE_RULE_107363'
    00911. 00000 -  "invalid character"
    *Cause:    identifiers may not start with any ASCII character other than
               letters and numbers.  $#_ are also allowed after the first
               character.  Identifiers enclosed by doublequotes may contain
               any character other than a doublequote.  Alternative quotes
               (q'#...#') cannot use spaces, tabs, or carriage returns as
               delimiters.  For all other contexts, consult the SQL Language
               Reference Manual.
    *Action:
    
    Error starting at line 1 in command:
    INSERT INTO ACCOUNT_RULES (rlm$ruleid, RLM$RULEDESC, rlm$rulecond)
    VALUES ('Rule1', 'DepositFollowedbyATMWithdrawalatODDTime',
    '<condition>
         <and join=" Acnt_Stats_Hist.AvgCash_NonCashTxnRatio*5 &lt Acnt_Stats_Curr.Cash_oNonCashTxnRatioCur_Mnth" >
            <object name="Acnt_Stats_Hist"/>
            <object name="Acnt_Stats_Curr"/>
         </and>
      </condition>
    ')
    Error report:
    SQL Error: ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00244: invalid use of less-than ('<') character (use &lt;)
    Error at line 2
    ORA-06512: at "RULES_MANAGER.RLM$VALIDATE_RULE_107363", line 92
    ORA-04088: error during execution of trigger 'RULES_MANAGER.RLM$VALIDATE_RULE_107363'
    31011. 00000 -  "XML parsing failed"
    *Cause:    XML parser returned an error while trying to parse the document.
    *Action:   Check if the document to be parsed is valid.
  • 4. Re: Problem in creating Rule Class
    442199 Explorer
    Currently Being Moderated
    Hello,



    The escape sequence for '&lt;' is '& l t ;' with ';' at the end (with no spaces). Since the rule condition itself is XML all special characters have to be escaped.



    The UI tool I was referring to is the EM style tool that can be installed on an OC4J instance. The zip file in the downloads section has all the necessary instructions to install. The documentation for the tool is available at http://www.oracle.com/technology/products/database/rules_manager/rlmui_viewlet_swf_0.html



    Hope this helps,

    -Aravind.
  • 5. Re: Problem in creating Rule Class
    637711 Newbie
    Currently Being Moderated
    Thanks,
    If i have to create a complex rule how do i do it?

    For eg.,

    IF (IF(A>b) AND (C>D)) AND (E>=0.8*G)

    How do i compose such type of rules?? here the result of the inner expression (IF(A>b) AND (C>D)) is ANDed with (E>=0.8*G)
    How to create rules for such types....
  • 6. Re: Problem in creating Rule Class
    442199 Explorer
    Currently Being Moderated
    Hello,

    If A, B, C, D and E in your example are attributes of a single event, you can use the SQL-WHERE clause syntax. XML syntax is used to correlated events into patterns - conjunction, disjunction, negation, aggregation etc. The application developer's guide has many examples that can help you get started. You can also find the rules manager demo script (rdbms/demo/ruldemo.sql) in the companion CD.

    Regards,
    -Aravind.
  • 7. Re: Problem in creating Rule Class
    637711 Newbie
    Currently Being Moderated
    Please tell me, if i am creating the rules correctly ot not.. As u suggested to use &lt; instead of '<'.. When i execute the following, it asks me to enter a value for LT.. When i enter <, it throws me a XML Parser error..as follows:
    INSERT INTO ACCOUNT_RULES (rlm$ruleid, RLM$RULEDESC, rlm$rulecond)
    VALUES ('Rule1', 'DepositFollowedbyATMWithdrawalatODDTime',
    '<condition>
         <and join="Acnt_Stats_Hist.AvgCash_NonCashTxnRatio * 5 &lt; Acnt_Stats_Curr.CASH_TO_NONCASH_RATIO_CURR">
            <object name="Acnt_Stats_Hist"/>
            <object name="Acnt_Stats_Curr"/>
         </and>
      </condition>
    ');
    Error is
    Error starting at line 1 in command:
    INSERT INTO ACCOUNT_RULES (rlm$ruleid, RLM$RULEDESC, rlm$rulecond)
    VALUES ('Rule1', 'DepositFollowedbyATMWithdrawalatODDTime',
    '<condition>
         <and join="Acnt_Stats_Hist.AvgCash_NonCashTxnRatio * 5 &lt; Acnt_Stats_Curr.CASH_TO_NONCASH_RATIO_CURR">
            <object name="Acnt_Stats_Hist"/>
            <object name="Acnt_Stats_Curr"/>
         </and>
      </condition>
    ')
    Error report:
    SQL Error: ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00244: invalid use of less-than ('<') character (use &lt;)
    Error at line 2
    ORA-06512: at "RULES_MANAGER.RLM$VALIDATE_RULE_107363", line 92
    ORA-04088: error during execution of trigger 'RULES_MANAGER.RLM$VALIDATE_RULE_107363'
    31011. 00000 -  "XML parsing failed"
    *Cause:    XML parser returned an error while trying to parse the document.
    *Action:   Check if the document to be parsed is valid.
    This is the code that i am using to create the rules application:
    --STEP 1
    create or replace
    TYPE Account_Details_Curr AS OBJECT (
    ACCOUNT_ID VARCHAR2(20),
    CURRENT_TRANSACTION_DATE DATE,
    TransactionType_CURR VARCHAR2(10),
    TRANSACTION_CHANNEL_CURR VARCHAR2(10),
    TRANSACTION_MODE_CURR VARCHAR2(10),
    ABNORMALITY_REASON VARCHAR2(15),
    SUSPICIOUS_FUND_FLOW_SCORE FLOAT,
    ACCOUNT_AGE FLOAT,
    INACTIVITY_WEIGHT FLOAT,
    CREDIT_AMT_BURST_SCORE FLOAT,
    DEBIT_AMT_BURST_SCORE FLOAT,
    TRANSACTION_AMOUNT_CURR FLOAT,
    Cumm_CshAmnt_RltdAccnt_Day FLOAT,
    ACCOUNT_TURNOVER FLOAT);
    
    create or replace
    TYPE Account_Stats_Curr AS OBJECT (
    ACCOUNT_ID VARCHAR2(20),
    CUMM_DEPOSITTXN_CNT_CURR FLOAT,
    CUMM_DEPOSITTXN_AMNT_CURR FLOAT,
    CUMM_WITHDRAWALTXN_CNT_CURR FLOAT,
    CUMM_WITHDRAWALTXN_AMNT_CURR FLOAT,
    CUMM_ATM_DEPOSITTXN_CNT_CURR FLOAT,
    CUMM_ATM_DEPOSITTXN_AMNT_CURR FLOAT,
    CUMM_ATM_WITH_TXN_CNT_CURR FLOAT,
    CUMM_ATM_WITH_TXN_AMNT_CURR FLOAT,
    CUMM_CASH_DEPOSITTXN_CNT_CURR FLOAT,
    CUMM_CASH_DEPOSITTXN_AMNT_CURR FLOAT,
    CUMM_CASH_WITH_TXN_CNT_CURR FLOAT,
    CUMM_CASH_WITH_TXN_AMNT_CURR FLOAT,
    CUMM_CLR_DEPOSITTXN_CNT_CURR FLOAT,
    CUMM_CLR_DEPOSITTXN_AMNT_CURR FLOAT,
    CUMM_CLR_WITH_TXN_CNT_CURR FLOAT,
    CUMM_CLR_WITH_TXN_AMNT_CURR FLOAT,
    CUMM_TRS_DEPOSITTXN_CNT_CURR FLOAT,
    CUMM_TRS_DEPOSITTXN_AMNT_CURR FLOAT,
    CUMM_TRS_WITH_TXN_CNT_CURR FLOAT,
    CUMM_TRS_WITH_TXN_AMNT_CURR FLOAT,
    CUMM_TXN_CNT_CURR FLOAT,
    CUMM_TXN_AMNT_CURR FLOAT,
    CUMM_TRANSFER_TXN_CNT_CURR FLOAT,
    CUMM_TRANSFER_TXN_AMNT_CURR FLOAT,
    CUMM_CLEARING_TXN_CNT_CURR FLOAT,
    CUMM_CLEARING_TXN_AMNT_CURR FLOAT,
    CUMM_ATM_TXN_CNT_CURR FLOAT,
    CUMM_ATM_TXN_AMNT_CURR FLOAT,
    CUMM_ATMCASH_DEP_TXN_CNT_CURR FLOAT,
    CUMM_ATMCASH_DEP_TXN_AMNT_CURR FLOAT,
    CUMM_ATMCASH_WITH_TXN_CNT_CURR FLOAT,
    CUMM_ATMCSH_WITH_TXN_AMNT_CURR FLOAT,
    CUMM_ATM_CSH_TOT_TXN_CNT_CURR FLOAT,
    CUMM_ATM_CSH_TOT_TXN_AMNT_CURR FLOAT,
    CUMM_CASH_TXN_CNT_CURR FLOAT,
    CUMM_CASH_TXN_AMNT_CURR FLOAT,
    CREDIT_TO_DEBIT_RATIO_CURR FLOAT,
    DEBIT_TO_CREDIT_RATIO_CURR FLOAT,
    CASHCR_TO_NONCASHCR_RATIO_CURR FLOAT,
    CASH_TO_NONCASH_RATIO_CURR FLOAT);
    
    create or replace
    TYPE Account_Stats_Hist AS OBJECT (
    ACCOUNT_ID VARCHAR2(20),
    AVG_DEPOSITTXN_COUNT FLOAT,
    AVG_DEPOSITTXN_AMOUNT FLOAT,
    AVG_WITHDRAWALTXN_COUNT FLOAT,
    AVG_WITHDRAWALTXN_AMOUNT FLOAT,
    AVG_ATM_DEPOSITTXN_COUNT FLOAT,
    AVG_ATM_DEPOSITTXN_AMOUNT FLOAT,
    AVG_ATM_WITHDRAWALTXN_COUNT FLOAT,
    AVG_ATM_WITHDRAWALTXN_AMOUNT FLOAT,
    AVG_CASH_DEPOSITTXN_COUNT FLOAT,
    AVG_CASH_DEPOSITTXN_AMOUNT FLOAT,
    AVG_CASH_WITHDRAWALTXN_COUNT FLOAT,
    AVG_CASH_WITHDRAWALTXN_AMOUNT FLOAT,
    AVG_CLR_DEPOSITTXN_COUNT FLOAT,
    AVG_CLR_DEPOSITTXN_AMOUNT FLOAT,
    AVG_CLR_WITHDRAWALTXN_COUNT FLOAT,
    AVG_CLR_WITHDRAWALTXN_AMOUNT FLOAT,
    AVG_TRS_DEPOSITTXN_COUNT FLOAT,
    AVG_TRS_DEPOSITTXN_AMOUNT FLOAT,
    AVG_TRS_WITHDRAWALTXN_COUNT FLOAT,
    AVG_TRS_WITHDRAWALTXN_AMOUNT FLOAT,
    AVG_TXN_COUNT FLOAT,
    AVG_TXN_AMOUNT FLOAT,
    AVG_TRANSFER_TXN_COUNT FLOAT,
    AVG_TRANSFER_TXN_AMOUNT FLOAT,
    AVG_CLEARING_TXN_COUNT FLOAT,
    AVG_CLEARING_TXN_AMOUNT FLOAT,
    AVG_ATM_TXN_COUNT FLOAT,
    AVG_ATM_TXN_AMOUNT FLOAT,
    AVG_ATMCASH_DEP_TXN_COUNT FLOAT,
    AVG_ATMCASH_DEP_TXN_AMOUNT FLOAT,
    AVG_ATMCASH_WITH_TXN_COUNT FLOAT,
    AVG_ATMCASH_WITH_TXN_AMOUNT FLOAT,
    AVG_ATM_CSH_TOT_TXN_COUNT FLOAT,
    AVG_ATM_CSH_TOT_TXN_AMOUNT FLOAT,
    AVG_CASH_TXN_COUNT FLOAT,
    AVG_CASH_TXN_AMOUNT FLOAT,
    AVG_CREDIT_TO_DEBIT_RATIO FLOAT,
    AVG_DEBIT_TO_CREDIT_RATIO FLOAT,
    AVG_CASHCR_TO_NONCASHCR_RATIO FLOAT,
    AVG_CASH_TO_NONCASH_RATIO FLOAT);
    
    CREATE or REPLACE TYPE AccntsCompEvent AS OBJECT (Acnt_Details Account_Details_Curr,
                                                      Acnt_Stats_Curr Account_Stats_Curr,
                                                      Acnt_Stats_Hist Account_Stats_Hist);
    
    --*************************************************************************************************
    BEGIN
    dbms_rlmgr.create_rule_class (
    rule_class => 'Account_Rules',
    event_struct => 'AccntsCompEvent',
    action_cbk => 'Rules_Execution',
    rlcls_prop => '<composite equal="Acnt_Details.ACCOUNT_ID, Acnt_Stats_Curr.ACCOUNT_ID,Acnt_Stats_Hist.ACCOUNT_ID"/>');
    END;
    
    --*************************************************************************************************
    --STEP 3
    INSERT INTO ACCOUNT_RULES (rlm$ruleid, RLM$RULEDESC, rlm$rulecond)
    VALUES ('Rule1', 'DepositFollowedbyATMWithdrawalatODDTime',
    '<condition>
         <and join="Acnt_Stats_Hist.AvgCash_NonCashTxnRatio * 5 &lt; Acnt_Stats_Curr.CASH_TO_NONCASH_RATIO_CURR">
            <object name="Acnt_Stats_Hist"/>
            <object name="Acnt_Stats_Curr"/>
         </and>
      </condition>
    ');
    I am getting this error and i am not able to proceed further.. Please help me...
  • 8. Re: Problem in creating Rule Class
    442199 Explorer
    Currently Being Moderated
    Hello,

    I am not sure why the escape sequence for < is not working. You can try to insert the text into an XMLType column (outside the context of rules manager) and try this out. Rules Manager relies on XML support in the database to process the rule conditions.

    But a quick fix for you is to flip the predicate around and write it as a 'greater than relationship. '>' symbol in an XML attribute value does not need an escape sequence.

    Regards,
    -Aravind.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points