Problem using scalar queries on sequence

derrill · **Joined:** Fri Jan 07, 2005 3:34 pm **Posts:** 8

List list = so.session.find("select FIELD_ID_SEQUENCE.nextval from dual");

results in this exception:
net.sf.hibernate.QueryException: undefined alias: FIELD_ID_SEQUENCE [select FIELD_ID_SEQUENCE.nextval from dual]

I know for sure my sequence is in the DB. Also, I have no trouble with using sequences with <generator> in my mappings file.

emmanuel · **Posted:** Tue Jan 18, 2005 7:10 am

find takes HQL not SQL

derrill · **Joined:** Fri Jan 07, 2005 3:34 pm **Posts:** 8

I tried using CreateSQLQuery which is for native queries, but I can't get it to work with an oracle sequence. Is it possible use oracle sequences without using the <generator> ?

steve · **Posted:** Thu Jan 20, 2005 4:42 pm

You cannot map general properties using a sequence.

session.createSQLQuery() is used for "native sql queries", for which "select FIELD_ID_SEQUENCE.nextval from dual" is not valid (read the docs as to why).

If you want to get the value of a sequence for something other than an <id/>, just get a handle to the session's connection and do it:

Code:

session.connection().prepareStatement("select FIELD_ID_SEQUENCE.nextval from dual")...