Stale result return by SQL ? Oracle 8.0.5.1, Forms 5.0

The following SQL return the same set of rows even though the value in the WHERE clause changes.

select rowid, path, dsc
from z_menudef
where path like zp_package.curpath || '_'

zp_package.curpath is a package function which return the value of a package variable. This SQL return the same set of records even thougt the zp_package.curpath value have changed.

This problem happens intermittently and we have not yet pin point the cause. However we found that when this happens and we then analyse the index on the path column. This problem disappear.

When this problem appears, even if we start another session (with the same schema and userid) to run the same SQL would have the problem. This is very weird becasue different session would have different instance of the package variables, but the SQL return the same set of result even thought the values returned by zp_menu.curpath are different.

Could this be a bug in Oracle ? I have logged a call to Oracle and still waiting for their reply.

The SQL was actually issued by Form 5.0 in Win95 and the server is Oracle 8.0.5.1 in IBM RS/6000 AIX 4.3.2.

Thanks,
Nelson Mak
(my e-mail is not valid to avoid spam) Received on Tue Aug 24 1999 - 23:16:56 CDT