Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Cases where a NULL status is correct, real time synchronous and asynchronous information versus transactional information

Cases where a NULL status is correct, real time synchronous and asynchronous information versus transactional information

From: Mark W. Farnham <>
Date: Fri, 24 Sep 2004 10:47:10 -0400
Message-ID: <>

> Dear God! Oracle Financials uses NULL in end_date on fnd_login to
> indicate either the current login or an abrupt termination of the user
> session neatly making your point and potentially providing with an

This is incorrect.

A login gets a value for end_date when a graceful logout is made. The initial assignment of NULL to end_date is correct, as there is no end_date for a current login. When there is a graceful logout, then and only then is there a known value for end_date. Understanding that the value is born NULL and how it can later become defined is entirely different from imputing a false meaning. Indeed an abrupt termination of the session from the client side results in a situation where the end_date remains undefined, and a crash of the database results in a situation where the end_date remains undefined.

Interpreting the value NULL for end_date as anything other than that the time of the end of the session is unknown is an error of logic. If someone in the e-Business suite team has produced something that counts NULL end_date tuples as current sessions, that is a bug, pure and simple. Documenting that a NULL value remains after an "abrupt" termination is correct documentation, but the NULL value continues to indicate only that the end_date is not known.

Whoever presents a result counting null end_dates as the number of current sessions is making an error. Now it may be a valid complaint that there is no convenient way to tell which fnd_logins are current other than joining to v$session and v$process, but misinterpreting the meaning of end_date is not justified by that complaint. Of course non-NULL end_date tuples can be filtered from consideration as current sessions, since those tuples were definitely excluded by graceful logouts.

This actually presents an interesting case of the interpretation of real time status information versus synchronous time based status versus committed transaction information.

Consider that if the e-Business suite added a column "CURRENT_SESSION" to fnd_login. That could be committed to TRUE on login, but what happens if the session is terminated on a real computer in real time? Then the committed status information in the database would be simply wrong. Not only is the quality of information less than that represented by the accuracy of unknown (NULL) in end_date, but it is actually misleading. This is an archetypal example of the attempting to record real time status information as anything other than information with an "as-of" date. It was true at such-and-so a time. If you want to know whether it is true right now, go look. You can save the answer again, and once again "as-of" your answer was right if you observed correctly.

Now another great example case of CURRENT is for things like catalog price lists. Informationally they can in theory be stamped with true on a status column if a price listed is valid until it is transactionally marked expired. Unfortunately, on price lists there is often a pre-determined valid starting time and expiration time (for example for a sale). This is well represented by two time columns as information. VALID_BEGINNING and VALID_THROUGH then have very specific values and the march of time tells you whether to include such a tuple in the candidate actual prices. (From which you're usually getting the minimum unless you're a used car dealer.) Still, no update to a status column is required to get the correct information value.

Now in the case of using VALID_BEGINNING and VALID_THROUGH, it is informationally true to use "high_values" for VALID_THROUGH, and this is conveniently included in the same query filtering against the system date(time) where VALID_BEGINNING is in the past and VALID_THROUGH is not in the past. That it may cause Oracle optimization difficulties is something that should be noted, but that is an implementation detail rather than informational truth. It is more germane to telling you whether you should collect histograms on the column than determining your information expression architecture. Still, whenever you are presented with date range where started some time ago through high values is indicative of "current" you do have a valid option of adding and maintaining a status column transactionally. The column adds no information (as long as there is a known value for high_values for time and we presume a system time value can be retrieved [both of which are true for Oracle]). However, whether the status column is useful is determined by size, cost of maintenance, and indexing on the negative side versus convenience and quite possibly speed on the positive side. Note that the interesting distinction here is that high values for time is disjoint from synchronous real time events (unless it is possible to run the system beyond the stated high_values, which is a violation of the definition of high_values), so that setting the status value is transactionally valid.



Received on Fri Sep 24 2004 - 09:43:25 CDT

Original text of this message