Creating an APEX application which spans across multiple schemas

Oracle APEX (Application Express) comes for free with Oracle’s ADB (Autonomous Database) and is also free for on-prem or DBCS users.

Oracle APEX is widely used for building Web Applications which can even be viewed on Mobile with an ease i.e., you can build an application which can be accessed both as Web Application and Mobile Application without any extra coding to be done (we literally mean it, no extra code to be written to use Web Application as Mobile Application, except for enabling a feature on the application while creating or post creation which is a sliding button).

Oracle APEX is also used for generating quick dashboards and interactive reports of your data which in other words is difficult to browse through on excel or csv.

For creating an Oracle APEX Application, you need to create a Workspace which is tagged to a specific Schema, allowing you to create your Application using the underlying objects of that schema.

In this blog, I’ll walk you through the process of creating a simple APEX application with data from multiple schemas.

1. Before, starting with APEX Application creation. Make sure you have tables in multiple schemas which you want to use in a single APEX application. To create an APEX application to create pages based on table s from other schema, all you need to do is grant access to the second schema from your workspace schema.

These are the two schemas I’ll be using

SAN → Workspace schema where EMP Table is present.

SAN_SCHEMA02 → Schema where DEPT02 table is present.

2. Grant access to SAN_SCHM02 from SAN schema as shown below:

3. Now, we’ll create a simple application which has only Home page for now and looks as below

Simple Application with only Home Page

Your application, when ran would look like this

4. Now, create a page to fetch “EMP” data which is in the same schema as the workspace. Let us create the page as ‘Reflow Report’ as page rendering will be good when viewed in mobile.

5. Now, will create a page to fetch “DEPT02” data which is in another schema. Let us create this page as “Reflow Report” as page rendering will be good when viewed in mobile.

Your application looks as below