How to convert system change number (SCN) to Date in Java?

Question

In Oracle DB you can find out when your table was updated last time by using

SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) FROM myTable;

(see this question)

I get 01-AUG-17 05.29.54.000000000 PM from this query.

If you execute this query

SELECT MAX(ora_rowscn) FROM myTable;

You'll get result in system change number (SCN) format. I get 268908318 from this query.

In my Java app I want to write function that get String and return Date.

Something like this:

public Date getDateFromSCNString(String scnString) {...}

So, I want to write my SCN_TO_TIMESTAMP function in Java.

How can I do this?

Update: I want to get the date from the SCN number (268908318).

Do you want to get a date from `01-AUG-17 05.29.54.000000000 PM` or from `268908318`? — , Aug 03 '17 at 15:51
If you want to get the date from the SCN number (268908318), I'm afraid it's not possible. The SCN number is [internal to oracle](https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm#BABFAFIC) and there's no way for Java to find the corresponding date (the only way is, of course, querying the database) — , Aug 03 '17 at 16:08
If you want to get a date from `01-AUG-17 05.29.54.000000000 PM`, though, you can make a search at stackoverflow (or google, which will probably send you to a stackoverflow link), as this question has been asked several times. Also, take a look at [tag:datetime-parsing] and [tag:date-parsing] tags. If you're using Java 8, I'd also suggest [oracle's date/time tutorial](https://docs.oracle.com/javase/tutorial/datetime/iso/format.html) — , Aug 03 '17 at 16:11
Thank you for your answer, @Hugo! I realy wanted to get Date from `268908318`. If I understand you correctly, there is no way to do this. Ok, I'll try another way. If you don't mind I'll write answer for this question using you comments. Thank you one more time! — axreldable, Aug 03 '17 at 18:03
Indeed, there's no way because SCN is a number that's internal to oracle and there's no direct relation to a date (it can't be directly converted to a date outside the database). It'll depend on the table and the records in that table: different SCN values in different tables can even have the same date, so it's not something that you can do outside the context of the database. The only way to get the date with Java is to query the database. — , Aug 03 '17 at 18:46

score 1 · Accepted Answer · answered Aug 03 '17 at 18:25

1

So, thanks for @Hugo we know the answer. There is no way to convert SCN number (268908318) to Date in Java, since it internal oracle number. You can do it only by querying database.

answered Aug 03 '17 at 18:25

axreldable

134
1
10

score 0 · Answer 2 · answered Aug 03 '17 at 15:13

You can use SimpleDateFormat to set the custom format that you want in input and write your method like this:

public Date getDateFromSCNString(String scnString) {
    DateFormat formatter = new SimpleDateFormat("dd-MMM-yy HH.mm.ss.SSS a", Locale.US);
    return formatter.parse(scnString);
}

This will parse the string to date correcly.

And if you use as input "01-AUG-17 05.29.54.000000000 PM" the method will return the following date:

Tue Aug 01 05:29:54 CEST 2017

How to convert system change number (SCN) to Date in Java?

2 Answers2