What the DB Adapter does
The Oracle Database Adapter lets OIC interact directly with Oracle and non-Oracle databases — executing SQL queries, calling stored procedures and functions, and performing DML operations. It’s one of the most used adapters in enterprise OIC deployments because so much business logic lives in the database layer.
Connection setup
To connect, you need a JDBC connection string, credentials, and — for on-premise databases — the OIC Connectivity Agent installed on a server that can reach the database. Cloud databases (ATP, ADW) connect directly using wallet-based authentication.
For ATP:
- Download the wallet from OCI Console
- Upload to OIC as a credential under Settings → Certificates
- Create a DB Adapter connection using the ATP service name from the wallet
Adapter operation types
Run a SQL Query — executes a SELECT and returns results as XML. Best for simple lookups.
Invoke a Stored Procedure — calls a PL/SQL procedure or function. The adapter introspects the procedure signature and generates the request/response schemas automatically. This is the preferred pattern for complex data operations.
Perform an Operation — supports INSERT, UPDATE, DELETE via drag-and-drop field mapping. Use for straightforward DML.
Calling stored procedures — the recommended pattern
Rather than writing raw SQL in the adapter configuration, wrap your logic in a stored procedure and call it from OIC. Benefits:
- Business logic stays in the database, testable independently
- Procedure changes don’t require OIC integration changes
- Better error handling via OUT parameters
PROCEDURE get_order_status (
p_order_id IN NUMBER,
p_status OUT VARCHAR2,
p_error OUT VARCHAR2
) AS BEGIN
SELECT status INTO p_status FROM orders WHERE id = p_order_id;
p_error := NULL;
EXCEPTION
WHEN NO_DATA_FOUND THEN p_error := 'Order not found';
END;
Pagination for large result sets
Never execute an unbounded SELECT through OIC. Always pass ROWNUM or use OFFSET/FETCH:
SELECT * FROM transactions
WHERE processed_date > :lastRun
ORDER BY id
FETCH FIRST :batchSize ROWS ONLY
Loop in OIC using a While activity until the result set returns fewer rows than the batch size.
Security notes
Use a dedicated read-only DB account for SELECT-based integrations. Write operations should go through stored procedures that enforce business rules. Never store credentials in integration configurations — use OIC’s credential store.