2 Replies Latest reply on May 31, 2013 1:01 PM by blaxell

Cannot extract time from Oracle

blaxell May 31, 2013 8:18 AM

Hi,

According to the Oracle's docs:

"The DATE datatype stores point-in-time values (dates and times) in a table. The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight)."

I expect to be able to extract the time parts from a date field, but when I am trying to extract the hour from an oracle's date field like:

SELECT EXTRACT(HOUR FROM a.modifieddate) FROM oracle_db.salestaxrate a WHERE a.salestaxrateid = 1

and get a TEIID30504 exception. When I look deeper in Teiid's log, i see the following Oracle's exception:

Caused by: java.sql.SQLException: Remote java.sql.SQLException: ORA-30076: invalid extract field for extract source

I am using Oracle 10g and Teiid 8.1

1. Re: Cannot extract time from Oracle

shawkins May 31, 2013 8:41 AM (in response to blaxell)

That is an odd Oracle limitation - http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions052.htm

"If HOUR, MINUTE, or SECOND is requested, then expr must evaluate to an expression of datatype TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND. DATE is not valid here, because Oracle Database treats it as ANSI DATE datatype, which has no time fields."

If you want, log an issue for us to compensate for this.

Steve
1 of 1 people found this helpful
Actions
2. Re: Cannot extract time from Oracle

blaxell May 31, 2013 1:01 PM (in response to shawkins)

Hi Steve,

thanks a lot for a quick answer! I logged the issue here https://issues.jboss.org/browse/TEIID-2522
Actions

Go to original post