error with TOraAlerter, DBMS_PIPE, UnpackMessage

[Roy Fraties]

On Delphi 5 and version 6.25 of ODAC, I observe a problem using DBMS_PIPE and UnpackMessage.

The *first* packed item, is always lost, with the message transmission.

So for example, a SQL block that does the following:

begin
DBMS_PIPE.PACK_MESSAGE('111');
DBMS_PIPE.PACK_MESSAGE('222');
DBMS_PIPE.PACK_MESSAGE('333');
DBMS_PIPE.SEND_MESSAGE('MY_PIPE');
end;
/

Then, a Delphi application, with a TOraAlerter component on it set to etPipe, listening for pipe 'MY_PIPE', will receive the message but the '111' portion is always lost, and only '222' and '333' portions make it across.

with OraPipe do
begin
sl := TStringList.Create;
while NextItemType mtNone do
sl.Add(UnpackMessage);
ShowMessage(CommaText);
// always shows '222', '333' and the '111' portion is always lost
end;

I *believe* (did not prove this) that the problem relates to this procedure (in the ODAC source)

procedure TOraAlerter.AfterExecute(Sender: TObject; Result: boolean);

The procedure incorrectly calls UnpackMessage, immediately after the message is received, which incorrectly removes the first packed item from the queue.

The work-around, obviously, is to send a dummy value as the first item in the message, so that it can be skipped without loss.

An alternative work-around is to create my own package wrapper (using the ODAC Oracle Package Wizard) and then use that in lieu of TOraAlerter.

Please advise.

[Plash]

The first message is not lost. You get it in the Message parameter of the OnEvent event.