2

I have ORACLE DB and 2 tables. I need select rows from table1 inner join table2 and order by ORACLE RowID column. To select I use criteria query. To add order by statement I use 

criteria.addOrder(Order.asc("this.rowId"));

In mapping RowId look like 

<property name="rowId" column="ROWID" insert="false" update="false"/>

But hibernate generate wrong sql query like 

select this_.docId as attr0_, this_.name as attr1_ from table1 this_ inner join table2 t2_ on this_.docId=t2_.docId order by ROWID asc

Hibernate drop alias "this" from query. Because all tables in ORACLE has ROWID column, we have oracle error ORA-00918

How i can write correct query by hibernate criteria to order by oracle RowId column?

pe4enko
  • 354
  • 4
  • 14
  • Functionnal question, why do you `order by rowid` ? – Plouf Mar 04 '13 at 10:24
  • Because Oracle return rows in random orders if we sort and rows has same values. [Tom Say](http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html) – pe4enko Mar 04 '13 at 10:42
  • Maybe, but if you delete a row, and insert an other one, it'll fill the gap. Thus, destroying your order, or at least inserting some data in the middle of your "ordered data". – Plouf Mar 04 '13 at 10:53
  • I know, but for me it is not important. – pe4enko Mar 04 '13 at 11:02
  • I'm afraid your request is logically incorrect. You say Oracle returns rows having "same" values? You data do not have any natural primary/business key? Maybe this is your problem. – ibre5041 Mar 04 '13 at 11:53
  • I have primary and business key. As written [here](http://stackoverflow.com/questions/2701782/rowid-oracle-any-use-for-it?rq=1) and [here](http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html) "ROWID is the physical location of a row. Consequently it is the fastest way of locating a row, faster even than a primary key lookup."(c) Business data can repeated. If I sorting by repeated business data, ORACLE not guarantees order of selected rows. That's why i need aditional sorting by rowID. – pe4enko Mar 04 '13 at 12:34
  • [Here](http://ronfrancis.wordpress.com/tag/hibernate/) is another article about this problem. – pe4enko Mar 04 '13 at 12:41
  • Similar one, can refer here - https://hibernate.onjira.com/browse/HHH-2381 – Nayan Wadekar Mar 04 '13 at 13:03
  • 2
    If you have a primary or unique key available then I'd order by one of those instead of ROWID -- it would also give you a deterministic sort order. You're not gaining anything from using ROWID here. It seems that ordering by this_.docId or this_.name would make sense – David Aldridge Mar 04 '13 at 13:42
  • 1
    I think it'is hibernate bug. As workaround we can use quoted column name in mapping like `` – pe4enko Mar 05 '13 at 08:30

1 Answers1

2

Hibernate thinks that rowid is an oracle function, but it is a column identificator. To say hibernate that rowid is a column name we need to write hibernate mapping as 

<property name="rowId" column="`ROWID`" insert="false" update="false"/>
svaor
  • 2,205
  • 2
  • 19
  • 41
pe4enko
  • 354
  • 4
  • 14