Clever SQL trick that could come back to bite you
(No Ratings Yet)
Loading ...Posted by chris on Jan 5th, 2007
I recently moved some test code written in PL/SQL from one database (Solaris, Oracle 10.1, US7ASCII) to another (Linux, Oracle 10.2, UTF8). This revealed a SQL trick that doesn’t work quite as expected on the new platform, almost certainly due to the change in Oracle version. The code in question is:
SELECT 1 FROM DUAL CONNECT BY ROWNUM <= 7
This returns 8 rows on the first database and 7 on the second. I suspect that this is due to a change in the point that ROWNUM is incremented in the processing of the query. As I said, this was only in test code, but I guess that the lesson here is that if you rely on unsupported SQL tricks then don’t be surprised if their behaviour changes.
February 16th, 2007 at 11:07 pm
Your blog ate the rest of my query. Let’s try again:
SELECT 1 FROM DUAL CONNECT BY LEVEL *LTE* 7;