Using a DBMS_PIPE.PACK_MESSAGE and DBMS_PIPE.SEND_MESSAGE in a trigger

Env: Oracle 12c

I’m looking at using Oracle DBMS_PIPE within a table trigger that will be used by many users. The trigger will fire only on a STATUS update as per below:

CREATE OR REPLACE TRIGGER MY_TRG  
AFTER UPDATE OF STATUS ON "MY_TABLE"  
REFERENCING NEW AS NEW OLD AS OLD  
FOR EACH ROW  
declare  
   v_status    INTEGER;  
begin      
    if :OLD.status = 'ERROR' and (:NEW.status = 'OK' or :NEW.status = 'ERROR') then  
       DBMS_PIPE.PACK_MESSAGE(:OLD.id_key);  
       DBMS_PIPE.PACK_MESSAGE(:NEW.status);  

       v_status := DBMS_PIPE.SEND_MESSAGE('MY_PIPE');  

       if v_status != 0 THEN  
         raise_application_error(num => -20002,msg => 'error message on trigger!');  
      end if;  
    end if;
  end;  

The following call will be initiated from an Oracle APEX page process where this can be submitted again by multiple users.

DBMS_PIPE.receive_message(pipename => 'MY_PIPE', timeout  => 10);   

My question is, for each user here, do I need to ensure that the PIPE NAME is specific to each user, so that they only see their messages within their PIPE or can just the one ‘MY_PIPE’ pipe name handle all transactions for multiple users?

If in the case that each user needs their own designated PIPE NAME, how would I do this, if the SEND_MESSAGE('USER_1_PIPE') is triggered from a table trigger, which my receive_message_proc will be unaware of this ‘USER_1_PIPE’ name.

My create pipe is like this:

v_res := DBMS_PIPE.create_pipe(pipename => 'MY_PIPE', private => TRUE);  

I assume I need to tag each user with their own private pipe name – is this correct?

Answer

Private pipes are private the username that created them. If you have multiple people logging on with the same user account, then they are all going to be able to see that pipe.

But perhaps a larger issue is that pipe are not transactional. So the moment that trigger fires, the message get put in the pipe…even if that transaction later rolls back, or fails or anything else that does not finally update the status. Moreover, the pipe message will be sent BEFORE the transaction commits. Another session (receiving that pipe message) will not be able to see changes done until the commit occurs, which can lead to timing inconsistencies.

Perhaps AQ (Advanced Queuing) is an alternative you might want to consider. Messages on a queue by default are transactional, so the message on the queue is then nicely bound to whether your changes to STATUS actually succeed.

The calling application just listens on a queue rather than on a pipe message.