Re: Time fraction(6) with Oracle 9i on Windows NT

On Thu, 16 Sep 2004 17:41:15 +0300, "Eugene Petrov" <e_petrov_at_altaee.com> wrote:

>Can Oracle 9i generate microseconds for timestamp fields
>on Windows NT?
>
>I have a table with one timestamp field.
>Several inserts of CURRENT time gave following result:
> 2004-09-15-16.47.11.154000
> 2004-09-15-16.47.11.186000
> 2004-09-15-16.47.11.201000
> 2004-09-15-16.47.11.232000
>
>It looks like Oracle can only generate 3 fractional digits.
>We used the default options while installing Oracle 9i.
>
>May be there is some options that can affect this restriction?
>Some other RDBMS can generate the fraction(6).
>For example, DB2 would generate following:
> 2004-09-15-16.47.11.154001
> 2004-09-15-16.47.11.186002
> 2004-09-15-16.47.11.201003
> 2004-09-15-16.47.11.232004
>
>It appears that DB2 uses a static counter for generations microseconds.
>It is a normal approach with Windows (that has problems with fractions more
>then 3 digits!).
>
>Theoretically, We could generate fraction(6) manually and insert them in DB,
>but the system we migrate on Oracle is too big. We cannot replace all
>'insert CURRENT' with the code generating correct timestamp fraction(6)!
>
>Who solved such problems, please, share your knowledge!
>
> Thanks in advance,
>Eugene Petrov.
>

>May be there is some options that can affect this restriction?

Not AFAIK. Oracle's timestamps are inherited from the O/S.

Solution 1) : Move to a platform which doesn't suffer such problem, e.g. Linux.

Solution 2) : Create an INSERT trigger on the table in question and update the timestamp here. Triggers are evil regarding scalability and maintenance, but sometimes they are the only solution when you have painted yourself into a corner.

Solution 3) : You seem to need the timestamp to identify the order in which the rows came into the table. Could the application be modified to find that order by other means than the timestamp ?

• Kenneth Koenraadt