DBMS_ADVANCED_REWRITE - very useful

We've had an issue with a client application where the rows used to reuturn in the correct order in 10.2 but since moving to 11.2 they do not. The SQL has no order by (hence the problem). It seems in 10.2 and below the rows were coming back in inserted order - since 11.2 they do not. We had some similar issues when we moved from 9i to 10.2 on some systems due to the behaviour or group by changing which meant it did not come back ordered (this could be 'fixed' by setting _gby_hash_aggregation).

This statement however was just a simple

SELECT * FROM AGGREGATOR.ZAINET_720_DTL

And is it just a straight table.

The problem is the app is so old no-one is sure we have the correct source code to start making changes to....

So what could we do.

This 'bodge' seems to resolve it......

beginsys.dbms_advanced_rewrite.declare_rewrite_equivalence ('test_rich','SELECT * FROM AGGREGATOR.ZAINET_720_DTL','SELECT * FROM (select * from AGGREGATOR.ZAINET_720_DTL ORDER BY RECNUM)',false);end

The code just does a text match on the SQL and replaces the SQL that you ran with the SQL you want to run instead (very powerful - and very dangerous.....). I had to do the rewritten SQL the way i did as it kept throwing the ORA-30389 error if i just added a simple order by to the end of the statement - bracketing it seems to resolve it.

Rich;