We have tried consuming a (ORDS) REST service in an Oracle APEX (v.20.2) interactive report using two different methods:
- Using REST Data Source, as defined in Shared Components
- configured as Oracle REST Data Services
- Using a Local Database source call in this format:
select * from json_table( apex_web_service.make_rest_request( p_url =>and so on. (using WITH_PLSQL hint)
Both ways work well, but the problem is when using the more clean method, 1, then the Actions menu contains less options, compared to method 2, for example the Group By is missing.
CodePudding user response：
As ORDS returns its data in pages, APEX by default assumes that not all data is available when the Interactive Report renders (only the rows which are actually seen on the report page). Thus the report options, which need to access all data, are disabled.
To change that, do the following:
- In Shared Components, navigate to your REST Data Source
- Edit the "GET" (Fetch Rows) Operation
- Enable the Allow Fetching All Rows switch.
For a normal report view, the behavior will not change; APEX will only fetch rows from ORDS, as needed to display the report page. But now, the Chart and Group By options will appear - and if you configure a GROUP BY, APEX will potentially execute multiple HTTP requests to get all required rows from your REST API. So be careful with this for REST Services potentially returning a large amount of rows ...
Hope this helps