2

I am working on Oracle 11g Enterprises Edition database. We have to get the data from database tables. Database tables have one of these types of columns :

  • TIMESTAMPLTZ
  • TIMESTAMPTZ

All other data types and their values are successfully fetched.

We are using apache meta-modal to parse the database tables and generating CSV using it.

When we are using this method on rows :

row.getValues()[indexColumn]

BUT this code does not fetch the value of column for the given row, it returning oracle.sql.TIMESTAMPLTZ@70156e7b object as string. We need its value.

Casting it to TIMESTAMPLTZ, throwing exception of cast. OR also columnValue.timestampValue() not making any sense.

How can I get the value for column type TIMESTAMPLTZ using Apache meta-model in formatted way?

Neelam Sharma
  • 2,745
  • 4
  • 32
  • 73
  • If you can already handle other data types, can't you use the [TIMESTAMPLTZ methods](https://docs.oracle.com/database/121/JAJDB/oracle/sql/TIMESTAMPLTZ.html) to convert to a Date etc. that you can then format for the CSV? Or even as a String if that format is acceptable? – Alex Poole Mar 30 '16 at 13:13

1 Answers1

3

OK, so if you are seeing something like "oracle.sql.TIMESTAMPLTZ@70156e7b", in your output then your code must be explicitly or implicitly calling the toString() on an oracle.sql.TIMESTAMPLTZ object.

That's not going to give you anything readable, let alone useful, because the TIMESTAMPLTZ class does not override toString(). What you are seeing is what the default Object.toString() method gives you.

An TIMESTAMPLTZ is actually a date converter / adapter that wraps a date in some form. The way you use a TIMESTAMPLTZ instance is to call one of the instance methods. For example, calling dateValue(Connection) on a TIMESTAMPLTZ object will convert the wrapped Oracle internal date into a Java Date object. Other instance methods convert to a String, an byte array, and JDBC date / time classes.

(The class also has a bunch of static converter methods, but I don't think that is what you need here.)

Stephen C
  • 698,415
  • 94
  • 811
  • 1,216
  • Thanks for help. Now I am able to get Date using dateValue, it is in yyyy-MM-dd HH:mm:ss.SSS format, but the value kept in database is of different format, as I have to use this CSV to restore those values in another database of oracle. At the time of restore it throw error of ORA-01843: not a valid month, because of format of date. I can't be sure of using one fix date format in SimpleDateFormat and this is not correct way to get format of date as kept in oracle db to save in my csv. – Neelam Sharma Mar 31 '16 at 10:39
  • Actually, the date is stored in the database in a binary form. It sounds like the actual problem here is in the conversions between string and binary that Oracle is doing. Look at the documentation on Format Models in Oracle SQL: https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm. You should be able to get everything to work consistently if you use a fixed SimpleDateFormat and a matching Date Model. – Stephen C Mar 31 '16 at 11:16
  • TIMESTAMPLTZ columnValue = (TIMESTAMPLTZ) row.getValues()[pos]; Date date = columnValue.dateValue(connection); String formattedDate = new SimpleDateFormat("dd-MMM-yy hh.mm.ss.SSSSSSSSS a").format(date);..columnValue.dateValue(connection) it return date only in 16-02-2016 11:22:06 till seconds, but in my db it is upto microseconds and time zone. So converting it into my desirable format is not producing correct result. – Neelam Sharma Apr 01 '16 at 06:59
  • So is the problem that the date is not stored in the >>source<< database with a timezone and microsecond accuracy? Check getting the TIMESTAMPLTZ object to output as a byte array, and printing and decoding the bytes by hand as per the javadoc. If that is the problem, then (obviously) you can't put the date into a target database whose schema requires components that do not exist in the source database. – Stephen C Apr 01 '16 at 07:32
  • In source database it is stored in timezone and microsecond, but getting value using Date date = columnValue.dateValue(connection) not returning the date exact saved in DB, and if it is not exported in CSV in correct format then it will not restore (import) in correct format. – Neelam Sharma Apr 01 '16 at 08:04
  • I think I know the answer to that. A `java.sql.Date` is truncated to second granularity. https://docs.oracle.com/javase/8/docs/api/java/sql/Date.html Try `java.sql.Timestamp` instead. – Stephen C Apr 01 '16 at 10:44