How can I realize paging on an OracleDB table when I do not know the exact setup of the table?
I am currently developing an addressbook-connector to several different remote data sources (such as MS-Exchange, GroupWise, all kinds of Directory Services, etc.) and also different SQL databases. Among those, OracleDB.
The user is able to specify the remote database, table name as well as the column names, such as first_name, last_name, company_etc, etc. But he is free to leave almost any column blank, hence I cannot rely on a single column being actually set.
Therefore, the ORDER BY portion of the default Oracle paging query must be realized in a similar fashion as the MSSQL query ( ORDER BY SELECT(NULL) ).
I do not require strict sorting reliability
PostgreSQL:
SELECT *
FROM table_name
OFFSET ? LIMIT ?
MySQL:
SELECT *
FROM table_name
LIMIT ?,?
MSSQL:
WITH select_result AS
(
SELECT *,
Row_number() OVER (ORDER BY SELECT(NULL))
AS RowNum FROM table_name
)
SELECT * FROM select_result
WHERE RowNum BETWEEN ? AND ?
OracleDB:
???
