Error in executing query which involves pagination

sudhirph · **Joined:** Thu Apr 20, 2006 3:44 am **Posts:** 32

I am executing following query which involves pagination

Iterator itr = session.createCriteria(MadAcctMapParameters.class)
.setFirstResult(2).setMaxResults(2).list().iterator();

FYI I am using oracle 10G and HIbernate 3

Following is the erroor I am getting at console..........please notice sql also in following message

Hibernate: select limit ? ? this_.ORDERNUM as ORDERNUM3_0_, this_.ACCTTYPE as ACCTTYPE3_0_, this_.CLIENTID as CLIENTID3_0_, this_.SOURCEFIELD as SOURCEFI4_3_0_, this_.VALUE as VALUE3_0_, this_.STARTPOS as STARTPOS3_0_, this_.ENDPOS as ENDPOS3_0_, this_.TRIMSPACES as TRIMSPACES3_0_, this_.PADSPACES as PADSPACES3_0_, this_.ADDDATE as ADDDATE3_0_ from sam_data_dev.MADACCTMAP this_

May 4, 2006 3:30:40 PM org.hibernate.util.JDBCExceptionReporter
WARNING: SQL Error: 923, SQLState: 42000
May 4, 2006 3:30:40 PM org.hibernate.util.JDBCExceptionReporter
SEVERE: ORA-00923: FROM keyword not found where expected

Error : could not execute query

axismundi · **Joined:** Mon Aug 22, 2005 5:45 am **Posts:** 146

have you tried to switch order setFirstResult() and setMaxResult()

Iterator itr = session.createCriteria(MadAcctMapParameters.class)
.setMaxResults(2).setFirstResult(2).list().iterator();

are you using correct dialect?

sudhirph · **Joined:** Thu Apr 20, 2006 3:44 am **Posts:** 32

i commented following SQL dialect, and it worked.....



I feel this is just a work around and I must include a dialect.

Considering oracle 10g , could you please tell me what dialect i shall use?

bkmr_77 · **Joined:** Tue Apr 25, 2006 12:04 pm **Posts:** 260

put this line in your hibernate config file

<property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>

It is working with oracle 10g for me.