[hibernate-issues] [Hibernate-JIRA] Commented: (EJB-434) SQLException: ORA-02287 while fetching the sequence value

[Carrie Bordadora (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/EJB-434?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=33621#action_33621 ] 

Carrie Bordadora commented on EJB-434:
--------------------------------------

We are using hibernate with EJB3.  The error occurred with the following:

String sql = "select MY_SEQ.NEXTVAL from DUAL";
Long value = ((BigDecimal) entityManager.createNativeQuery(sql).getSingleResult()).longValue();

Between hibernate entity manager 3.3 and 3.4, the query execution for single result was improved to limit the maximum number of rows to 2.  As stated in the comment above, Oracle does not allow the sequence to be part of the SQL as:

select * from ( select MY_SEQ.NEXTVAL from DUAL ) where rownum <= 2

The workaround we used is to not execute the getSingleResult method.  Instead, we use the general getResultList method and return the first value of the List.

String sql = "select MY_SEQ.NEXTVAL from DUAL";
List results entityManager.createNativeQuery(sql).getResultList();
Long value = ((BigDecimal)results.iterator.next()).longValue();

This is working for us for now.  But hoping hibernate could come up with something better.

> SQLException: ORA-02287 while fetching the sequence value 
> ----------------------------------------------------------
>
>                 Key: EJB-434
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/EJB-434
>             Project: Hibernate Entity Manager
>          Issue Type: Bug
>          Components: EntityManager
>    Affects Versions: 3.4.0.GA
>         Environment: 3.4.0.GA,  Oracle 10g
>            Reporter: Jerish Joseph
>
> We are getting ORA-02287 error while fetching the sequence value from database. The query executed is SELECT APP_SEQ.NEXTVAL FROM DUAL. We reveretd to an old version of hibernate-entitymanager (3.2.1.ga ) to get rid of this issue.
> The exception thrown with 3.4.0.GA is:
> Caused by: java.sql.SQLException: ORA-02287: sequence number not allowed here
>         at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
>         at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
>         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
>         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
>         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
>         at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
>         at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
>         at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
>         at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
>         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
>         at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
>         at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
>         at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1203)
>         at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuery(WSJdbcPreparedStatement.java:1011)
>         at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:661)
>         at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
>         at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
>         at org.hibernate.loader.Loader.doQuery(Loader.java:697)
>         at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
>         at org.hibernate.loader.Loader.doList(Loader.java:2228) 
>         ... 196 more

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira