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:

• Up-to-Date Information:

  • You always get the latest data directly from the source.

• Simplified data management:

  • Eliminate the need for data synchronization logic and error handling.

  • Reduce complexity in maintaining data consistency.

• Security and Compliance:

  • Centralized data storage can simplify compliance with data protection regulations.

  • Reduces the risk of data breaches from local storage vulnerabilities.

• Storage Costs:

  • Avoid the storage costs associated with replicating data.

The Case for Synching

Syncing the data to local tables also has many benefits, including:

1. 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).

2. Reliability:

   • Less vulnerable to network issues, API downtimes, and rate limits.

   • There is no need to worry about error handling and fallback mechanisms.

3. 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

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

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

🎂 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:

1. 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.

2. 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.