support new Oracle 12c OFFSET / TOP N

[Mike Bayer]

Let's support at least a subset of https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1 to fulfill LIMIT/OFFSET.

This is a big enough deal that I'd like to at some point get an Oracle 12c CI environment going.

There should be a manual flag to select either style, and if set to None it will auto-select based on detected Oracle version.

[Mike Bayer]

workaround:

from sqlalchemy.sql import compiler


def patch_engine(engine):
    class Oracle12CCompiler(engine.dialect.statement_compiler):
        def visit_select(self, element, **kw):
            return compiler.SQLCompiler.visit_select(self, element, **kw)

        def limit_clause(self, select):
            # This code overrides the empty method in OracleCompiler:
            my_limit_clause = ""
            if select._offset:
                my_limit_clause += " OFFSET %d ROWS" % select._offset
            if select._limit:
                my_limit_clause += " FETCH FIRST %d ROWS ONLY" % select._limit
            return my_limit_clause
    engine.dialect.statement_compiler = Oracle12CCompiler


if __name__ == '__main__':
    from sqlalchemy import create_engine, select, table, column
    import mock

    e = create_engine(
        "oracle://",

        # only because I don't have cx_oracle installed here, this
        # is not part of the example
        module=mock.Mock(version="10.2", paramstyle="named")
    )
    patch_engine(e)

    t = table('foo', column('bar'))
    stmt = select([t]).limit(5).offset(12)

    """
    SELECT foo.bar
    FROM foo OFFSET 12 ROWS FETCH FIRST 5 ROWS ONLY
    """
    print stmt.compile(e)

[Mike Bayer]

pushing this out for now as there's a workaround and we still need to get 12c running. the community can submit patches for this to move the milestone up.