Use FETCH FIRST N ROWS / OFFSET for Oracle LIMIT/OFFSET

Oracle will now use FETCH FIRST N ROWS / OFFSET syntax for limit/offset
support by default for Oracle 12c and above. This syntax was already
available when :meth:`_sql.Select.fetch` were used directly, it's now
implied for :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset` as
well.

I'm currently setting this up so that the new syntax renders
in Oracle using POSTCOMPILE binds.    I really have no indication
if Oracle's SQL optimizer would be better with params
here, so that it can cache the SQL plan, or if it expects
hardcoded numbers for these. Since we had reports that the previous
ROWNUM thing really needed hardcoded ints, let's guess
for now that hardcoded ints would be preferable.  it can be turned
off with a single boolean if users report that they'd prefer
real bound values.

Fixes: #8221
Change-Id: I812ec24ffc947199866947b666d6ec6e6a690f22

1 files changed, 13 insertions, 12 deletions

diff --git a/test/dialect/oracle/test_dialect.py b/test/dialect/oracle/test_dialect.py
index 98eb76c18..e3101840a 100644
--- a/test/dialect/oracle/test_dialect.py
+++ b/test/dialect/oracle/test_dialect.py
@@ -880,21 +880,22 @@ class ExecuteTest(fixtures.TestBase):
         )
 
         # here, we can't use ORDER BY.
-        eq_(
-            connection.execute(
-                t.select().with_for_update().limit(2)
-            ).fetchall(),
-            [(1, 1), (2, 7)],
-        )
+        # as of #8221, this fails also.  limit w/o order by is useless
+        # in any case.
+        stmt = t.select().with_for_update().limit(2)
+        if testing.against("oracle>=12"):
+            with expect_raises_message(exc.DatabaseError, "ORA-02014"):
+                connection.execute(stmt).fetchall()
+        else:
+            eq_(
+                connection.execute(stmt).fetchall(),
+                [(1, 1), (2, 7)],
+            )
 
         # here, its impossible.  But we'd prefer it to raise ORA-02014
         # instead of issuing a syntax error.
-        assert_raises_message(
-            exc.DatabaseError,
-            "ORA-02014",
-            connection.execute,
-            t.select().with_for_update().limit(2).offset(3),
-        )
+        with expect_raises_message(exc.DatabaseError, "ORA-02014"):
+            connection.execute(t.select().with_for_update().limit(2).offset(3))
 
 
 class UnicodeSchemaTest(fixtures.TestBase):