2 Replies Latest reply: Jun 22, 2006 10:06 AM by 442199 RSS

    Complex Join - with Association Table

    woodsmithnh
      We're trying to write a rule that will execute on conditions that happen over a join through an association table and are having trouble. The rule never fires. Ideas?

      Here's a complete example.

      ----------------------
      CREATE TABLE PERMIT_ACTION_T
      (
      ACTION_ID NUMBER NOT NULL,
      PERMIT_FOLDER_ID NUMBER NULL,
      CREATION_DATE DATE NOT NULL,
      CREATOR VARCHAR2(100 BYTE) NOT NULL,
      LINK_ID NUMBER NULL,
      PERMIT_START_DATE DATE NULL,
      PERMIT_END_DATE DATE NULL,
      PERMIT_TYPE VARCHAR2(100 BYTE) NULL,
      PERMIT_NOTICE_DATE DATE NULL
      );

      CREATE TABLE LOCATION_AA
      (
      LOC_ID NUMBER NULL,
      ACTION_ID NUMBER NULL
      );

      CREATE TABLE LOCATION_T
      (
      LOC_ID NUMBER NOT NULL,
      LOCATION_NAME VARCHAR2(500 BYTE) NULL,
      LOCATION_GEOM MDSYS.SDO_GEOMETRY NULL
      );

      begin
      DBMS_RLMGR.CREATE_EVENT_STRUCT (event_struct => 'AATest');

      DBMS_RLMGR.ADD_ELEMENTARY_ATTRIBUTE(
      event_struct => 'AATest',
      attr_name => 'r_pat',
      tab_alias => RLM$TABLE_ALIAS('permit_action_t'));

      DBMS_RLMGR.ADD_ELEMENTARY_ATTRIBUTE(
      event_struct => 'AATest',
      attr_name => 'r_locaa',
      tab_alias => RLM$TABLE_ALIAS('location_aa'));

      DBMS_RLMGR.ADD_ELEMENTARY_ATTRIBUTE(
      event_struct => 'AATest',
      attr_name => 'r_loct',
      tab_alias => RLM$TABLE_ALIAS('location_t'));


      DBMS_RLMGR.CREATE_RULE_CLASS (
      rule_class => 'AATestRC',
      event_struct => 'AATest',
      action_cbk => 'AATestCBK',
      actprf_spec => 'actionType VARCHAR2(40), actionParam VARCHAR2(100), ruleCategory varchar2(100)',
      rlcls_prop => '<composite/>');
      END;
      /

      INSERT INTO AATestRC (rlm$ruleid, actionType, actionParam, ruleCategory, rlm$rulecond)
      VALUES (1, 'Location violated - complex join','AA Test','Complex Assoction Check on Location',
      '<condition>
      <and join="r_pat.action_id=r_locaa.action_id and r_locaa.loc_id = r_loct.loc_id" >
      <object name="r_pat">
      permit_type = ''403b''
      </object>
      <object name="r_loct">
      location_name = ''Test''
      </object>
      </and>
      </condition>');

      insert into permit_action_t (action_id, creation_date,creator, permit_type) values (1,sysdate,'me', '403b');
      insert into location_aa (action_id, loc_id) values (1, 1);
      insert into location_t (loc_id, location_name, location_geom) values (1, 'Test', mdsys.sdo_geometry(2001,8265,mdsys.sdo_point_type(-75,45,null),null,null));

      commit;

      CREATE OR REPLACE procedure "AATESTCBK" ( "R_PAT" ROWID,
      "R_LOCAA" ROWID,
      "R_LOCT" ROWID,
      rlm$rule "AATESTRC"%ROWTYPE) is
      msg varchar2(200);
      begin
      msg := 'Matched rule: '
      ||rlm$rule.rlm$ruleid||chr(10)||
      '-> Recommended Action : '||chr(10)||
      ' Action Type ['||rlm$rule.actionType||
      ']'||chr(10)|| ' Action Parameter ['||
      rlm$rule.actionParam||']';

      dbms_output.put_line (msg||chr(10));
      end;
      /

      CREATE OR REPLACE Procedure test(p_action_id in number) is

      testrowid rowid;
      testrowid2 rowid;

      BEGIN


      select rowid into testrowid from permit_action_t t where t.action_id = p_action_id;

      dbms_rlmgr.process_rules (
      rule_class => 'AATestRC',
      event_type => 'permit_action_t',
      event_inst => testrowid); -- rowid of a row ---

      dbms_output.put_line('rowid '||testrowid||' checked');
      END;
      /

      set serveroutput on
      execute test(p_action_id=>1);