Código

Basado en el problema que fui el primero en resolver en Totalplay

Recuperación de la Exception Queue de Oracle AQ

Miles de clientes atorados, sin que nadie lo supiera.

C# 81%SQL 13%MSBuild 6%15 archivos729 líneas de código 9/9 pruebas
using Oracle.ManagedDataAccess.Client;

namespace AqRecovery.Oracle;

/// <summary>
/// Implementación con ODP.NET. El desencolado de la exception queue, el reencolado en la cola
/// original, el registro para revisión y la bitácora comparten una sola transacción de Oracle
/// (<see cref="OracleAQVisibilityMode.OnCommit"/>): o pasa todo, o no pasa nada.
/// </summary>
public sealed class OracleRecoveryGateway : IRecoveryGateway
{
    private readonly string _connectionString;
    private readonly string _exceptionQueue;
    private readonly string _targetQueue;

    public OracleRecoveryGateway(string connectionString, string exceptionQueue, string targetQueue)
    {
        _connectionString = connectionString;
        _exceptionQueue = exceptionQueue;
        _targetQueue = targetQueue;
    }

    public async Task<IRecoveryUnitOfWork> BeginAsync(CancellationToken cancellationToken)
    {
        var connection = new OracleConnection(_connectionString);
        await connection.OpenAsync(cancellationToken);
        var transaction = connection.BeginTransaction();
        return new OracleUnitOfWork(connection, transaction, _exceptionQueue, _targetQueue);
    }

    public async Task<int> GetRecoveryAttemptsAsync(string correlationKey, CancellationToken cancellationToken)
    {
        await using var connection = new OracleConnection(_connectionString);
        await connection.OpenAsync(cancellationToken);
        await using var command = connection.CreateCommand();
        command.BindByName = true;
        command.CommandText =
            "SELECT COUNT(*) FROM COBRANZA.AQ_RECOVERY_AUDIT WHERE NVL(CORRELATION_ID, MESSAGE_ID) = :correlation_key";
        command.Parameters.Add("correlation_key", OracleDbType.Varchar2).Value = correlationKey;
        return Convert.ToInt32(await command.ExecuteScalarAsync(cancellationToken));
    }

    private sealed class OracleUnitOfWork : IRecoveryUnitOfWork
    {
        // ORA-25228: tiempo de espera agotado o fin de la cola al desencolar.
        private const int NoMessageAvailable = 25228;

        private readonly OracleConnection _connection;
        private readonly OracleTransaction _transaction;
        private readonly string _exceptionQueue;
        private readonly string _targetQueue;
        private bool _committed;

        public OracleUnitOfWork(OracleConnection connection, OracleTransaction transaction, string exceptionQueue, string targetQueue)
        {
            _connection = connection;
            _transaction = transaction;
            _exceptionQueue = exceptionQueue;
            _targetQueue = targetQueue;
        }

        public Task<QueueMessage?> DequeueAsync(CancellationToken cancellationToken)
        {
            using var queue = new OracleAQQueue(_exceptionQueue, _connection, OracleAQMessageType.Raw);
            queue.DequeueOptions.Wait = 0;
            queue.DequeueOptions.Visibility = OracleAQVisibilityMode.OnCommit;
            queue.DequeueOptions.DequeueMode = OracleAQDequeueMode.Remove;

            try
            {
                var message = queue.Dequeue();
                return Task.FromResult<QueueMessage?>(new QueueMessage(
                    Convert.ToHexString(message.MessageId),
                    string.IsNullOrEmpty(message.Correlation) ? null : message.Correlation,
                    (byte[])message.Payload,
                    new DateTimeOffset(message.EnqueueTime, TimeSpan.Zero)));
            }
            catch (OracleException ex) when (ex.Number == NoMessageAvailable)
            {
                return Task.FromResult<QueueMessage?>(null);
            }
        }

        public Task RequeueAsync(QueueMessage message, TimeSpan delay, CancellationToken cancellationToken)
        {
            using var queue = new OracleAQQueue(_targetQueue, _connection, OracleAQMessageType.Raw);
            queue.EnqueueOptions.Visibility = OracleAQVisibilityMode.OnCommit;

            var outgoing = new OracleAQMessage(message.Payload)
            {
                Correlation = message.CorrelationId ?? message.MessageId,
                Delay = (int)delay.TotalSeconds,
            };
            queue.Enqueue(outgoing);
            return Task.CompletedTask;
        }

        public async Task MoveToManualReviewAsync(QueueMessage message, string reason, CancellationToken cancellationToken)
        {
            await using var command = _connection.CreateCommand();
            command.BindByName = true;
            command.CommandText = """
                INSERT INTO COBRANZA.AQ_MANUAL_REVIEW (MESSAGE_ID, CORRELATION_ID, PAYLOAD, REASON)
                VALUES (:message_id, :correlation_id, :payload, :reason)
                """;
            command.Parameters.Add("message_id", OracleDbType.Varchar2).Value = message.MessageId;
            command.Parameters.Add("correlation_id", OracleDbType.Varchar2).Value = (object?)message.CorrelationId ?? DBNull.Value;
            command.Parameters.Add("payload", OracleDbType.Blob).Value = message.Payload;
            command.Parameters.Add("reason", OracleDbType.Varchar2).Value = reason;
            await command.ExecuteNonQueryAsync(cancellationToken);
        }

        public async Task RecordAuditAsync(AuditEntry entry, CancellationToken cancellationToken)
        {
            await using var command = _connection.CreateCommand();
            command.BindByName = true;
            command.CommandText = """
                INSERT INTO COBRANZA.AQ_RECOVERY_AUDIT (MESSAGE_ID, CORRELATION_ID, SOURCE_QUEUE, ACTION, REASON, ATTEMPT)
                VALUES (:message_id, :correlation_id, :source_queue, :action, :reason, :attempt)
                """;
            command.Parameters.Add("message_id", OracleDbType.Varchar2).Value = entry.MessageId;
            command.Parameters.Add("correlation_id", OracleDbType.Varchar2).Value = (object?)entry.CorrelationId ?? DBNull.Value;
            command.Parameters.Add("source_queue", OracleDbType.Varchar2).Value = entry.SourceQueue;
            command.Parameters.Add("action", OracleDbType.Varchar2).Value =
                entry.Action == RecoveryActionKind.Requeue ? "REQUEUED" : "MANUAL_REVIEW";
            command.Parameters.Add("reason", OracleDbType.Varchar2).Value = entry.Reason;
            command.Parameters.Add("attempt", OracleDbType.Int32).Value = entry.Attempt;
            await command.ExecuteNonQueryAsync(cancellationToken);
        }

        public Task CommitAsync(CancellationToken cancellationToken)
        {
            _transaction.Commit();
            _committed = true;
            return Task.CompletedTask;
        }

        public async ValueTask DisposeAsync()
        {
            if (!_committed) _transaction.Rollback();
            _transaction.Dispose();
            await _connection.DisposeAsync();
        }
    }
}