Oracle equivalent for SQL Server TIMESTAMP/ROWVERSION?

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  January 8, 2009 at 4:53 pm

  #197965

  Working on an app that needs to run in both SQL Server and Oracle. I want to be able to use the timestamp/rowversion data type as found in SQL Server (NOT the SQL-2003 timestamp data type). Is there an Oracle equivalent?

  To refresh your memory, this is timestamp in SQL Server...

  "Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter is the database timestamp. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one timestamp column. Every time that a row with a timestamp column is modified or inserted, the incremented database timestamp value is inserted in the timestamp column. "

  Thanks.

• Ivyflora

  SSC Enthusiast

  Points: 148

  More actions

  February 25, 2009 at 10:41 pm

  #950424

  In Oracle i am using ORA_ROWSCN function instead of SQL SERVER's Timestamp...

  Its very easy to use..

  When you create a table you can add ROWDEPENDENCIES command.

  For Example...

  SQL> CREATE TABLE TEST001(

  2 TESTID NUMBER,

  3 TESTNAME VARCHAR2(25))

  4 ROWDEPENDENCIES;

  Table created.

  SQL>

  SQL> INSERT INTO TEST001 VALUES(1,'DEMO');

  1 row created.

  SQL> COMMIT;

  Commit complete.

  SQL> select ORA_ROWSCN,TESTID,TESTNAME from TEST001;

  ORA_ROWSCN TESTID TESTNAME

  ---------- ---------- -------------------------

  5066802 1 DEMO

  SQL>

  SQL> update TEST001 set TESTNAME = 'TEST' WHERE TESTID = 1;

  1 row updated.

  SQL> COMMIT;

  Commit complete.

  SQL> select ORA_ROWSCN,TESTID,TESTNAME from TEST001;

  ORA_ROWSCN TESTID TESTNAME

  ---------- ---------- -------------------------

  5066883 1 TEST