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. The complicated part for me was to figure out the way how the external filters and external order by works.

External Filter

Before we dive into how the external filter works, I have to warn you – it only works if your Web Source Module Type is Oracle REST Data Services, if you’d for some reason choose Simple HTTP, it won’t work (same with the external order by). After some trial and error I deduced the external filter is based on JSON, but there are peculiar way how to chain your conditions especially when you have OR condition added to the mix.

For example this simple SQL condition:

WHERE deptno = 20 AND (job = 'ANALYST' OR job = 'CLERK')

will translate into this JSON:

{"$and":[  {"deptno" : 20}
         , {"$or":[  {"job" : "ANALYST"}
                   , {"job" : "CLERK"}]}
        ]}

With construction like this, you have to take extra care of how you make your conditions.

External Order By

By default it sorts data based on some logic I didn’t understand. If you want to specify your own order, remove the option to sort each report column from the column attributes and use JSON to add your own sorting like this:

{"hiredate" : "ASC"}

This will conclude the article for now. As I promised, I created the report also in my APEX Corner application here: https://apex.oracle.com/pls/apex/f?p=70928:6:2257580952198::NO:::, but this time you won’t see much there – just filtered report.

Until the next post, have a nice day.

You may also like

Leave a Reply

Your email address will not be published. Required fields are marked *