Phantom messages with DBMS

From: Pascal Byrne <pbyrne_at_icada.com>
Date: 14 Nov 2001 06:40:21 -0800
Message-ID: <e45d1299.0111140640.155f1017@posting.google.com>

Hi,

I have an application that uses the DBMS_PIPE package on Linux Oracle 8.0.5. When I write to the pipe from inside a block-level trigger then phantom messages appear in addition to the real message. When the same code is moved to a non-trigger procedure then everything works fine. The phantom message format looks like an old message except that the sysdate and session-ID are always correct!

Firstly the trigger calls procedure rsm_pipe.write_pipe like this:

  rsm_pipe.write_pipe('TABLE=LOG'||','||                 


                      'OPERATION='||v_operation||','||   

                      'SID='||v_sid||','||               

                      'ACTION='||v_actionid||','||       

                      'ROWID='||v_rowid||','||           

                      'DATE='||sysdate||','||

                      'TIMESTAMP='||timestamp||','||

                      'SOURCE='||source||','||

                      'MESSAGE='||message||','||

                      'SEVERENESS='||severeness||','||

                      'RETICLE_REF='||reticle_ref||','||

                      'SLOT_NUMBER='||slot_number||','||

                      'STOCKER_REF='||stocker_ref||','||

                      '-'||',');


Which calls:
  PACKAGE rsm_pipe;
  .....
  PROCEDURE write_pipe(message IN VARCHAR2) IS
  BEGIN
    IF (SUBSTR(message,1,6) = 'TABLE=') THEN   -- Real message
      INSERT INTO process_table values(message);
      DBMS_PIPE.PACK_MESSAGE(message);
      stat := DBMS_PIPE.SEND_MESSAGE('rms_pipe');
      IF NOT stat = 0 THEN
        RAISE_APPLICATION_ERROR(-20010, 
          'Execute_system: Error while sending.  Status = ' || stat);
      END IF;
    ELSE                                        -- Phantom message
      INSERT INTO ignore_table values(message);

END IF;
END write_pipe;

Now to see what is in the pipe:

First read shows real message SQL> exec rsm_pipe.read_pipe TABLE=LOG,OPERATION=UPDATE,SID=8,ACTION=43,ROWID=AAAAhfAAGAAACpkAAA,DATE=14-NOV- 01,TIMESTAMP=14-NOV-01,SOURCE=x,MESSAGE=x,SEVERENESS=1,RETICLE_REF=x,SLOT_NUMBER =1,STOCKER_REF=x,-,
Second call shows phantom messages. Notice the correct date
and session ID SQL> exec rsm_pipe.read_pipe LOG,UPDATE,8,14-NOV-01,TIMESTAMP=14-NOV-01,SOURCE=x,MESSAGE=x,SEVERENESS=1,RETIC LE_REF=x,SLOT_NUMBER=1,STOCKER_REF=x,-,

I have no idea where these messages are coming from. Any help would be much appreciated.

Thanks,
pascal Received on Wed Nov 14 2001 - 08:40:21 CST

Phantom messages with DBMS_PIPE