ansaurus

Question

Problem using JPA with Oracle and grouping by hour

Answer 1

A:

Not quite an answer, but it sounds suspiciously like a driver issue to me (especially as this works fine on SQLServer). What version of the Oracle driver are you using? Have you tried different versions?

karianna 2010-10-01 12:10:57

Hmm, would've have preferred to leave that as a comment, but I don't have the rep, ah well!

karianna 2010-10-01 12:27:39

It does seam to be an issue with my oracle install. If I run the select: "select extract(hour from startofperiod) from lum_moneventdata", it produces the same error. Should this work? I'm using the 10.2.0.4.0 ojdbc driver. Running over the Oracle 11g.

Berne 2010-10-01 12:31:58

Not sure, but there are more modern drivers for 11g, see http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html for details. Incidentally on Sybase (15) you'd need to use a time or datetime data type, the oracle docs do seem to conflict on this (saying DATE stores hour but then stating that to extract it you need to use timestamp). That's about the extent of my knowledge I'm afraid

karianna 2010-10-01 13:10:05

I think my solution will be changing my field from DATE to TIMESTAMP (although it makes no sense for me).

Berne 2010-10-01 13:27:51

Sounds reasonable, let us know how you get on!

karianna 2010-10-01 13:34:33

Answer 2

+1 A:

Have you tried TO_CHAR(d, 'HH24') instead? You could also trunc() to hours...

Samuel 2010-10-01 13:02:09

The problem is that the JPA implementation (Hibernate) creates the query. Another problem is that this project must not be BD specific.

Berne 2010-10-01 13:10:46

Just an update, select to_char(startofperiod, 'hh24') from lum_moneventdata this works perfectly but select extract(hour from startofperiod) from lum_moneventdata doesn't work at all (and it's exactly what the JPA impl does).

Berne 2010-10-01 13:22:57

Answer 3

A:

I didn't convert all my DATE fields to TIMESTAMP ones. Instead, I extended the Oracle's dialect to rewrite the hour function.

Like that:

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

Using this dialect (org.hibernate.dialect.Dialect), the hour function will not use the ANSI hour function (extract(hour from <FIELD>)). It will use to_number(to_char(<FIELD>, 'hh24')) instead.

The bad thing is that it always use the custom function (even when it doesn't need to be used, like with a TIMESTAMP field).

This is what I did to solve my problem.

Berne 2010-10-04 14:15:52

A teammate of mine opened a bug in Hibernate's Jira. It is: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5626

Berne 2010-10-05 12:26:31

ansaurus

tags:

views:

answers:

Problem using JPA with Oracle and grouping by hour

related questions