Series: Oracle APEX in Fusion Implementations — Part 3 of 4 Published under: Oracle APEX | Oracle Integration Cloud | Oracle Fusion | Solution Architecture
In Part 2 of this series, we walked through the architecture of a custom Sales Order UI in APEX — how we structured the three layers, where validation lived, and what the submission flow looked like end to end. That post was about improving what users put into Fusion.
This post is about what happens after. Specifically: how do you give a business operations team meaningful visibility into their integrations — without handing them access to a tool they were never meant to use?
Watch on YouTube" : https://youtu.be/5UlW6OJUuoE
The Problem: The OIC Console Is Not for Everyone
Oracle Integration Cloud (OIC) is the integration backbone for most serious Fusion environments. On any given day in a mature implementation, you'll have scheduled integrations syncing data between systems, event-driven flows triggered by Fusion activity, and on-demand processes the operations team needs to fire manually. These integrations are critical infrastructure.
The OIC console is a fully capable monitoring and management tool — for integration developers. It surfaces instance-level execution logs, fault details, payload inspection, resubmission controls, and configuration management all in one place. It's comprehensive, and it's genuinely useful if you know what you're looking at.
The operations manager who needs to know whether last night's inventory sync completed is not that person.
On a recent Fusion engagement, the operations team's integration visibility looked like this: they emailed IT when something seemed wrong. IT checked the OIC console. IT replied. On a good day, that loop took 20 minutes. On a bad morning, before IT was at their desks, it took longer. Downstream decisions — purchasing, fulfillment prioritization, reporting — waited on that loop.
The ask was simple: give us a way to see integration health ourselves, in plain language, without depending on IT for every question.
The Architecture: Three Components, One Clear Flow
The solution we built has three components working together:
DBMS_SCHEDULER Job
↓ (runs every hour)
BIZ_OIC_MONITOR_PKG → OIC Monitoring REST API
↓ (upserts results)
BIZ_OIC_MONITORING_TBL
↓ (read at page load)
APEX Dashboard
Each piece has a single responsibility. The scheduler triggers the data load. The package handles the API call and data persistence. The table serves as the fast, stable data layer that the APEX UI reads from. The dashboard presents the result.
Nothing in the APEX UI calls OIC directly at runtime. That decision — keeping the UI layer decoupled from the live API — turned out to be one of the most important ones we made.
Component 1: The Scheduler
We used DBMS_SCHEDULER rather than an APEX Automation for the background refresh job. The reason is reliability: DBMS_SCHEDULER runs at the database level, independent of any active APEX session or application state. APEX Automations are convenient for lighter use cases, but for something the operations team depends on every morning, we wanted the job to run regardless of what was happening at the application layer.
The job is configured to run every hour. On each execution, it calls BIZ_OIC_MONITOR_PKG.LOAD_MONITORING_DATA — the package that handles everything from authentication through data persistence.
Component 2: The Monitor Package
This is where the real work happens. The package (BIZ_OIC_MONITOR_PKG) does five things in sequence on each execution:
Step 1 — Token Retrieval
The first call is to BIZ_OIC_TOKEN_PKG.GET_TOKEN. This package checks whether a valid Bearer token is cached in the config table. If the token is still within its validity window, it returns the cached value immediately. If it has expired, it authenticates against the Oracle IDCS token endpoint and caches the new token before returning it.
This approach — token caching with auto-refresh — keeps the monitoring load fast and avoids unnecessary authentication calls. OIC Bearer tokens are valid for approximately 7 days, so in practice the refresh happens rarely. We store the token expiry with a one-hour buffer to avoid edge cases where the token expires mid-execution.
Step 2 — Paginated API Call
The OIC monitoring API returns integration instance data in pages. We call it in a loop, incrementing the offset on each iteration, until the response returns hasMore: false.
One technical detail worth noting: Oracle's JSON_VALUE function does not support the $.items.size() path expression that works in some other JSON implementations. To get the item count per page, we use TO_NUMBER(JSON_VALUE(response, '$.totalResults')) instead. This was a non-obvious issue that caused us time in early testing.
Similarly, the & character in the URL query string cannot be written directly in a PL/SQL string that may be processed through SQL Workshop — it gets interpreted as a substitution variable. We use CHR(38) to concatenate the ampersand character safely:
sql
l_url := C_BASE_URL || C_MON_PATH
|| '?integrationInstance=' || C_INSTANCE
|| CHR(38) || 'q={timewindow : ''1h''}'
|| CHR(38) || 'limit=25'
|| CHR(38) || 'offset=' || l_offset;
Small detail. Significant frustration if you don't know it.
Step 3 — JSON Processing and Upsert
For each item in the response, the package extracts the relevant fields — instance ID, integration name, flow type, status, duration in milliseconds, invoked-by identity, and timestamps — and calls an upsert procedure.
The upsert logic checks whether the instance ID already exists in the monitoring table. If it does, it updates the mutable fields: status, duration, completed date, and fault flag. If it doesn't, it inserts a new record.
This matters for in-progress integrations. An integration that was running at the time of the previous load will appear in subsequent loads with a completed status. The upsert pattern captures that update correctly. A delete-and-reload approach would lose the in-progress record between cycles or introduce inconsistencies.
Duration is stored in two forms: the raw millisecond value for sorting and calculations, and a pre-formatted HH:MM:SS string for direct display. The formatting happens in the package at load time, not at query time — a small optimization that makes the APEX report queries simpler and faster.
Step 4 — Metadata Update
After the load completes, the package writes the current timestamp to a MON_LAST_LOAD_DATE entry in the config table. The APEX dashboard displays this timestamp prominently — users can always see exactly how current the data is, which matters for their operational decisions.
Step 5 — Cleanup
The final step in each execution is CLEANUP_OLD_RECORDS. This reads a configurable retention period from the config table (default: 7 days) and deletes records older than that threshold. Retention is adjustable without a code change — the operations team can extend it if they need longer history for a specific investigation period.
Component 3: The APEX Dashboard
The APEX page reads entirely from BIZ_OIC_MONITORING_TBL. There is no live OIC API call at page load time. This was a deliberate choice: OIC API calls have variable latency, and a monitoring dashboard that takes 8 seconds to load because it's making live API calls is a dashboard people stop using.
The dashboard surfaces:
Summary metrics at the top — total runs in the last 24 hours, count of successful vs failed, count of runs with recoverable faults. These are simple aggregations from the monitoring table, displayed as highlight cards so the operations team can assess overnight health at a glance.
Integration run history — an interactive report showing each integration instance with its name, status, flow type, duration (formatted), timestamps, and fault indicator. Status is displayed with colour-coded badges — green for completed, red for failed, amber for faults — so the visual scan is immediate.
Last refresh timestamp — displayed in the page header. The operations team knows the data is at most one hour old. If they need fresher data for a specific situation, the page includes a manual refresh button that calls the load package on demand.
Drill-down on failures — failed instances link to a detail view that shows the full OIC instance ID, which the team can use to look up the specific error in the OIC console if deeper investigation is needed. The dashboard doesn't try to replicate OIC's fault detail UI — it just surfaces enough information to decide whether to escalate and what to say when doing so.
What This Replaced
Before this dashboard, the operations team's integration monitoring workflow was:
Notice something downstream seemed wrong (delayed report, missing records, fulfillment hold)
Email or message IT
Wait for IT to check OIC and respond
Decide whether to escalate or wait
After:
Check the dashboard at start of day as part of normal routine
See immediately if anything overnight is flagged
Escalate proactively with the instance ID and error summary already in hand — or confirm everything is healthy and move on
The change isn't just convenience. It shifts the team from reactive to proactive. Failures that previously surfaced through downstream symptoms are now visible the morning after they happen, before anyone feels the impact.
Technical Decisions Worth Carrying Forward
A few principles from this build that apply broadly to similar integrations:
Cache tokens; don't re-authenticate on every call. Authentication overhead adds up across scheduled jobs. A token management layer that handles caching and refresh is worth building properly once.
Use DBMS_SCHEDULER for business-critical background jobs. APEX Automations are convenient but DBMS_SCHEDULER is more observable, more controllable, and independent of application state.
Upsert over delete-and-reload for API-sourced monitoring data. In-progress records, partial failures, and retry scenarios all behave more predictably with upsert logic.
Store timestamps as TIMESTAMP, display as formatted strings. Sorting, filtering, and calculations work correctly on the stored type. Formatting at the display layer keeps the data clean.
CHR(38) for ampersands in PL/SQL URL strings. This is a minor but genuinely non-obvious gotcha in Oracle SQL Workshop that will catch you if you don't know it.
What's Next
Part 4 of this series is a different kind of post: when APEX is the wrong answer. We'll cover the scenarios where a Fusion extension, a VB Studio application, or a pure OIC solution is a better fit — and how to make that call early enough in a project to avoid backtracking.
Honest guidance on when not to use a tool is, we think, more useful than advocacy. Part 4 will be that.
Building a Fusion environment and wondering whether OIC monitoring visibility is a problem worth solving before go-live? Reach out — it almost always is, and it's much easier to build before operations teams have formed habits around working around the gap.
Also in this series:
Part 1: Why Oracle APEX Belongs in Your Oracle Fusion Implementation
Part 2: Building a Custom Sales Order UI in APEX That Feeds Oracle Fusion
Part 4 (coming): When NOT to Use APEX — and What to Use Instead
Tags: Oracle APEX | Oracle Integration Cloud | OIC | Oracle Fusion | DBMS_SCHEDULER | REST API | PL/SQL | Solution Architecture | Operations Monitoring | Low-Code
