title: DBMS_CHANGE_NOTIFICATION and NEW_REG_START
body: <p>I read some example of how to use DBMS_CHANGE_NOTIFICATION for an asynchronous trigger.</p>
  
  <p>I need to create a trigger for table CONTENT only if STATUS = 1709003.</p>
  
  <p>So I created the following PROCEDURE:</p>
  
  <pre><code>  CREATE OR REPLACE PROCEDURE tables_changed_chnt(ntfnds IN SYS.chnf$_desc) IS
    l_table_name              VARCHAR2(60);
    l_event_type              NUMBER;
    l_numtables               NUMBER;
    status                    NUNBER;
    Row_id                    VARCHAR2(20);
    numrows                   NUMBER;
    token                     varchar2(100);
    subject                   varchar2(100);
    message                   varchar2(100);
    result                    varchar2(100);
    planid                                number := 0;
    userId                                    number := -10000;
    stoponefromatmissing          number := 0;
    timetostopprocess                 number := 0;
    retrials                                number := 2;
    stoponeinvalidaddress         number := 0;
    NL_CONTENT                number:=-1;
    event_status              number:=-1;
  BEGIN
    l_numtables  := ntfnds.numtables;
    l_event_type := ntfnds.event_type;
  
    IF l_event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE THEN
      FOR i IN 1 .. l_numtables LOOP
        l_table_name      := ntfnds.table_desc_array(i).table_name;
  
        if l_table_name = 'CONTENT' then
          IF (bitand(operation_type, DBMS_CHANGE_NOTIFICATION.ALL_ROWS) = 0) THEN
            numrows := ntfnds.table_desc_array(i).numrows;
          ELSE 
            numrows :=0;   /* ROWID INFO NOT AVAILABLE */
          END IF;
         FOR j IN 1..numrows LOOP
           Row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id;
           select NL_CONTENT_ID into NL_CONTENT from CONTENT where rownum=:Row_id;
           select event_status_code into status from CONTENT where rownum=:Row_id; 
            if (status = 1709003) then
              select NL_CONTENT_PLAN_ID into planid from NL_CONTENT where NL_CONTENT_ID=:NL_CONTENT;
              result := workflow_cust.om_start_delivery(token,
                                               subject,
                                               message,
                                               planid,
                                                                       userId,
                                               stoponefromatmissing,
                                               timetostopprocess,
                                               retrials,
                                               stoponeinvalidaddress);
           end if;
        END LOOP;   
        end if    
      END LOOP; 
    END IF;      
  END;
  </code></pre>
  
  <p>I saw that I need to invoke it with something similar to:</p>
  
  <pre><code>DECLARE
    REGDS      SYS.CHNF$_REG_INFO;
    regid      NUMBER;
    cust_id    varchar2(20);
    qosflags   NUMBER;
  BEGIN
  qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE + DBMS_CHANGE_NOTIFICATION.
   QOS_ROWIDS;
  REGDS := SYS.CHNF$_REG_INFO ('SP_EVENT_GENERATE', qosflags, 0,0,0);
  regid := DBMS_CHANGE_NOTIFICATION.NEW_REG_START (REGDS); 
    /* registe the customer table */
  SELECT CUSTID INTO cust_id  FROM TB_CUSTOMER WHERE ROWNUM=1;
  DBMS_CHANGE_NOTIFICATION.REG_END;
  END;
  </code></pre>
  
  <ol>
  <li><p>What do I need to write instead of <code>SELECT CUSTID INTO cust_id  FROM TB_CUSTOMER WHERE ROWNUM=1;</code></p></li>
  <li><p>Is there a way to retrieve the changed object, instead only the rowid - since it will save me time to do the find in the first procedure?</p></li>
  </ol>