Hello,
When I use 'order by' in query with setMaxRows (and setFirstResult)
I have error in Oracle
I don't know which query hibernate send to Oracle, because Hibernate
write query after do statement - when statement fail query don't logged

I have this test :

public void testMaxRows () {
String query = "from AdmSnpeRoles entity where entity.id like '%'
order by entity.id";
// query isn't important, only order by clause
// Query q is created with Spring HibernateDaoSupport
// it isn't important
Query q = roleDao.createQuery(query);
try {
List list = q.list();
} catch (HibernateException e) {
assertTrue("list without FirstResult and RowMaxResult", false);
}
// it work - query is correct
q.setFirstResult(0);

// from now is errors
q.setMaxResults(10);
try {
q.list();
} catch (HibernateException e1) {
assertTrue("list with FirstResult and RowMaxResult", false);
}
}

I have Oracle 8.0 and error is (error is in setMaxRows which don't fire Exception) :

0 [main] WARN net.sf.hibernate.util.JDBCExceptionReporter - SQL Error: 907, SQLState: 42000
10 [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - ORA-00907: missing right parenthesis

12 [main] WARN net.sf.hibernate.util.JDBCExceptionReporter - SQL Error: 907, SQLState: 42000
13 [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - ORA-00907: missing right parenthesis

27 [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - Could not execute query
java.sql.SQLException: ORA-00907: missing right parenthesis

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:955)
at net.sf.hibernate.loader.Loader.list(Loader.java:946)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:846)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1543)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at yu.co.snpe.dao.TestBaseModel.testMaxRows(TestBaseModel.java:76)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)

Show the generated SQL.

What Dialect are you using?

Are you aware that there are problems with Oracle 8.0, and that you should use a more recent release of Oracle 8i?

I can't show generate SQL - hibernate show sql only if statement success

Exception is in setMaxRows ???

I use OracleDialect

I don't use Oracle 8i, because 8i is more memory hungry

regards
Haris Peco

No, the exception is certainly NOT from setMaxRows(). Show the SQL!

error is in second q.list

This is complete output (in eclipse)

Hibernate: select admsnperol0_.ID as ID, admsnperol0_.TIP as TIP, admsnperol0_.IME as IME from INTERBG.ADM_SNPE_ROLES admsnperol0_ where (admsnperol0_.ID like '%' ) order by admsnperol0_.ID
Hibernate: select * from ( select admsnperol0_.ID as ID, admsnperol0_.TIP as TIP, admsnperol0_.IME as IME from INTERBG.ADM_SNPE_ROLES admsnperol0_ where (admsnperol0_.ID like '%' ) order by admsnperol0_.ID ) where rownum <= ?
0 [main] WARN net.sf.hibernate.util.JDBCExceptionReporter - SQL Error: 907, SQLState: 42000
16 [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - ORA-00907: missing right parenthesis

20 [main] WARN net.sf.hibernate.util.JDBCExceptionReporter - SQL Error: 907, SQLState: 42000
20 [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - ORA-00907: missing right parenthesis

41 [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - Could not execute query
java.sql.SQLException: ORA-00907: missing right parenthesis

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:955)
at net.sf.hibernate.loader.Loader.list(Loader.java:946)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:846)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1543)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at yu.co.snpe.dao.TestBaseModel.testMaxRows(TestBaseModel.java:63)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)

So, the generated SQL is correct - looks like a bug in your version or Oracle.

If you want, you can customize the Hibernate OracleDialect to disable the use of the outer query to do pagination.

(Or just upgrade to a current version of Oracle.)

Oracle 8.0 don't accept order by in view (simple view and view in from clause) and this is a problem.

It isn't bug, oracle 8.0 haven't this behaviour

Have You solution or this features don't work for oracle 8.0 ?

regards

PS

it work with oracle 9.2

As I said above, you can customize the Dialect.

ScrollableResults do thing for me.It is better than limit query for 9, too

It isn't possible limit query with Oracle 8.0 and maybe we can set
supportLimit to false for Oracle 8 - this mean that we have to different Dialect for oracle 8.0 and Oracle 8i - Oracle80Dialect extends OracleDialect and set supportLimit to false, only

I don't know is it important

regards