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.
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();
}
}
}