Hi,

I'm attempting to reproduce a "select ... for update nowait where rownum < 2" in Hibernate version 2 against Oracle, but I'm running into difficulties.

Here's how I create the query:

Criteria criteria = session.createCriteria(Thing.class);
criteria.add(Expression.eq("aProperty", "someValue"));
criteria.setLockMode(LockMode.UPGRADE_NOWAIT);
criteria.setMaxResults(1);

When I execute this query however, I get an Oracle error:

ORA-00907: missing right parenthesis

and indeed when I look at the generated SQL I see this form:

select * from ( select this.A_PROPERTY as A etc... from THINGS this where this.A_PROPERTY='someValue' for update nowait ) where rownum <= 1

which indeed fails.

If I comment out the setMaxResults(1) bit it produces correct SQL, so it seems that LockMode.UPGRADE_NOWAIT and maxResults are not compatible.

Is this the case, or have I missed something?

Cheers

Nick Cotter

How do you do that in plain Oracle SQL?

I would do something along the lines of

select * from table where rownum < 2 for update nowait;

I quote Gavin from the dev list.

Fair enough - I will try to get around it with a n SQL query directly.

Thanks

Nick