Remote Report (Oracle APEX Web Source)

Welcome back to my blog. It’s a while since my last blog post, but the lack of interesting or worth sharing stuff made me go on hold for over a half year. That changed with the last request I got. Due to some reasons we have internal application which we currently develop, but we want to expose part of it to the big bad Internet. Due to the nature of the Internet we won’t be risking exposing the application itself and we only create reporting application, which will connect to only specific objects in database remotely, so we limit the risk of exposing data we don’t want to expose.

I’ve search the Internet for some inspiration and even though I found how to setup the remote reports and web source, I was unable to find how to work with external filtering and sorting, as I didn’t want to create separate modules and resource handlers in ORDS RESTful Services for each report.

For now let’s start from the beginning – creating the web source on (for us) remote server, which will be providing us the data.

To create the emp resource handler go to your SQL Workshop and choose ORDS Based RESTful Services.

1. Register your schema for ORDS RESTful Services, if you haven’t done it already.
2. Create new module: hr.
3. Create new resource template: emp.
4. Create new resource handler:
   1. Method: GET
   2. Source Type: Collection Query
   3. Format: JSON
   4. Source: SELECT * FROM emp

This will be all the work you need to do on the remote server, just note the full URL you have for the ORDS Handler Definition. In my case it’s: https://apex.oracle.com/pls/apex/dominion/hr/emp. You can protect the module, template or handler by privileges and roles, but that might be the topic for some other article.

Let’s move to our application, where we want to consume the web source we created above. Here you have to first create Web Source Module to consume data.

To create the Web Source Module go through the wizard in Shared Components – Web Source Modules – Create:

1. Create Web Source Module: From scratch
2. Web Source Type: Oracle REST Data Services
3. Name: emp
4. URL Endpoint: https://apex.oracle.com/pls/apex/dominion/hr/emp
5. Remote Server: – Create New – (if you don’t have it already created)
6. Base URL: https://apex.oracle.com/pls/apex/dominion/hr/
7. Service URL Path: emp
8. Authentication Required: no

After that you should click on discover and get the data profile of the web source parsed from JSON into a table and you finally can create the web source module. If you open the module, you have the GET operation there and you can test it to see if it works correctly.

Now with both the data source and the web source module on our server setup, finally, we can start creating the report. Open your page and add new classic report where the source location will be Web Source and module is the newly created emp. Save your page and run it. You should see all the data in your report.