BEGIN
  SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
  (
    QUEUE_TABLE           =>        'QT_NEW_CASE'
   ,QUEUE_PAYLOAD_TYPE    =>        'FILE_ACTION'
   ,COMPATIBLE            =>        '8.1'
   ,STORAGE_CLAUSE        =>        '
                                     TABLESPACE USERS
                                     PCTUSED    0
                                     PCTFREE    10
                                     INITRANS   1
                                     MAXTRANS   255
                                     STORAGE    (
                                                 INITIAL          64K
                                                 NEXT             1M
                                                 MINEXTENTS       1
                                                 MAXEXTENTS       UNLIMITED
                                                 PCTINCREASE      0
                                                 BUFFER_POOL      DEFAULT
                                                )'
   ,SORT_LIST             =>        'ENQ_TIME'
   ,MULTIPLE_CONSUMERS    =>         FALSE
   ,MESSAGE_GROUPING      =>         0
   ,SECURE                =>         FALSE
   );
End;

CREATE OR REPLACE TYPE FILE_ACTION                                                                                                                                                       AS OBJECT
(    ACTION VARCHAR2(20),
     CASE_ID NUMBER(10),
     OTHER VARCHAR2(20)
);

queue_util.add_file ('CLOSE', v_case_id,:NEW.ID);

PROCEDURE add_file_to_queue (action_in      IN VARCHAR2,
                                d_case_id_in   IN NUMBER,
                                d_other_in     IN VARCHAR2:= NULL)
   IS
      /******************************************************************************
      PURPOSE: when there is a change to a file (create, closed or reopen)  add the change to the queue of changes
     ******************************************************************************/
      queue_options        SYS.DBMS_AQ.enqueue_options_t;
      message_properties   SYS.DBMS_AQ.message_properties_t;
      message_id           RAW (16);
      my_message           file_action;
      err_text             VARCHAR2 (2000);
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      my_message := file_action (action_in, d_case_id_in, d_other_in);
      DBMS_AQ.enqueue (queue_name           => 'NEW_CASE_QUEUE',
                       enqueue_options      => queue_options,
                       message_properties   => message_properties,
                       payload              => my_message,
                       msgid                => message_id);

      IF g_debugging
      THEN
        ;
      --insert debugging info if g_debugging is true
      END IF;

      COMMIT;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         err_text := SQLERRM;
         --logging error to another table

      WHEN OTHERS
      THEN
         err_text := SQLERRM;
         --more logging
   END add_file_to_queue;

PROCEDURE send_from queue (case_id_in IN NUMBER := NULL)
   IS
      /******************************************************************************
      PURPOSE:get the list of file changes and send them out
      *****************************************************************************/
      queue_options                DBMS_AQ.dequeue_options_t;
      message_properties           DBMS_AQ.message_properties_t;
      message_id                   file_action;
      v_file                       VARCHAR2 (20);
      v_case_id                    NUMBER (10);
      v_filename                   VARCHAR2 (500);
      v_action                     VARCHAR2 (20);
      v_other                      VARCHAR2 (20);
      v_err_id                     INTEGER;
      bad_data_ex EXCEPTION;
      v_err_text                   VARCHAR2 (50);

      TYPE cases_cur IS REF CURSOR;

      new_cases                    cases_cur;
   BEGIN
      IF case_id_in IS NULL
      THEN
         OPEN new_cases FOR
              SELECT   qt.msgid
                FROM   cqt_new_case qt
            ORDER BY   qt.enq_time;
      ELSE
         OPEN new_cases FOR
              SELECT   qt.msgid
                FROM   qt_new_case qt
               WHERE   qt.user_data.case_id = case_id_in
            ORDER BY   qt.enq_time;
      END IF;

      --should have added a check here to make sure
      --the other database is up and running
      LOOP
         BEGIN
            FETCH new_cases INTO   message_id;

            --reinitialize values to null
            v_case_id := NULL;
            v_filename := NULL;
            v_file := NULL;
            v_action := NULL;
            v_other := NULL;
            --to try and clear all locks
            COMMIT;
            EXIT WHEN new_cases%NOTFOUND;

            IF case_id_in IS NOT NULL
            THEN
               queue_options.deq_condition :=
                  'tab.user_data.case_id = ' || case_id_in;
            END IF;

            DBMS_AQ.dequeue (queue_name           => 'NEW_CASE_QUEUE',
                             dequeue_options      => queue_options,
                             message_properties   => message_properties,
                             payload              => my_message,
                             msgid                => message_id);
            v_case_id := my_message.case_id;
            v_action := my_message.action;
            v_other := my_message.other;

            IF v_case_id IS NOT NULL AND v_case_id > 0
            THEN
               IF g_debugging
               THEN
                 ;
               --insert your debugging information
               END IF;
            --continues on with lengthy data transforms
            --for actions like NEW, CLOSE, REOPEN
END SEND_FROM_QUEUE;