Oracle – Convert Raw or Hex Value to Signed 2’s Complement via SQL

It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set. Both your original procedure and your new procedure could then use the function. The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY. In this way the core query code would not be duplicated. Here is a pipelined result set example along with two queries using it:

create or replace PACKAGE Example AUTHID Definer AS

Type tRow Is Record (
     F1 Date
   , F2 Date
   );
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;

END Example;
/


create or replace PACKAGE BODY Example AS

Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
   vYesterday Date := trunc(sysdate-1);
Begin
  For vRow IN (
      SELECT pDate, vYesterday FROM DUAL
      UNION ALL
      SELECT pDate, vYesterday-1 FROM DUAL
      UNION ALL
      SELECT pDate+1, vYesterday FROM DUAL
  )
  Loop
    Pipe Row(vRow);
  End Loop;
End;

END Example;
/


SELECT * FROM TABLE(Example.PipelineResult);

SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;

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;