As part of the automation of processes inside a company that works with the Oracle Application we frequently need to get information from the E-Business Suite database to incorporate into the logic of the processes, we are automating. Having direct database access can’t be a viable option due to security constraints from the IT team, especially if the automation processes are implemented on the Cloud and the Oracle Applications works on-premise, in a server protected by VPN and firewall configurations.
In this case, accessing the database using web services can be a good alternative and one that will look more acceptable for the security and networking teams.
A very easy way to publish REST web services from an Oracle database is through the Application Express tool. This is an extremely easy development tool that comes pre-installed with the database and allows the generation of reports, data entry windows.., and also REST services.
Publishing REST services from Application Express is easily done by building a standard reports page and enabling the Allow RESTful Access option from the administration page, and then defining the report as Public from the security definition page.
The service is available through the aprex_rest services published on the based Application Express URL, and you can add the parameters of your service to the request in addition to the mandatory Application Id, Page Id, and a static Id used to identify the region in the page that you built for each specific query you want to run.
Example:
https://apex.demo.com/apex/apex_rest.getReport?app=101&page=200&reportid=200&output=JSON
From the UiPath Studio (in our case, the automation tool used) the service is consumed through the HTTP Request activity which returns a string with the document content as a Json file, which can later be processed with the Deserialize Json to convert it to an object from where we can get each of the attributes returned.
