Get current time, on commit, with oracle database

oracleoracle-12cplsql

When inserting a row I want to make a column, equal to the current time on commit.

If I use default value sysdatetime on the column, it uses the current time at insert (which could be significantly different to commit time). If I use an on insert trigger, it again uses the time at the insert – not the actual commit.

Are there any possible solutions to get current time on commit with oracle ?

Best Answer

To get an approximate value (+/- 3sec or so) you may use ORA_ROWSCN

You must define the table with ROWDEPENDENCIES to have the SCN stored for each row.

Here an simple example:

create table t1 
(c1 number,
c2 date)
ROWDEPENDENCIES;

insert into t1 values(1,sysdate);
-- wait 5 sec
commit;

insert into t1 values(2,sysdate);
-- wait 10 sec
commit;

SELECT ORA_ROWSCN, SCN_TO_TIMESTAMP(ORA_ROWSCN), c1,c2 FROM t1;


ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)         C1 C2                
---------- ---------------------------- ---------- -------------------
   1.4E+13 19.05.16 19:14:15,000000000           1 19.05.2016 19:14:09 
   1.4E+13 19.05.16 19:14:30,000000000           2 19.05.2016 19:14:21

Related Solutions

Oracle – capture row commit order in a table

It isn't possible to determine the order in which rows are committed. Either a sequence or a timestamp can allow you to determine the order in which rows are inserted. But neither will tell you the order in which those transactions were committed.

Since you have a process that is trying subscribe to changes in a table, however, you have a few options that don't require you to identify this order. You can use Oracle Change Data Capture (CDC) and then have the reading process register as a subscriber. Or you can use Streams to publish changes to the table and have the reader process act as a custom apply process.

Both CDC and Streams will send the subscriber the data in commit order. The CDC change table will also store the commit SCN and the commit timestamp if you really want them (though I suspect you don't really care so long as the reader is sent every change in order).

Normal CDC involves the reader process controlling a window that shows one change set at a time, consuming that change set, and then advancing the window. The reader consumes data in commit order and never needs to know specifically what the SCN of a change is. You can hit the change tables directly as well to get the actual SCN if you want to complicate the reader's pull process.

Your reader process can hit the change tables, get the SCN from the change table, move it to the sequence_id column of your existing table, and then do whatever else you need to do with the data in the change table to move it into your existing architecture. It's a bit messy but doable. Your reader process could also be a Streams consumer and do the same thing (grabbing the SCN from the Streams change record rather than the CDC change table).

Insert with different key values to avoid contraint violation

I'd think it's probably not a best practice to use EXCEPTION to do logic/handling, such as duplicate keys. Here's an idea: write a stored proc. Also create a sequence that has your alternate values that will be used in cases of duplicate keys. You might want to start this sequence with a fairly high value like 1,000,000 (or much higher, just depends on your actual data). Then do your inserts via this stored proc whenever you want the dupe-sub functionality.

CREATE TABLE components (
    id_component INTEGER PRIMARY KEY
  , code         VARCHAR2(255) 
  , description  VARCHAR2(255)
  , model        VARCHAR2(255)
  , resp         VARCHAR2(255)
);

CREATE SEQUENCE components_alt_id_seq
  START WITH 1000000;

CREATE OR REPLACE PROCEDURE component_ins
  (  p_id_component IN components.id_component%TYPE
   , p_code         IN components.code%TYPE 
   , p_description  IN components.description%TYPE 
   , p_model        IN components.model%TYPE 
   , p_resp         IN components.resp%TYPE )
AS
  v_is_duplicate INTEGER;
BEGIN
  SELECT count(*) INTO v_is_duplicate FROM components WHERE id_component = p_id_component;
  IF v_duplicate = 0 THEN
    INSERT INTO components (id_component, code, description, model, resp)
      VALUES (p_id_component, p_code, p_description, p_model, p_resp);
  ELSE
    INSERT INTO components (id_component, code, description, model, resp)
      VALUES (components_alt_id_seq.NEXTVAL, p_code, p_description, p_model, p_resp);
  END IF;
END;
/

EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39823, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39824, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (40015, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (40016, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')

And then checked the result. Notice that the duplicate input values were automatically subbed with a value taken from the sequence.

SELECT id_component FROM components;

ID_COMPONENT
------------
       39822
       39823
       39824
       40015
       40016
     1000005
     1000006

7 rows selected.

Best Answer

Related Solutions

Oracle – capture row commit order in a table

Insert with different key values to avoid contraint violation

Related Question