* This blog was last tested on OAC 5.9 + Oracle Application Express 20.2.0.00.20 + ADW 19c by Jay Pearson (Oracle A-Team) *
This article documents a method by which RESTful web-services can be created in Oracle Application Express (APEX) and exposed to replicate the SQL from an Oracle Analytics Cloud (OAC) analysis report.
OAC report has been created from this dataset.
RESTful services can be created from the SQL Workshop and then the 'RESTful Services' option:
Create Module, Template, and Handler.
For this example, use the URL Prefix 'demo' and create a 'GET' handler which will respond by selecting all the records from the SAMP_REVENUE_DENORM table based on the SQL statement of:
'select * from OAC_USER.SAMP_REVENUE_DENORM', as shown below.
Once created, test the Full URL in a browser to confirm that the JSON formatted data will be returned as expected.
This simple example showed how to extract data from a specific table, but it may be necessary to use some more complex SQL and include filters to limit the data returned.
The below analysis filters on Company.
Within OAC a user with Administrator rights, can retrieve the physical SQL.
Using the 'Administration' option, and then 'Manage Sessions' - the SQL that OAC runs can be located and examined.
With a little work, and some testing within the SQL Workshop, the SQL statement can be recreated. In this case the following SQL statement was reversed engineered to mimic the OAC report.
This SQL can then be used to create a new RESTful 'GET' method.
This time a parameter for 'company' will be created, which is included as a filter in the select statement.
This parameter can be passed in the URL of the RESTful service.
Multiple parameters can be created in this manner to filter the dataset further and minimize the data returned.
This article demonstrated how the Apex RESTful services can be used to replicate OAC reports and allow data to be extracted from the reporting database via a web-service call.