Oracle SQL Support about dbextensions HOT 6 CLOSED

How do you do pagination in Oracle?

from dbextensions.

Here's an example of the SQL generated by PetaPoco:

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY Id) peta_rn, peta_base.* FROM (
SELECT Id, FirstName FROM Person
) peta_base) peta_paged WHERE peta_rn>0 AND peta_rn < 100 ORDER BY peta_rn

The only complication is pulling the ORDER BY out of the original query to use in the ROW_NUMBER, but I assume you are doing something similar for the SQL Server paging.

from dbextensions.

Thanks. Looking into it.

from dbextensions.

I have an initial implementation ready, but don't have Oracle installed to test it.
Please checkout the oracle branch and test it against your database.

from dbextensions.

Additional changes needed:

• Oracle does not like underscore-prefixed identifiers (e.g. __countQuery)
• Oracle does not accept the use of the AS keyword for subqueries in the FROM clause. See this.
• Need a way to ignore the row number column (PocoMapper ignores it, but DynamicMapper and XmlMappingReader always map all columns).

from dbextensions.

Shipped in v4.3.0, needs testing though.

from dbextensions.