We seem to have found a problem using Hibernate against an Oracle 9.2.0.1 database when using LockMode.UPGRADE in conjuction with the setMaxResults() method on the Query.
Our HQL is as follows:
--------------------------------
from com.mincom.ellipse.edoi.ejb.msf220.MSF220Rec MSF220Rec where (MSF220Rec.poType = 'T')
we setup the query as follows:
-------------------------------------------
String hqlString = "from com.mincom.ellipse.edoi.ejb.msf220.MSF220Rec MSF220Rec where (MSF220Rec.poType = 'T')";
Session session = HibernateUtil.getSession();
org.hibernate.Query hqlQuery = session.createQuery(hqlString);
//Setup the max results for the query
if (maxResults > 0){
hqlQuery.setMaxResults(maxResults);
}
hqlQuery.setLockMode("MSF220Rec", LockMode.UPGRADE);
queryResults = new QueryResultsImpl(hqlQuery.list());
The Oracle SQL generated from Hibernate is as follows:
select * from ( select msf220rec0_.PO_NO as PO1_333_, msf220rec0_.PO_TYPE as PO2_333_, msf220rec0_.SUPPLIER_NO as SUPPLIER3_333_, msf220rec0_.PURCH_OFFICER as PURCH4_333_, msf220rec0_.STATUS_220 as STATUS5_333_, msf220rec0_.SUPPLY_CUST_ID as SUPPLY6_333_, msf220rec0_.DSTRCT_CODE as DSTRCT7_333_, msf220rec0_.REVSD_ORDER_DATE as REVSD8_333_, msf220rec0_.REVSD_CRT_DATE as REVSD9_333_, msf220rec0_.PO_XMIT_VERSION as PO10_333_, msf220rec0_.FREIGHT_CODE as FREIGHT11_333_, msf220rec0_.DELIV_LOCATION as DELIV12_333_, msf220rec0_.LIVE_CONF_IND as LIVE13_333_, msf220rec0_.PO_MEDIUM_IND as PO14_333_, msf220rec0_.LAST_PRT_RUNNO as LAST15_333_, msf220rec0_.CURRENCY_TYPE as CURRENCY16_333_, msf220rec0_.ORDER_DATE as ORDER17_333_, msf220rec0_.PO_VERSION as PO18_333_, msf220rec0_.CHANGE_COUNT as CHANGE19_333_, msf220rec0_.NO_OF_ITEMS as NO20_333_, msf220rec0_.COMPLETE_ITEMS as COMPLETE21_333_, msf220rec0_.OWNED_STK_IND as OWNED22_333_, msf220rec0_.CON_STOCK_IND as CON23_333_, msf220rec0_.SERV_ITEM_IND as SERV24_333_, msf220rec0_.PREQ_ITEM_IND as PREQ25_333_, msf220rec0_.FREL_ITEM_IND as FREL26_333_, msf220rec0_.EDI_ACK_RCVD as EDI27_333_, msf220rec0_.EDI_ACK_DATE as EDI28_333_, msf220rec0_.AUTHSD_BY as AUTHSD29_333_, msf220rec0_.AUTHSD_POSITION as AUTHSD30_333_, msf220rec0_.AUTHSD_DATE as AUTHSD31_333_, msf220rec0_.AUTHSD_TIME as AUTHSD32_333_, msf220rec0_.AUTHSD_STATUS as AUTHSD33_333_, msf220rec0_.RESTRICT_POS as RESTRICT34_333_, msf220rec0_.RESTRICT_RULE as RESTRICT35_333_, msf220rec0_.CREATION_DATE as CREATION36_333_, msf220rec0_.CREATION_TIME as CREATION37_333_, msf220rec0_.CREATION_USER as CREATION38_333_, msf220rec0_.LAST_RCPT_NO as LAST39_333_, msf220rec0_.ORIGIN_CODE as ORIGIN40_333_, msf220rec0_.COMPLETED_DATE as COMPLETED41_333_, msf220rec0_.AUTHSD_TOT_AMT as AUTHSD42_333_, msf220rec0_.AUTHSD_ITM_AMT as AUTHSD43_333_, msf220rec0_.FMS_REQ_IND as FMS44_333_, msf220rec0_.FMS_SUSPEND_IND as FMS45_333_, msf220rec0_.NPV_INDEX_CODE as NPV46_333_, msf220rec0_.NPV_PAY_DATE as NPV47_333_, msf220rec0_.NPV_PAY_DAYS as NPV48_333_, msf220rec0_.ORDER_ORIG as ORDER49_333_, msf220rec0_.GLOBAL_PO_IND as GLOBAL50_333_, msf220rec0_.MULTI_DST_RCPT_FLG as MULTI51_333_, msf220rec0_.WHOUSE_ID as WHOUSE52_333_, msf220rec0_.IDT_BTWN_SYS_SW as IDT53_333_ from MSF220 msf220rec0_ where msf220rec0_.PO_TYPE='T' ) where rownum <= ? for update of msf220rec0_.PO_NO
Oracle barfs with the following error:
select * from ( select msf220rec0_.PO_NO as PO1_333_, msf220rec0_.PO_TYPE as PO2_333_, msf220rec0_.S
ERROR at line 1:
ORA-00911: invalid character
---------------------------------------------------
If we do not call the setMaxResults() method then a valid Oracle statement is generated. Is this a bug or is it not valid to be able to use setMaxResults() in conjunction with a LockMode.UPGRADE? Can't think why it wouldn't be valid to be able to lock for update on a limited set of rows.
|