I'm using ORDS 3.0 production to create a query based GET service defined as a "Collection Query". Works great, but pagination was set to 25. This was great for testing, but in production, this particular service (basically a SELECT * from a view) is intended to let an outside party download an entire copy of the view - about 20,000 rows. I tried setting pagination to "0" in SQL Developer, but it kept resetting it back to 25. Bug maybe? Or am I not understanding something? Should it be a "Query" - I'm not sure I understand the difference between Collection Query and plain Query.
Try setting pagination to maximum no. of rows you expect your web service to handle instead of zero (by default it is 25).
But setting only the pagination will not suffice, you have to set the ORDS Configuration Parameter jdbc.maxRows (by default it is 500) in defaults.xml configuration file, in order to your ORDS configuration to support you web service request.
- See the jdbc.maxRows parameter definition : http://docs.oracle.com/cd/E56351_01/doc.30/e56293/config_file.htm#BABIEFDD
- APEX Listener ignores jdbc.MaxRows limit for REST Services ?
Hope this helps!
If there is a max rows limit in ORDS, then maybe I don't want to use ORDS for this use case. I wouldn't want to set it to more than 20,000 for EVERYTHING. I think I'll write a Web PL/SQL procedure by hand for this.
So, it looks like ORDS is not the right solution for this use case. I used a PL/SQL Server page for this, and had no trouble having it generate JSON that looks very much like that generated by ORDS. Plus I made it use the same query to generate CSV - at the user's choice.
There is a max that is in the defaults.xml to prevent someone from doing bad things by accident. So it's more of a safety net than anything else. You can always change it to anything.
If PL/SQL is easier than sql , then go for it.