[hibernate-issues] [Hibernate-JIRA] Commented: (EJB-434) SQLException: ORA-02287 while fetching the sequence value
[
http://opensource.atlassian.com/projects/hibernate/browse/EJB-434?page=co...
]
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira