Re: SQL Server rowversion or timestamp equivalent in Oracle 9i

From: Mark Bole <makbo_at_pacbell.net>
Date: Mon, 13 Dec 2004 18:02:51 GMT
Message-ID: <fPkvd.33533$zx1.27821@newssvr13.news.prodigy.com>

Najm Hashmi wrote:
> Hi All,
> I need to migrate a SQL Sever Database to Oracle 9i. I am just curious what
> equivalent of SQL Server timestamp datatype in Oracle. Or how I can
> implement similar behavior in 9i. I am sure Oracle must have very elegant
> way to handle such situation.
>
> " timestamp is a data type that exposes automatically generated binary
> numbers, which are guaranteed to be unique within a database. timestamp is
> used typically as a mechanism for version-stamping table rows. The storage
> size is 8 bytes."
>
> Thanks in advance.
>
> Najm
>
>

Closest thing would be SYS_GUID() function (see tahiti.oracle.com for details).

(There have been reports that this value is not necessarily truly, globally, unique, but then I suppose there could be cases where SQL Server timestamp is not always truly, globally, unique, in the same way that computer-based random number generators are not truly random.)

See also the definition of ROWID, which is built-in and therefore very elegant!

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/glossary.htm#432703

What are you using timestamp for in SQL Server? Generating a primary key or other unique indexed value? An Oracle sequence would be an appropriate replacement in this case.

The following link also has some specific alternatives for guaranteeing uniqueness of keys *across* databases, even though that isn't exactly what you asked.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96567/repconflicts.htm#21677

Mark Bole Received on Mon Dec 13 2004 - 12:02:51 CST