Nhibernate Generat wrong SQL for Oracle with locking

Question

yesterday I've been trying to make this code work inspite the fact it's just working fine with nhibrnate and SQL server but when it come to oracle it generate wrong sql

UnitOfWork.Current.CreateCriteria<Bill>().Add(Restrictions.IsEmpty("ReqId"))
.SetMaxResults(BatchSize).SetLockMode(LockMode.Upgrade).List<Bill>();

the generated SQL will something like

Select * from 
(Select bill_0.id,bill_0.BillNo ...... from Bill bill_0 where bill_0.reqId is Not null )
where ROWNUM < 10 for UPDATE of bill_0.ID

so i wont run because the allies bill_o is defined inside the inner sql statement so who got the solution ?

the correct sql would be something like this which i tried and worked on oracle db

Select bill_0.id,bill_0.BillNo ...... from Bill bill_0 
where bill_0.reqId is Not null and ROWNUM < 10 for UPDATE of bill_0.ID

Answer 1

Since, as you say, NHibernate is generating invalid Oracle SQL, I suggest you file a bug with the NHibernate people. The SQL would work if the in-line view had been assigned an alias of "bill_0", or if the FOR UPDATE clause didn't use a table alias ("for UPDATE of ID"). Whether you can modify your NHibernate calls to make either of these happen I'm afraid I have no idea.