Serialize :new in trigger

oracleoracle-11g-r2plsqltrigger

I have a trigger that needs to create an Oracle Advanced queue message. The payload of that message should contain the new record that was created, that is accessible via :new.

What I am looking for is a way to transform the :new object into raw or a custom data type that can be set as the payload of the queue message, in a generic way.

At the moment I'm trying to convert :new to anydata and set that as the payload data type.

Best Answer

I implemented what you are trying to do this way: the transformation to a user defined object happens in a packaged procedure that is called by triggers.

---all comments and debugging removed to keep it simple----    
CREATE OR REPLACE TRIGGER when_close_ini
   AFTER UPDATE OF end_date
   ON YOUR_TABLE
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE

BEGIN
   IF :NEW.end_date IS NULL AND :OLD.end_date IS NOT NULL
   THEN
      my_schema.queue_util.add_file_to_queue ('REOPEN', :NEW.ID, NULL);

   END IF;

END when_close_ini;

----------------an excerpt from the package with debugging removed----
 PROCEDURE add_file_to_queue   (action_in      IN VARCHAR2,
                                d_case_id_in   IN NUMBER,
                                d_other_in     IN VARCHAR2 := NULL)
   IS

      queue_options        SYS.DBMS_AQ.ENQUEUE_OPTIONS_T;
      message_properties   SYS.DBMS_AQ.MESSAGE_PROPERTIES_T;
      message_id           RAW (16);
      my_message           MY_SCHEMA.file_action;
      err_text             VARCHAR2 (2000);
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN

      my_message := my_schema.file_action (action_in, d_case_id_in, d_other_in);
      DBMS_AQ.enqueue (queue_name           => 'MY_SCHEMA.MY_QUEUE',
                       enqueue_options      => queue_options,
                       message_properties   => message_properties,
                       payload              => my_message,
                       msgid                => message_id);
     COMMIT;
   END add_file_to_queue;
Related Question