The problem of using JPA with Oracle and the clock grouping

Question

I have a problem with JPA with Oracle and clockwise grouping. Here's the scenario: I have the name EventData. This object has a java.util.Date field called startOfPeriod. This field displays the data type in the table field DATE. I am using the following query:

select min(ed.startOfPeriod) as eventDate,
(...)
from
Event e inner join e.eventDatas ed
(...)
group by
    year(ed.startOfPeriod), 
    month(ed.startOfPeriod), 
    day(ed.startOfPeriod), 
    hour(ed.startOfPeriod) 
order by 1

If I delete the group "hour (ed.startOfPeriod)", it works fine (it does not cause errors, but does not do what I want). When I insert this group by clause, it makes this exception:

Caused by: java.sql.SQLException: ORA-30076: campo de extração inválido para origem de extração
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:813)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3415)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1812)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)

Analyzing the error code , this occurs when "The source does not contain the specified extract field." But the extraction source (field startOfPeriod) has a DATE data type (which has a time part).

The same code works like a charm in SQL Server.

- , ?

Tnhks!

Answer 1

DATE TIMESTAMP. Oracle, hour.

:

public class Oracle9Dialect extends org.hibernate.dialect.Oracle9Dialect
{
    public Oracle9Dialect()
    {
        super();
        registerFunction("hour", new SQLFunctionTemplate(Hibernate.INTEGER, "to_number(to_char(?1, 'hh24'))"));
    }
}

(org.hibernate.dialect.Dialect), hour ANSI hour (extract(hour from <FIELD>)). to_number(to_char(<FIELD>, 'hh24')).

, ( , , TIMESTAMP).

, .

Answer 2

TO_CHAR (d, 'HH24')? trunc() ...

Answer 3

, , (, SQLServer). Oracle ? ?