Building OIC Integrations That Call Oracle Database Stored Procedures

Connecting OIC to Your Database

One of the most common OIC patterns is calling PL/SQL stored procedures in response to events from other systems. For example, when an invoice is approved in ERP Cloud, OIC can call a procedure in your on-premise reporting database to update a local summary table. The Oracle Database adapter in OIC handles the connection, parameter mapping, and transaction management.

Setting Up the Database Connection

In OIC, create a Connection using the Oracle Database adapter. Provide the database host, port, service name, and credentials. For on-premise databases, you need the OIC Connectivity Agent installed in your network to bridge the cloud-to-on-premise connection. The agent runs as a lightweight service and relays requests from OIC to your database through an outbound-only connection.

Designing the Integration

Create an App Driven Orchestration with a REST trigger. Add the Oracle Database adapter as an invoke step and select “Call a Stored Procedure” as the operation. OIC discovers the procedure’s parameters from the database metadata and generates a visual mapping interface.

Map the incoming REST request fields to the stored procedure parameters using OIC’s visual mapper. The mapper supports data type conversions, string functions, lookups, and conditional logic. For complex transformations, use XSLT expressions.

Handling Procedure Output

If your stored procedure returns OUT parameters, OIC captures them and makes them available for subsequent integration steps or for the REST response. For procedures that return REF CURSORs, OIC can map the result set to a repeating structure in the response payload.

Error Handling in the Integration

Wrap the database invoke in a Scope with a Fault Handler. If the procedure raises an exception, the fault handler catches it and can log the error, send a notification, or retry the operation. OIC also provides global error handling that sends email alerts when an integration instance fails, which is useful for production monitoring.

-- Design your procedures for integration-friendliness
CREATE OR REPLACE PROCEDURE sync_invoice(
    p_invoice_id   IN  NUMBER,
    p_amount       IN  NUMBER,
    p_vendor_name  IN  VARCHAR2,
    p_status       OUT VARCHAR2,
    p_error_msg    OUT VARCHAR2
) IS
BEGIN
    -- Process the invoice
    MERGE INTO local_invoices li USING DUAL
       ON (li.cloud_invoice_id = p_invoice_id)
    WHEN MATCHED THEN UPDATE SET amount = p_amount, vendor = p_vendor_name
    WHEN NOT MATCHED THEN INSERT VALUES (p_invoice_id, p_amount, p_vendor_name, SYSDATE);

    p_status := 'SUCCESS';
EXCEPTION
    WHEN OTHERS THEN
        p_status := 'ERROR';
        p_error_msg := SQLERRM;
END;

Best Practices

Design procedures with explicit status and error OUT parameters so OIC can branch on success or failure. Keep procedures focused on a single operation. Use autonomous transactions for logging so that error records persist even when the main transaction rolls back. Test procedures independently with SQL Developer before wiring them into OIC integrations.