Package Query Infinite Loop

[Colin Brown]

Hi

I have created a package in Oracle 9i that contains:

procedure xyz (p_cursor in out ref_cursor)
as
begin
open p_cursor for
select
t.sqid
from
table_t t
where
(systimestamp - t.last_contact) > interval '1' minute;
end;

where ref_cursor is defined in the header as a ref cursor:

type ref_cursor is ref cursor;
procedure xyz (p_cursor in out ref_cursor);

and used the wizard to create the Delphi interface.

Elsewhere in my application I have:

fPackage.xyz (fQuery);
fQuery.Execute;
while not fQuery.Eof do begin
MyQueue.Add(fQuery.Field('SQID'));
end;

The query is continually returning the first sequential identifier ad infinitum (even though the Oracle Monitor shows only six rows returned as expected).

Has anyone else experienced this behaviour and found a solution? I have numerous other queries similarly formulated that all work perfectly.

Thanks
Colin Brown

 

[Marco Kalter]

Perhaps a fQuery.Next in within the loop will help

?

------------------
Marco Kalter
Allround Automations

 

[Colin Brown]

Thanks

I figured this out later in the day. I will
go and hide under a rock now.

Late nights, cut paste and edit tut, tut.

As an aside, does anyone know why when I try
to use TOracleSession.CheckConnection(true)
in my program and then unplug my network
connection the program just dies completely
but will survive and report errors without
this call?

Colin

 

[Marco Kalter]

TOracleSession.CheckConnection(True) will not only verify if the connection is still okay, but will also try to establish a new connection if the session is not okay. When the network is unplugged, this apparently leads to a problem.

------------------
Marco Kalter
Allround Automations