DEV Community

Puja Saha
Puja Saha

Posted on

Run Scheduled OIC Integrations from Oracle APEX: A Self-Service Launcher with Runtime Parameters

#apex #oic #plsql #oracle

How to build a metadata-driven APEX screen that runs scheduled orchestrations in Oracle Integration Cloud on demand, with parameters, guardrails and live status tracking.

Introduction

Scheduled orchestrations in Oracle Integration Cloud (OIC) usually run on a fixed timetable. But support teams often need to run one right now:

Re-run a failed sync for a specific date range
Pull a single record after fixing it at the source
Refresh master data before a business user goes ahead

The usual approach is to log in to the OIC console, find the integration, click Submit Now, type the schedule parameters by hand, and then watch the monitoring page. That's slow and easy to get wrong. It also means everyone who needs to run a job must have access to the OIC console.

I built an Oracle APEX screen that handles this from one place. An administrator picks an integration, enters any schedule parameters, and clicks a button. APEX sends the run to OIC, logs it in Oracle ATP and shows its status live until it finishes.

This article covers the design, the ATP tables, the PL/SQL and the APEX pages, so you can build the same thing yourself.

What we are building
Plain Text
APEX Admin Page
│ select integration + enter parameters
▼
PL/SQL package (ATP)
│ 1. validate 2. log run 3. call OIC REST API
▼
OIC ──► Scheduled orchestration runs once with the supplied parameters
▲
│ poll instance status every few seconds
APEX page (Ajax) ◄── run log table updated by PL/SQL

Design principles:

  1. Configuration-driven. Adding a new integration means adding a row in a table, not writing code.
  2. Parameters are defined, not typed blindly. Each integration declares the parameters it accepts.
  3. Every run is logged. You always know who ran what, when, with which values, and how it ended.
  4. Guardrails. Only admins can run jobs, and integrations that can't safely run twice at once are blocked from doing so.
  5. No credentials in the browser. OIC is called from the database through an APEX Web Credential.

Step 1: Design the ATP tables

1.1 Integration registry

This is the master record for each OIC integration you want to run from APEX. In my application it's maintained through the Integrations (admin) report and form pages.

SQL
CREATE TABLE intg_registry (
integration_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
integration_code VARCHAR2(50) NOT NULL UNIQUE,
integration_name VARCHAR2(200) NOT NULL,
oic_integration_id VARCHAR2(200) NOT NULL, -- OIC integration identifier
oic_version VARCHAR2(50) NOT NULL, -- e.g. 01.00.0000
max_batch_size NUMBER DEFAULT 200,
batch_timeout_mins NUMBER DEFAULT 60,
self_incompatible CHAR(1) DEFAULT 'Y' CHECK (self_incompatible IN ('Y','N')),
notification_enabled CHAR(1) DEFAULT 'N' CHECK (notification_enabled IN ('Y','N')),
notification_email VARCHAR2(500),
active_flag CHAR(1) DEFAULT 'Y' CHECK (active_flag IN ('Y','N')),
created_by VARCHAR2(100),
creation_date TIMESTAMP DEFAULT SYSTIMESTAMP,
last_updated_by VARCHAR2(100),
last_update_date TIMESTAMP
);

Why these columns matter:

  • oic_integration_id and oic_version are all the REST API needs to find the right integration.
  • self_incompatible stops two runs of the same integration from going at the same time.
  • batch_timeout_mins lets the tracker mark a run as failed if it gets stuck.

1.2 Parameter definitions

Each scheduled orchestration declares its own schedule parameters in OIC. Store the same definitions in ATP, so the APEX screen knows which fields to show and how to check them.

SQL
CREATE TABLE intg_param_def (
param_def_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
integration_id NUMBER NOT NULL REFERENCES intg_registry,
param_name VARCHAR2(100) NOT NULL, -- must match the OIC schedule parameter name exactly
param_label VARCHAR2(200),
data_type VARCHAR2(20) DEFAULT 'STRING'
CHECK (data_type IN ('STRING','NUMBER','DATE')),
default_value VARCHAR2(4000),
required_flag CHAR(1) DEFAULT 'N',
display_seq NUMBER DEFAULT 10,
CONSTRAINT intg_param_def_uk UNIQUE (integration_id, param_name)
);

1.3 Run log

Every launch gets one row here, and the status tracker updates that row until the run ends.

SQL
CREATE TABLE intg_run_log (
run_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
integration_id NUMBER NOT NULL REFERENCES intg_registry,
request_payload CLOB, -- parameters sent to OIC
oic_instance_id VARCHAR2(200),
http_status NUMBER,
run_status VARCHAR2(30) DEFAULT 'SUBMITTED',
-- SUBMITTED / QUEUED / IN_PROGRESS / COMPLETED / FAILED / ERROR
status_message VARCHAR2(4000),
submitted_by VARCHAR2(100),
submitted_at TIMESTAMP DEFAULT SYSTIMESTAMP,
completed_at TIMESTAMP
);
 
CREATE INDEX intg_run_log_n1 ON intg_run_log (integration_id, run_status);

Step 2: Create the OIC Web Credential in APEX

Never put OIC credentials in page items or JavaScript.

Go to Workspace Utilities → Web Credentials.
Create a credential, for example OIC_RUNTIME_CRED. Use OAuth2 Client Credentials, which is recommended for OIC Gen 3, or Basic authentication for a service account.
Under Valid for URLs, enter only your OIC host.

The OIC user or client needs permission to run integrations, such as the ServiceInvoker or ServiceMonitor role, depending on your security setup.

Step 3: Build the PL/SQL package

3.1 Submit a run

For a scheduled orchestration, OIC provides a REST endpoint that runs the integration's schedule once ("Submit Now"). It accepts schedule parameters as name/value pairs:

Plain Text
POST /ic/api/integration/v1/integrations/{id}|{version}/schedule/jobs

SQL
CREATE OR REPLACE PACKAGE BODY intg_launcher_pkg AS
 
c_oic_base CONSTANT VARCHAR2(200) := 'https://<your-oic-host>';
c_cred CONSTANT VARCHAR2(100) := 'OIC_RUNTIME_CRED';
 
PROCEDURE submit_run (
p_integration_id IN NUMBER,
p_params IN CLOB, -- JSON object: {"P_FROM_DATE":"2026-09-01", ...}
p_run_by IN VARCHAR2,
p_run_id OUT NUMBER,
p_status OUT VARCHAR2,
p_message OUT VARCHAR2 )
IS
l_reg intg_registry%ROWTYPE;
l_running NUMBER;
l_body CLOB;
l_response CLOB;
l_url VARCHAR2(1000);
BEGIN
SELECT * INTO l_reg
FROM intg_registry
WHERE integration_id = p_integration_id
AND active_flag = 'Y';
 
-- Guardrail: block concurrent runs for self-incompatible integrations
IF l_reg.self_incompatible = 'Y' THEN
SELECT COUNT(*) INTO l_running
FROM intg_run_log
WHERE integration_id = p_integration_id
AND run_status IN ('SUBMITTED','QUEUED','IN_PROGRESS');
IF l_running > 0 THEN
p_status := 'ERROR';
p_message := 'A run is already in progress for this integration.';
RETURN;
END IF;
END IF;
 
-- Build the OIC schedule-parameter payload
apex_json.initialize_clob_output;
apex_json.open_object;
apex_json.open_object('scheduleParams');
FOR p IN (SELECT jt.k, jt.v
FROM JSON_TABLE(p_params, '$.*'
COLUMNS (k VARCHAR2(100) PATH '$.name',
v VARCHAR2(4000) PATH '$.value')) jt)
LOOP
apex_json.write(p.k, p.v);
END LOOP;
apex_json.close_object;
apex_json.close_object;
l_body := apex_json.get_clob_output;
apex_json.free_output;
 
INSERT INTO intg_run_log (integration_id, request_payload, submitted_by)
VALUES (p_integration_id, l_body, p_run_by)
RETURNING run_id INTO p_run_id;
 
l_url := c_oic_base || '/ic/api/integration/v1/integrations/'
|| utl_url.escape(l_reg.oic_integration_id || '|' || l_reg.oic_version, TRUE)
|| '/schedule/jobs';
 
apex_web_service.g_request_headers.delete;
apex_web_service.set_request_headers('Content-Type', 'application/json');
 
l_response := apex_web_service.make_rest_request(
p_url => l_url,
p_http_method => 'POST',
p_body => l_body,
p_credential_static_id => c_cred);
 
UPDATE intg_run_log
SET http_status = apex_web_service.g_status_code,
oic_instance_id = JSON_VALUE(l_response, '$.runId'),
run_status = CASE WHEN apex_web_service.g_status_code BETWEEN 200 AND 299
THEN 'QUEUED' ELSE 'ERROR' END,
status_message = SUBSTR(l_response, 1, 4000)
WHERE run_id = p_run_id;
COMMIT;
 
p_status := CASE WHEN apex_web_service.g_status_code BETWEEN 200 AND 299
THEN 'SUBMITTED' ELSE 'ERROR' END;
p_message := 'HTTP ' || apex_web_service.g_status_code;
END submit_run;

Note: Check the exact response attribute that holds the run or instance ID in your OIC version. Test the endpoint in Postman first and adjust the JSON_VALUE path to match.

The page builds the p_params JSON from the parameter definitions as [{"name":"P_FROM_DATE","value":"2026-09-01"}, ...]. The code turns that into the scheduleParams object OIC expects.

3.2 Check status

SQL
PROCEDURE check_run (
p_run_id IN NUMBER,
p_status OUT VARCHAR2,
p_message OUT VARCHAR2,
p_is_terminal OUT VARCHAR2 )
IS
l_log intg_run_log%ROWTYPE;
l_timeout NUMBER;
l_response CLOB;
BEGIN
SELECT * INTO l_log FROM intg_run_log WHERE run_id = p_run_id;
 
IF l_log.run_status IN ('COMPLETED','FAILED','ERROR') THEN
p_status := l_log.run_status; p_message := l_log.status_message;
p_is_terminal := 'Y'; RETURN;
END IF;
 
SELECT batch_timeout_mins INTO l_timeout
FROM intg_registry WHERE integration_id = l_log.integration_id;
 
IF l_log.submitted_at < SYSTIMESTAMP - NUMTODSINTERVAL(l_timeout, 'MINUTE') THEN
UPDATE intg_run_log
SET run_status = 'FAILED', status_message = 'Timed out', completed_at = SYSTIMESTAMP
WHERE run_id = p_run_id;
COMMIT;
p_status := 'FAILED'; p_message := 'Run exceeded timeout.'; p_is_terminal := 'Y';
RETURN;
END IF;
 
l_response := apex_web_service.make_rest_request(
p_url => c_oic_base || '/ic/api/integration/v1/monitoring/instances/'
|| l_log.oic_instance_id,
p_http_method => 'GET',
p_credential_static_id => c_cred);
 
p_status := UPPER(NVL(JSON_VALUE(l_response, '$.status'), 'IN_PROGRESS'));
p_is_terminal := CASE WHEN p_status IN ('COMPLETED','FAILED','ABORTED') THEN 'Y' ELSE 'N' END;
p_message := 'OIC status: ' || p_status;
 
UPDATE intg_run_log
SET run_status = p_status,
completed_at = CASE WHEN p_is_terminal = 'Y' THEN SYSTIMESTAMP END
WHERE run_id = p_run_id;
COMMIT;
END check_run;
 
END intg_launcher_pkg;
/

Step 4: Build the APEX pages

Page A: Integration registry (interactive report and modal form)

  • Interactive report on intg_registry, joined to a systems table for source and target names.
  • An edit link opens a modal form for the integration's settings: OIC ID, version, batch size, timeout, self-incompatible flag and notifications.
  • Authorization: set the page's Required Role to an admin authorization scheme.
  • Add an interactive grid on intg_param_def so admins can manage parameter definitions for the chosen integration.

Page B: Launcher

  1. Select list P_INTEGRATION_ID showing active integrations from intg_registry.
  2. Parameters region, either:
  • A dynamic region that generates input fields from intg_param_def, or
  • An editable interactive grid on a collection pre-filled with each parameter's name and default value. This is simpler and works well.
  1. A Run Now button wired to a dynamic action (JavaScript) instead of a page submit.

On-demand (Ajax callback) process: SUBMIT_RUN

SQL
DECLARE
l_run_id NUMBER;
l_status VARCHAR2(30);
l_message VARCHAR2(4000);
BEGIN
intg_launcher_pkg.submit_run(
p_integration_id => apex_application.g_x01,
p_params => apex_application.g_x02,
p_run_by => :APP_USER,
p_run_id => l_run_id,
p_status => l_status,
p_message => l_message);
 
apex_json.open_object;
apex_json.write('runId', l_run_id);
apex_json.write('status', l_status);
apex_json.write('message', l_message);
apex_json.close_object;
END;

On-demand process: CHECK_RUN

SQL
DECLARE
l_status VARCHAR2(30);
l_message VARCHAR2(4000);
l_terminal VARCHAR2(1);
BEGIN
intg_launcher_pkg.check_run(
p_run_id => TO_NUMBER(apex_application.g_x01),
p_status => l_status,
p_message => l_message,
p_is_terminal => l_terminal);
 
apex_json.open_object;
apex_json.write('status', l_status);
apex_json.write('message', l_message);
apex_json.write('terminal', l_terminal);
apex_json.close_object;
END;

Dynamic action on Run Now (Execute JavaScript):

JavaScript
(function () {
var btn = $(this.triggeringElement);
var spinner = apex.util.showSpinner($('body'));
btn.prop('disabled', true);
 
function done() { spinner.remove(); btn.prop('disabled', false); }
 
function poll(runId) {
apex.server.process('CHECK_RUN', { x01: String(runId) }, {
dataType: 'json',
success: function (d) {
apex.message.showPageSuccess(d.message);
if (d.terminal === 'Y') {
done();
if (d.status !== 'COMPLETED') {
apex.message.showErrors([{ type: 'error', location: 'page',
message: d.message, unsafe: false }]);
}
apex.region('runHistory').refresh();
} else {
setTimeout(function () { poll(runId); }, 5000);
}
},
error: function () { done(); }
});
}
 
apex.server.process('SUBMIT_RUN', {
x01: $v('P_INTEGRATION_ID'),
x02: JSON.stringify(collectParams()) // build [{name, value}] from the parameter region
}, {
dataType: 'json',
success: function (d) {
if (!d.runId || d.status === 'ERROR') {
done();
apex.message.showErrors([{ type: 'error', location: 'page',
message: d.message, unsafe: false }]);
return;
}
apex.message.showPageSuccess('Submitted. Waiting for OIC...');
setTimeout(function () { poll(d.runId); }, 5000);
},
error: function () { done(); }
});
})();

  1. Add a Run History interactive report (static ID runHistory) on intg_run_log for the selected integration.

Step 5: Security checklist

  1. Use an admin-only authorization scheme on the registry and launcher pages.
  2. Store OIC credentials only in Web Credentials, restricted to the OIC URL.
  3. Validate parameters in PL/SQL against intg_param_def (required fields, data types). Never trust the browser alone.
  4. Log submitted_by on every run for auditing.
  5. Respect the self-incompatible flag so overlapping runs can't corrupt data.

Lessons learned

Treat OIC integrations as data. A registry table turns "add a new trigger" into a configuration change.
Poll from the database, not the browser. The browser only asks APEX for status. All OIC calls stay on the server.
Make timeouts explicit. A run that never reports back is still a failure, so the tracker should say so.
Keep parameter names identical to the OIC schedule parameter names. Most "parameter not received" problems come from a mismatch here.
Conclusion

With a few ATP tables, one PL/SQL package and two APEX pages, scheduled OIC orchestrations become self-service tasks: pick an integration, enter parameters, click run, and watch it finish. It cuts down on OIC console access, makes every manual run auditable, and gives support teams a consistent, safe way to re-run integrations when the business needs it.

Top comments (0)