function f_get_queue_mes return my_type
  as
    v_dequeue_options    dbms_aq.dequeue_options_t;
    v_message_properties dbms_aq.message_properties_t;
    v_message_handle     raw(16);
    v_obj                sys.anydata;
    v_var                my_type := my_type();
    v_res                number;
    no_message           exception;
    pragma               exception_init(no_message,-25228);
  begin   
    v_dequeue_options.navigation := dbms_aq.first_message;
    v_dequeue_options.wait       := dbms_aq.no_wait;
  
    dbms_aq.dequeue(queue_name         => 'my_queue_q',
                    dequeue_options    => v_dequeue_options,
                    message_properties => v_message_properties,
                    payload            => v_obj,
                    msgid              => v_message_handle);
                    
    v_res := v_obj.getobject(v_var);    
    
    return v_var;   
  exception
    --если очередь пустая, ничего не делаем
    when no_message
      then return null;                   
  end; 
  
  
  procedure p_proc as
    v_arr   my_type := my_type();
  begin
    --основная процедура обработки очереди
    loop
      begin
        v_arr := f_get_queue_mes;
        if v_arr is null then
          exit;
        end if;
        
        --действия с сообщением
      end;
      commit;
    end loop;
  end;  

begin
  for rec in (select * from long_time_queue)
  begin
    proc(rec.id);
  end;
end;