ORDS never returns a whole table at once. Collections come back in pages, 25 rows by default, with fields that say where the page starts and whether more rows follow, and links to the next and previous pages. Clients choose the page with limit and offset. This guide pages through a REST-enabled view of 22 airports.
Before You Start
You need ORDS installed and running against your database, and a schema to work in. The examples use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder of the Oracle Database 26ai code repository on GitHub. The schema comes from Oracle Database 26ai SQL and PL/SQL Book.
ORDS in the examples answers at https://localhost:8443/ords/, and NIMBUS is REST-enabled with the URL alias nimbus. Replace the host and port with your own ORDS address. The curl commands use -k because the test server has a self-signed certificate; leave it out when your certificate is trusted. JSON responses are formatted for reading; ORDS returns them on one line.
The examples read airports, a read-only view of the AIRPORTS table published with AutoREST in How to Filter AutoREST Results with the q Parameter.
Syntax
GET .../collection/?limit=n -- rows per page GET .../collection/?limit=n&offset=m -- skip m rows first
Every page carries items, hasMore, limit, offset, count, and links with rel values such as first, next, and prev.
The First Page
Example:
curl -k "https://localhost:8443/ords/nimbus/airports/?limit=5"
Output:
{
"items": [
{
"airport_code": "DXB",
"city": "Dubai",
"country_code": "AE",
"elevation_ft": 62,
"is_hub": true
},
{
"airport_code": "LHR",
"city": "London",
"country_code": "GB",
"elevation_ft": 83,
"is_hub": false
},
{
"airport_code": "CDG",
"city": "Paris",
"country_code": "FR",
"elevation_ft": 392,
"is_hub": false
},
{
"airport_code": "FRA",
"city": "Frankfurt",
"country_code": "DE",
"elevation_ft": 364,
"is_hub": false
},
{
"airport_code": "AMS",
"city": "Amsterdam",
"country_code": "NL",
"elevation_ft": -11,
"is_hub": false
}
],
"hasMore": true,
"limit": 5,
"offset": 0,
"count": 5,
"links": [
{
"rel": "self",
"href": "https://localhost:8443/ords/nimbus/airports/"
},
{
"rel": "describedby",
"href": "https://localhost:8443/ords/nimbus/metadata-catalog/airports/"
},
{
"rel": "first",
"href": "https://localhost:8443/ords/nimbus/airports/?limit=5"
},
{
"rel": "next",
"href": "https://localhost:8443/ords/nimbus/airports/?offset=5&limit=5"
}
]
}Five airports arrive with hasMore true, and the next link already holds the URL of the second page: offset=5&limit=5.
The Last Page
Example:
curl -k "https://localhost:8443/ords/nimbus/airports/?limit=5&offset=20"
Output:
{
"items": [
{
"airport_code": "NBO",
"city": "Nairobi",
"country_code": "KE",
"elevation_ft": 5327,
"is_hub": false
},
{
"airport_code": "KTM",
"city": "Kathmandu",
"country_code": "NP",
"elevation_ft": 4390,
"is_hub": false
}
],
"hasMore": false,
"limit": 5,
"offset": 20,
"count": 2,
"links": [
{
"rel": "self",
"href": "https://localhost:8443/ords/nimbus/airports/"
},
{
"rel": "describedby",
"href": "https://localhost:8443/ords/nimbus/metadata-catalog/airports/"
},
{
"rel": "first",
"href": "https://localhost:8443/ords/nimbus/airports/?limit=5"
},
{
"rel": "prev",
"href": "https://localhost:8443/ords/nimbus/airports/?offset=15&limit=5"
}
]
}Only two airports are left, hasMore is false, and there is a prev link but no next link. A client can follow next links until they stop.
Stable Pages Need an Order
Without an order, the database may return rows in any order, and a row could appear on two pages or none. Add $orderby in the q parameter so each page continues where the last one ended:
Example:
curl -k -G "https://localhost:8443/ords/nimbus/airports/?limit=5&offset=5" \
--data-urlencode 'q={"$orderby":{"airport_code":"asc"}}'Output:
{
"items": [
{
"airport_code": "DEL",
"city": "Delhi",
"country_code": "IN",
"elevation_ft": 777,
"is_hub": false
},
{
"airport_code": "DXB",
"city": "Dubai",
"country_code": "AE",
"elevation_ft": 62,
"is_hub": true
},
{
"airport_code": "FRA",
"city": "Frankfurt",
"country_code": "DE",
"elevation_ft": 364,
"is_hub": false
},
{
"airport_code": "GRU",
"city": "S\u00e3o Paulo",
"country_code": "BR",
"elevation_ft": 2459,
"is_hub": false
},
{
"airport_code": "HKG",
"city": "Hong Kong",
"country_code": "HK",
"elevation_ft": 28,
"is_hub": false
}
],
"hasMore": true,
"limit": 5,
"offset": 5,
"count": 5,
"links": [
{
"rel": "self",
"href": "https://localhost:8443/ords/nimbus/airports/?q=%7B%22%24orderby%22:%7B%22airport_code%22:%22asc%22%7D%7D"
},
{
"rel": "describedby",
"href": "https://localhost:8443/ords/nimbus/metadata-catalog/airports/"
},
{
"rel": "first",
"href": "https://localhost:8443/ords/nimbus/airports/?q=%7B%22%24orderby%22:%7B%22airport_code%22:%22asc%22%7D%7D&limit=5"
},
{
"rel": "next",
"href": "https://localhost:8443/ords/nimbus/airports/?q=%7B%22%24orderby%22:%7B%22airport_code%22:%22asc%22%7D%7D&offset=10&limit=5"
},
{
"rel": "prev",
"href": "https://localhost:8443/ords/nimbus/airports/?q=%7B%22%24orderby%22:%7B%22airport_code%22:%22asc%22%7D%7D&limit=5"
}
]
}The second page in airport code order runs from DEL to HKG, and its next and prev links keep the same q parameter.
Things to Know
- The default page size of AutoREST collections is 25; REST module handlers use the items-per-page value of their module or handler.
- count is the number of rows on this page, not in the whole collection.
- Follow the links ORDS returns instead of building URLs yourself; they keep filters and ordering.
Related Guides
- How to Filter AutoREST Results with the q Parameter
- How to REST-Enable a Schema with ORDS.ENABLE_SCHEMA
Conclusion
ORDS pages collections with limit and offset, and each page says whether more rows follow and links to its neighbors. Sort with $orderby for stable pages, and let clients follow the next links to read a collection to the end.
