Example 3-2 Using the SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN Functions

DECLARE
   right_now TIMESTAMP; 
   yesterday TIMESTAMP; 
   sometime TIMESTAMP;
   scn1 INTEGER; 
   scn2 INTEGER; 
   scn3 INTEGER;
BEGIN
   right_now := SYSTIMESTAMP; -- Get the current SCN
   scn1 := TIMESTAMP_TO_SCN(right_now);
   DBMS_OUTPUT.PUT_LINE('Current SCN is ' || scn1);
   yesterday := right_now - 1; -- Get the SCN from exactly 1 day ago
   scn2 := TIMESTAMP_TO_SCN(yesterday);
   DBMS_OUTPUT.PUT_LINE('SCN from yesterday is ' || scn2);
-- Find an arbitrary SCN somewhere between yesterday and today
-- In a real program we would have stored the SCN at some significant moment
   scn3 := (scn1 + scn2) / 2;
   sometime := SCN_TO_TIMESTAMP(scn3); -- What time was that SCN was in effect?
   DBMS_OUTPUT.PUT_LINE('SCN ' || scn3 || ' was in effect at ' ||
 TO_CHAR(sometime));
END;
/