Introduction
Between them, Oracle APEX and Oracle REST Data Services (ORDS) provide several powerful integration capabilities. If you run in an Oracle Cloud Infrastructure environment (OCI), even more integration options are available, including DBMS_CLOUD.COPY_DATA (for fetching data from object storage) and Live Share for sharing data between ATP instances.
When integrating data for consumption by an APEX Application, one of the first decisions you need to make is:
Should I synchronize data to a local table or use REST APIs / DB Links / Live Share to access the data in the target instance in real-time?
In this post, I will explore this question and provide my recommendations.
The Case for Real-Time Integration
There are some obvious reasons for fetching the data directly from the source as and when you need it:
The Case for Synching
Syncing the data to local tables also has many benefits, including:
Performance:
Reduce potential delays due to network latency.
Reduced dependency on the API server's performance and availability.
Perhaps the most critical performance impact is when you need to join data from a remote source with tables in the local database. For example, if you have items and item descriptions in a Master Data Management system and Sales Orders in an Order Management System. If you have an APEX App in the Order Management System that needs to join the Order Lines table with the items coming from a REST API Call in the MDM system, performance will be terrible (even if you use REST Source Caching).
Reliability:
Less vulnerable to network issues, API downtimes, and rate limits.
There is no need to worry about error handling and fallback mechanisms.
Reduced Complexity:
- It simplifies the APEX application as it only deals with DB tables.
How Do I Decide?
To address the question, I will discuss several examples of where I needed to make this decision and the solution I went with.
Currency Codes
Requirement
Provide a list of currency code values on an APEX sales order page.
Currency codes are available from a REST API.
The actual currency code (as opposed to an ID) is stored in the Sales Order table in the database.
Reports did not need additional information about the currency code (e.g., region).
Solution
👉
In this case, I created an APEX REST Data Source to access currency codes via the REST API. Currency codes do not change frequently, so I turned on REST Source Caching to cache the results from the REST call for 24 hours. Caching the currency codes significantly improves performance compared to calling the REST API every time.
GL Account Segment Values
Requirement
I was building an APEX application to create General Ledger Journals and interface them to Oracle Fusion ERP Cloud.
When entering Journal Lines in an Interactive Grid in the APEX Application, users need to look up the segment values for the account string (company, department, line of business, natural account, etc).
The source for these segment values was Oracle Fusion ERP Cloud.
Users needed to be able to look up values using the code or the description.
Users can also upload Journal Lines via Excel. The segment values need to be validated during the Excel upload.
Each Journal could have thousands of lines.
Reports are needed to join segment values in the journal lines table to display additional information about the segment.
Solution
👉
I created an APEX REST Source to access the Oracle Fusion ERP Cloud segment values. I then created a REST Source Synchronization to Synchronize the segment values to a local table every evening. I utilized the last updated date attributes of the segment values to only create / update values that have changed since the previous Sync. The last run date parameters were passed into the REST Source Syn at run-time using REST Source Synchronization Steps.
Quickbooks Invoices
Requirement
A customer needed to access QuickBooks invoices from an APEX Application.
Invoices can change over time (e.g., deletion, cancellation, refund).
The customer had more than 25,000 invoices and counting.
They also needed to view Quickbooks Customer information associated with the invoices.
Finally, they needed to match the QuickBooks invoices with Sales Orders created in an APEX Application.
Solution
👉
I used REST Sources and REST Source Syncing to sync Customers, Invoice Headers, and Invoice lines from QuickBooks to local tables. Instead of scheduling the Synchronizations in APEX, I used a PL/SQL wrapper procedure to call the apex_rest_source_sync.synchronize_data API first to sync customers, then Invoice Headers, and finally Invoice lines. Taking control over the sync order ensured that dependencies were met (e.g., new customers were synced before new invoices). It also allowed me to write some PL/SQL code to delete Invoices from the local tables if they did not appear in the latest Sync. I used APEX Automation to schedule my PL/SQL procedure.
🎂 Can I Have my Cake and Eat It?
I recently wrote a post titled "Managing Fusion Cloud ERP Data from APEX with No Code," demonstrating how APEX can utilize Oracle Fusion Cloud ERP REST APIs to manage Fusion data remotely with no code. This solution accessed Fusion data in real-time over a REST API with no syncing required.
We can use this solution without syncing data locally because of two things:
Fusion REST APIs allow you to pass query string parameters to limit the columns of the data being retrieved and the rows being returned. This negates some of the reasons we need to Sync Data Locally. We can ask for just the data we need when we need it.
APEX is ‘aware’ of the Fusion REST APIs and knows how to pass filter criteria from APEX native components. When a user filters a column in an Interactive Report, APEX adds the appropriate query string when calling the REST API.
Unfortunately, even this approach falls short when you need to join the results of a REST Service call with other tables in your local database. At some point, Oracle has to bring the REST API data together with the table data, which will be slow.
So the answer, at least most of the time, is No Cake for You.
Conclusion
On the surface, you would think it is always better not to replicate data across multiple instances. In practice, most remote data needs to be synced to local tables for the reasons I have outlined in this post.
