I am using these hibernate settings

<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">
org.hibernate.dialect.Oracle9Dialect
</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.format_sql">true</prop>
<prop key="hibernate.generate_statistics">true</prop>
<prop key="hibernate.use_outer_join">true</prop>
<prop key="hibernate.jdbc.batch_size">0</prop>
<prop key="hibernate.jdbc.use_streams_for_binary">
true
</prop>
<prop key="hibernate.max_fetch_depth">1</prop>
<prop key="hibernate.cache.use_second_level_cache">
true
</prop>
<prop key="hibernate.cache.use_query_cache">true</prop>
<prop key="hibernate.cache.provider_class">
org.hibernate.cache.EhCacheProvider
</prop>
<!-- <prop key="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</prop> -->
<prop key="hibernate.query.factory_class">
org.hibernate.hql.ast.ASTQueryTranslatorFactory
</prop>

<prop key="hibernate.connection.release_mode">
auto
</prop>


</props>
</property>



When i execute a select query it shows following error:


ORA-01002: fetch out of sequence


Kindly tell me what should i do to resolve this problem.
what hibernate property should be changed in order to solve this.


Thanks in advance
[/b]

We are also experiencing the "ORA-01002: fetch out of sequence" problem with Hibernate 3 running with Liferay portal.

This is a critical problem for us. Every help is greatly appreciated.

This is the jboss datasource we're using:

<?xml version="1.0"?>

<datasources>
<local-tx-datasource>
<jndi-name>jdbc/LiferayPool</jndi-name>
<connection-url>jdbc:oracle:oci8:@lportal</connection-url>
<connection-property name="auto-commit">false</connection-property>
<driver-class>oracle.jdbc.OracleDriver</driver-class>
<user-name>****</user-name>
<password>****</password>
<min-pool-size>5</min-pool-size>
</local-tx-datasource>
</datasources>

and there are hibernate settings:

hibernate.cache.use_query_cache=true
hibernate.cache.use_second_level_cache=true
hibernate.cache.use_minimal_puts=true
hibernate.cache.use_structured_entries=false
#hibernate.cache.provider_class=org.hibernate.cache.NoCacheProvider
#hibernate.cache.use_query_cache=false
#hibernate.cache.use_second_level_cache=false

hibernate.jdbc.use_scrollable_resultset=true
hibernate.bytecode.use_reflection_optimizer=true
hibernate.show_sql=false


Thank you

According to the Oracle documentation:

[/quote]

I'm not sure whether it is your code or Hibernate that is to blame, but what I would do is turn on the SQL and transaction logging and see exactly what sequence of SQL and transaction begin/end statements is causing the problem.

Do a small query and see if you are searching past the last record in the returned list. If this is the case, the suggested solution should work!

After enabling Hibernate log and DB trace, we noticed 2 records missing in the DB, and it seems that this caused the problem.
So, for now, I would say that the problem isn't related to hibernate.

I've also seen this caused by people looping through a ref cursor that is being used as an out parameter in the code. Instead, you can avoid the ORA-01002: Fetch Out Of Sequence Exception by opening two different and separate database cursors, and use one as the out parameter, and use the other cursor throughout the loop.