[Oracle] Durability agent cannot recover inbox/outbox because generated batch SQL is incompatible with ODP.NET

[pedroandrade03]

Describe the bug

Wolverine 6.22.0 fixes #3589: the wolverinedb agent URI is now recognized and the durability agent starts successfully.

However, once the agent starts, the Oracle-backed durability operations repeatedly fail with ORA-00933 and ORA-00936.

The generated commands appear to:

• concatenate multiple SQL statements into a single OracleCommand;
• use generic @parameter placeholders in SQL executed by ODP.NET.

Because of this, persisted outgoing messages are not recovered after the application restarts. The message remains in wolverine_outgoing_envelopes with owner_id = 0.

This appears to be a separate issue from #3589. Version 6.22.0 fixes the agent URI problem, but then exposes this Oracle SQL incompatibility when the agent begins executing its recovery operations.

To Reproduce

Packages:

• WolverineFx 6.22.0
• WolverineFx.Oracle 6.22.0
• WolverineFx.RabbitMQ 6.22.0

Infrastructure:

• Oracle XE gvenzl/oracle-xe:21.3.0-slim-faststart
• RabbitMQ rabbitmq:3.11-management-alpine
• Application targeting net9.0

Relevant configuration:

builder.UseWolverine(options =>
{
    options.Durability.Mode = DurabilityMode.Solo;
    options.AutoBuildMessageStorageOnStartup =
        AutoCreate.CreateOrUpdate;

    options.PersistMessagesWithOracle(
        oracleConnectionString,
        "WOLVERINE_AGENT_REPRO");

    var rabbitMq = options.UseRabbitMq(factory =>
    {
        factory.HostName = rabbitHost;
        factory.Port = rabbitPort;
        factory.UserName = rabbitUsername;
        factory.Password = rabbitPassword;
    });

    rabbitMq.AutoProvision();
    rabbitMq.UseSenderConnectionOnly();

    options.PublishMessage<DurableMessage>()
        .ToRabbitRoutingKey(
            "durability.repro",
            "durability.repro.v1")
        .UseDurableOutbox();
});

public sealed record DurableMessage(Guid Id);

Steps:

1. Start Oracle and RabbitMQ.
2. Start the application.
3. Stop RabbitMQ.
4. Publish a durable message.
5. Verify that the message exists in wolverine_outgoing_envelopes.
6. Stop the application.
7. Start RabbitMQ again.
8. Restart the application using the same Oracle schema.
9. Observe the durability agent logs.
10. Query wolverine_outgoing_envelopes again.

The durability agent starts, but its database batches fail with errors similar to:

Wolverine.RDBMS.Polling.DatabaseBatchCommandException:
Database operation batch failure:

1. Wolverine.RDBMS.Durability.CheckRecoverableIncomingMessagesOperation
2. Wolverine.RDBMS.Durability.CheckRecoverableOutgoingMessagesOperation
3. Wolverine.RDBMS.Durability.MoveReplayableErrorMessagesToIncomingOperation
4. Wolverine.RDBMS.Durability.DeleteOldNodeEventRecords

Oracle.ManagedDataAccess.Client.OracleException:
ORA-00933: SQL command not properly ended

The generated batch contains multiple statements and @ parameters, for example:

select received_at, count(*)
from WOLVERINE_AGENT_REPRO.wolverine_incoming_envelopes
where status = 'Incoming' and owner_id = 0
group by received_at;

select distinct destination
from WOLVERINE_AGENT_REPRO.wolverine_outgoing_envelopes
where owner_id = 0;

insert into ...
where replayable = @replayable;

delete from ...
where timestamp < @p1;

A separate durability operation also fails:

Wolverine.RDBMS.Durability.DeleteExpiredEnvelopesOperation

delete from WOLVERINE_AGENT_REPRO.wolverine_incoming_envelopes
where status = 'Handled' and keep_until <= @p0;

Oracle.ManagedDataAccess.Client.OracleException:
ORA-00936: missing expression

The persisted outgoing message remains unrecovered:

select owner_id, destination, attempts
from WOLVERINE_AGENT_REPRO.wolverine_outgoing_envelopes;

Example result:

OWNER_ID = 0
ATTEMPTS = 0

Expected behavior

After RabbitMQ becomes available and the application restarts, the durability agent should:

1. Detect the persisted outgoing message.
2. Send it to RabbitMQ.
3. Remove or mark the persisted envelope as successfully processed.

Oracle durability operations should use Oracle-compatible parameter binding and command execution.

Potential source of the problem

From inspecting the 6.22.0 source:

• OracleMessageStore.ToCommandBuilder() returns the generic Weasel.Core.DbCommandBuilder.
• The generic builder produces @ parameter placeholders, while the Oracle-specific builder uses : parameters.
• DatabaseOperationBatch configures several operations into one compiled command.
• MoveReplayableErrorMessagesToIncomingOperation also contains a hard-coded @replayable parameter.

ODP.NET does not accept the generated multi-statement SQL batch as a regular OracleCommand.

A possible fix may require:

• using Oracle-compatible parameter markers;
• removing hard-coded @ parameters from shared durability operations;
• executing the durability operations separately within the same transaction, or generating an Oracle-compatible block.

Additional context

The original Unrecognized agent scheme 'wolverine' exception from #3589 no longer occurs in 6.22.0. The wolverinedb agent now starts, so that fix is working.

The failure happens afterward, when the started agent attempts to poll and recover persisted messages from Oracle.