DATE datatype in Oracle 7.3 Server vs. Oracle Lite

I have discovered that Oracle 7.3 Server and Oracle Lite 3.0.6.2.6 handle the DATE datatype differently.

In Oracle 7.3 Server, the DATE datatype includes a time component. In Oracle Lite 3.0.6.2.6, however, the DATE datatype does NOT include a time component. To include a time component, you must use the TIMESTAMP datatype in Oracle Lite. The Oracle Lite SQL Language Help states that TIMESTAMP in Oracle Lite is the equivalent of DATE in Oracle Server.

This distinction caused me a problem in a Forms 5.0 application that ran against both Oracle Lite and Oracle Server. I used the same SQL script, with DATE datatypes throughout, to create the database in both Oracle Lite and Oracle Server. When the form ran against Oracle Lite, I received the FRM-40654 "Record has been update by another user" error because the database and form became out of sync (date fields in the form had a time component, but the corresponding fields in the database did not). I did not receive the error when running against Oracle Server. After changing the DATE datatypes in Oracle Lite to TIMESTAMP, the error disappeared.

Furthermore, in Oracle Lite, the SQL*Plus DESCRIBE command displays a TIMESTAMP column as DATE. Thus, the only way to be absolutely sure that the column is TIMESTAMP is to drop and re-create it explicitly as TIMESTAMP. The "Developing Applications with Oracle Lite" manual contains in Chapter 1 a section titled "Differences Between Oracle Lite and Oracle Databases." However, this different behavior of DATE datatypes is not mentioned.

To me, it is counter-intuitive that the DATE datatype behaves differently in the two products.

Todd Owers

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Feb 04 2000 - 11:41:31 CST