top of page

Integrating Oracle APEX (18.2) with Microsoft Power BI (Oracle RESTful Data Services)

Lately we can see how much has been spoken of Business Intelligence, the famous BI, and the tools that encompass this world. One of them, well known by signal, is Microsoft's Power BI. Thinking about this and how Oracle APEX has been prominent in the development of web and mobile applications, how could we combine the best of the two? I mean, how could we use Power BI to graphically display data generated in Oracle APEX and bring value to a company's business through its data?

The answer to this question seems very clear: RESTful Services in Oracle APEX.

Note: My intention is not to speak of the capabilities of Power BI, but of the ability to integrate Oracle APEX with other systems, creating Web Service Modules applied to the REST model.

What is Oracle REST Data Services (ORDS)?

According to Oracle: The Oracle REST Data Service aims to facilitate the development of REST interfaces to relational data in the Oracle Database, the Oracle Database 12c JSON Document Store, and the Oracle NoSQL Database. Using ORDS, we easily create HTTP (S) methods (GET, POST, PUT, DELETE, etc.)

Note: ORDS is included with both Oracle Database and Oracle SQL Developer installs.

Integrating Oracle APEX with Power BI

From now on, we'll see how to create modules in Oracle APEX through REST Data Services to perform the integration of our application with Microsoft Power BI.

To illustrate our example, we will simulate a sales order table so that we can make this data available to Power BI. Then we will have the following model:

Table Creation Scripts

create table product
 product_id number generated as identity nocache,
 name       varchar2(45) not null unique,
 price      number(11,2) not null,
 constraint pk_product primary key (product_id)
create table customer
 customer_id number generated as identity nocache,
 name        varchar2(45) not null,
 age         number not null,
 constraint  pk_customer primary key (customer_id)
create table status_order
 status_order_id number generated as identity nocache,
 status          varchar2(30) not null unique,
 status_code     number not null unique,
 constraint pk_status_order primary key (status_order_id)
create table customer_order
 order_id    number generated as identity nocache,
 customer_id number not null,
 product_id  number not null,
 qty_product number not null,
 total       number(11,2) not null,
 order_date  date not null,
 status_id   number not null,
 constraint pk_order primary key (order_id),
 constraint fk_customer_order foreign key (cutomer_id) references customer (customer_id),
 constraint fk_product_order foreign key (product_id) references product (product_id),
 constraint fk_status_order foreign key (status_id) references status_order (status_order_id)

Creating RESTful Services in APEX

To create the REST service with the method of accessing our data, we will follow the following steps:

1. In Oracle APEX, in the SQL Workshop menu we select the RESTful Services option.

2. When we use RESTful Services for the first time, the message "Schema not registered with ORDS" will appear. To register, click the Register Schema with ORDS.

3. A window will open so we can provide some ORDS Schema attributes. We define RESTful Access as enabled and enter an alias for our Schema. We can also opt for the installation of an example service and authorization to access our service. We click on Save Schema Attributes and then, our Schema will be registered with ORDS.

For security reasons and as a good practice, it is recommended not to define the Schema alias as the official Schema name in the database. In our example, the Schema alias is apxservices.

4. Now, we need to create the module that will contain the parameters and methods of accessing our data. To do this, within RESTful Services, go to Modules and click Create Module. Some fields will appear to define the module settings. We define a Module Name = apex.learning and Base Path = /apxlr/. To conclude, click on Create Module.

Base Path will compose the URL that will be generated for access to our Web Service.

5. After creating our module, we will create a template. Inside the created module, in Resource Templates, click Create Template. In the fields that will be made available, we define for the Template URI that will be used to access specific features of our database. In our case, we will return the Sales Order data and therefore define the name "orderinfo/". The URI Template can also include a bind variable such as "orderinfo/:id", for example. The priorities we leave as zero (0) and the HTTP Entity Tag Type as "Secure Hash". To conclude, we created in Create Template.

As we can see below, the URI Template and the bind variable - if included - will also compose our URL.

6. To finish, we will create the method and the query to bring the data that we want. To do this, within the template that we just created, click Create Handler in the Resource Handlers region. In the fields that will appear, we select the GET method and the source type Query, the return format will be JSON. In Source, we will select the data from the VW_ORDERS view, which contains the sales order data. To complete the creation of the Handler, click Create Handler.


  select a.order_id, as customer, as product,
 from customer_order a join customer b on (a.customer_id = b.customer_id)
                       join product c on (a.product_id = c.product_id)
                       join status_order d on (a.status_id = d.status_order_id)

Once this is done, our RESTful service will be complete. The resulting URL that will give access to the data is now:

Integration with Power BI

To integrate our application with power BI we will follow the following steps:

1. In Power BI, on the Home tab, click Get Data and select Web.

2. In the window that will open, we enter our URL and click OK.

3. After clicking OK, we will see that Power BI returns the data as a list containing several objects of type Record. To display the data as in a table, on the Transform tab, click Convert To Table and everything will be fine.

After the transformation, we will have our data in a table.

Our data is now available on Power BI and can be used as desired. In the example below, we have a total of sales orders per month, separated by Status.


We were able to see how easy it is to integrate the applications developed in Oracle APEX with other applications and/or solutions. In our example, Power BI.

While demonstrating integration with Power BI, when possible, I recommend using the Oracle APEX graphics , which use an Oracle JET based engine, which, in addition to being a native APEX solution, is quite powerful and useful for analysis and data management, as demonstrated in the example below.

In practice...

To see the Sales Order data and the charts used in this example, go to the demo application below.

Demo Application

User (default): demo

Password: demo

8,393 views6 comments

Recent Posts

See All


ahsan shah
ahsan shah
Apr 08, 2022

Brilliant Work . Thanks Man !!


eric novaes
eric novaes
Aug 24, 2020

Hi Lucas. How are you My name is Eric and I'm Brazilian, I'm starting at the apex. Congratulations for the excellent blog and your posts. I have a small doubt as to how I would run a select list without having to apply a submit on the entire page, as it would be to apply the query only in an interactive report? hug and congratulations for the content.


Enock Oloo
Enock Oloo
May 31, 2020


Iam still using Apex 5.1 but would like to generate my pdf reports in power bi we have installed, how can I achieved this without extra authentication layer. Like when a button is clicked on Apex page users get pdf downloads?



Naeem Alsaadi
Naeem Alsaadi
Sep 28, 2019

good and clear tobic , i follow the steps and it work with me ,, thanks Lucas


Lucas Rayner
Lucas Rayner
Jan 09, 2019

Hi Alan,

Thank you for your comment!

If your goal is to create authentication for your REST service created in APEX, you can create a privilege by adding to it the role "RESTful Services" and the module you want to request authentication. After that, create a new user in the APEX or use an existing one, assigning him a role "RESTful Services" and that's it. When attempting to place a call, you will be prompted for the user and password with role assignment.

If it is not clear, I am available to assist you.

bottom of page