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

<Kenneth Koenraadt> wrote in message
news:4149b016.751843_at_news.inet.tele.dk...

> 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.

It is our primary task to migrate our product from Windows NT/2000/2002 + DB2

to Windows NT/2000/2002 + Oracle.

As I told before, DB2 does not have the problem described (cause it uses

a static counter for generating microseconds).

All know that Oracle is the leader among RDBMS,

that is why I am too SURPRIZED knowing that DB2 supports fraction(6)

but Oracle seems not!!!!

>
> 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.

This solution seems good, but what about performance?

>
> 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?

As I told before we would rather not to change our system.

I know a set of solutions that would make our DB architecture

better and code much more safe,

but we cannot make these changes now!

>
> - Kenneth Koenraadt
>
>

With regards,

Eugene Petrov. Received on Fri Sep 17 2004 - 04:11:41 CDT