1

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.

1

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;
0

I addressed this with the PL/SQL project manager years ago, and was told that it is not possible. You can't address the :NEW or :OLD columns as a record and convert them to anything. You can create a temporary table that is either identical to the table that you are writing the trigger for or generic, i.e. table_name, column_name, column_type, clob. You would then insert the :NEW or :OLD values into the temporary table and you can access those values with an after statement trigger. Then you can read the data in an after statement trigger.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.