Hibernate Oracle Problem: ORA-00907: missing right parenthes

[deanouk]

Hi all.

I'm using JBoss 4.2.1GA, and Oracle 10g, and attempting to perform a query.

I've been receiving a "ORA-00907: missing right parenthesis" error when attempting to perform the query.

Now this is apparently due to Hibernate using the 'as' keyword in the query - something that has been fixed in Hibernate 3.3.0ga.

So I updated these Hibernate libs in \server\default\lib:
hibernate-entitymanager.jar (3.3.1ga)
hibernate-annotations.jar (v 3.3.0ga)
hibernate-commons-annotations.jar (within Hibernate Annotations.rar\lib)
ejb3-persistence.jar (within Hibernate Annotations.rar\lib)
hibernate3.jar (v 3.2.5.ga)

But still get the same error. The query still contains the 'as' keyword.

Can anyone help?

[deanouk]

Just to let you know the query...


I have two tables.

Users and UserSurveys

There is a one-to-many mapping between
Users to UserSurveys.

I'm trying to perform a query like to get users who haven't completed a survey before 'purchaseDate':

Query query = entityManager.createQuery("FROM " + User.class.getSimpleName()
+ " cu LEFT JOIN FETCH cu.userSurveys us"
+ " WHERE cu.purchaseDate <= :purchaseDate");

query.setParameter("purchaseDate", new java.sql.Date(cal.getTimeInMillis()), TemporalType.DATE);

That's it.

[deanouk]

Query query = entityManager.createQuery("FROM " + User.class.getSimpleName()
+ " cu LEFT JOIN FETCH cu.userSurveys us"
+ " WHERE cu.purchaseDate <= :purchaseDate"
+ " AND NOT EXISTS (SELECT us FROM cu.userSurveys us WHERE us.userId = cu.userId)");

[deanouk]

To make it even simpler I've done:

Query query = entityManager.createQuery("FROM " + User.class.getSimpleName()
+ " cu LEFT JOIN cu.userSurveys us"
+ " WHERE NOT EXISTS (SELECT us FROM cu.userSurveys us WHERE us.userId = cu.userId)");

Still the same problem.

[deanouk]

I'm going to have to assume this is a bug and raise it with both JBoss and Hibernate unless anyone has any further ideas?