Oracle Database Integration Guide

Connect MaestroHub pipelines to Oracle Database instances for enterprise-grade data ingestion and orchestration. This guide highlights Oracle specifics such as PL/SQL, service names, and authentication modes so you can configure pipelines with confidence.

Overview

The Oracle Database connector unlocks relational workloads with:

• Bidirectional data flows for reading and writing SQL and PL/SQL queries
• Stored procedure execution with parameter binding and output handling
• Secure connectivity with SSL/TLS encryption and granular connection tuning

Supported Versions

The connector is validated with Oracle Database 10g and later versions. Supported Oracle versions: 10g, 11g, 12c, 18c, 19c, 21c+

Connection Configuration

Creating an Oracle Database Connection

Open Connections → New Connection → Oracle and provide the following details:

Oracle Database Connection Creation Fields

1. Profile Information

Field	Default	Description
Profile Name	-	A descriptive name for this connection profile (required, max 100 characters)
Description	-	Optional description for this Oracle connection

2. Database Configuration

Field	Default	Description
Host	localhost	Server hostname or IP address (required)
Port	1521	Oracle listener port number (1-65535)
Service Name	-	Oracle service name (required, e.g., XEPDB1, ORCLPDB1, FREEPDB1). Modern Oracle databases use service names for connection
Connect Timeout (sec)	30	Connection timeout in seconds (0-600) - required

Notes

• Supported Oracle Versions: 10g, 11g, 12c, 18c, 19c, 21c+
• Service Name: Oracle databases use service names for connections. This is the modern approach replacing SID
• Pluggable Databases (PDB): For multitenant environments, specify the PDB service name (e.g., ORCLPDB1)
• Container Databases (CDB): To connect to CDB root, use the CDB service name

3. Basic Authentication

Field	Default	Description
Username	-	Oracle database username (required, e.g., SYSTEM, HR, APPUSER)
Password	-	Oracle user password (required)

Oracle User Privileges

Ensure the user has appropriate privileges:

• SELECT privilege for read operations
• INSERT, UPDATE, DELETE for write operations
• EXECUTE for stored procedures
• CREATE SESSION for establishing connections

4. SSL/TLS Settings

4a. SSL Configuration

Field	Default	Description
Enable SSL/TLS	false	Enable encrypted connections to Oracle server
Trust Server Certificate	true	Accept all server certificates (required when SSL is enabled - no wallet support)

SSL/TLS Configuration

When SSL/TLS is enabled:

• All server certificates are automatically trusted
• Oracle Wallet is not supported in the current version
• Suitable for development and testing environments
• For production, ensure your Oracle server has valid certificates

5. Connection Pool Settings

Field	Default	Description
Max Open Connections	100	Maximum number of simultaneous database connections (1-1000). Higher values increase concurrency but add DB load
Max Idle Connections	25	Idle connections kept ready for reuse to reduce latency (0-1000). Set 0 to close idle connections immediately
Connection Max Lifetime (sec)	900	Maximum age of a connection before it is recycled (0-86400 sec, 0 = keep indefinitely)
Connection Max Idle Time (sec)	300	How long an idle connection may remain unused before being closed (0-86400 sec, 0 = disable idle timeout)

Connection Pool Best Practices

• Max Open Connections: Balance between concurrency and database load. Default 100 is suitable for most applications
• Max Idle Connections: Keeping idle connections reduces connection establishment latency
• Max Lifetime: Prevents connection leaks and helps with database maintenance windows (default 900 sec = 15 minutes)
• Max Idle Time: Closes unused connections to free up database resources (default 300 sec = 5 minutes)

6. Retry Configuration

Field	Default	Description
Retries	3	Number of connection retry attempts (0-10). 0 means no retry
Retry Delay (ms)	200	Initial delay between retry attempts in milliseconds (0-3,600,000 ms = 1 hour max)
Retry Backoff Multiplier	2	Exponential factor for retry delay growth (1.0-10.0, e.g., 2 means delay doubles each retry)

Retry Behavior Example

• 1st retry: wait 200ms
• 2nd retry: wait 400ms (200 × 2)
• 3rd retry: wait 800ms (400 × 2)

7. Connection Labels

Field	Default	Description
Labels	-	Key-value pairs to categorize and organize this Oracle connection (max 10 labels)

Example Labels

• env: prod - Environment (production, staging, development)
• team: data - Responsible team
• region: us-east - Geographical region
• application: erp - Associated application

Notes

• Oracle Version Support: This connector supports Oracle Database 10g, 11g, 12c, 18c, 19c, 21c+
• Service Names: Oracle uses service names for modern connection management.
• SSL/TLS Recommendations: Development: Use SSL disabled or trust all certificates for convenience. Production: Use SSL enabled with valid server certificates
• Connection Pooling: The driver uses Go's database/sql connection pool, which efficiently manages connections
• Retry Strategy: Implements exponential backoff for connection failures to handle transient network issues
• Fields marked as required must be filled out before creating/saving the connection
• All timeout values are in seconds unless otherwise specified (Retry Delay is in milliseconds)

Function Builder

Creating Oracle Functions

After the connection is saved, create reusable Oracle SQL functions:

1. Navigate to Functions → New Function
2. Choose Query as the function type
3. Select the Oracle connection you configured
4. Compose your SQL or PL/SQL statement with parameter placeholders

Oracle query function editor with SQL/PL-SQL and parameter binding

Query Function

Purpose: Execute SQL statements (SELECT, INSERT, UPDATE, DELETE) or PL/SQL blocks on Oracle Database. Run general-purpose SQL to read or modify data in your Oracle database.

Configuration Fields

Field	Type	Required	Default	Description
SQL Query	String	Yes	-	SQL or PL/SQL statement to execute. Supports parameterized queries with bind variables.
Timeout (seconds)	Number	No	0	Per-execution timeout in seconds. Sets maximum time allowed for query execution.

Use Cases:

• SELECT machine KPIs (OEE, downtime) from telemetry tables
• INSERT production events (start/stop, shift logs, quality checks)
• UPDATE work orders, inventory counts, or maintenance schedules
• DELETE or archive old telemetry and audit logs
• EXECUTE PL/SQL procedures and functions

Using Parameters

MaestroHub detects the ((parameterName)) syntax and exposes each parameter for validation and runtime binding.

Configuration	Description	Example
Type	Enforce data types for incoming values	string, number, boolean, datetime, array
Required	Mark parameters as mandatory or optional	Required / Optional
Default Value	Fallback value applied when callers omit a parameter	SYSTIMESTAMP, 0, 'Active'
Description	Add guidance for downstream users	"UTC start time for the query window"

Parameter configuration with validation rules and helper text

Oracle Bind Variables

Oracle automatically uses bind variables for parameters, which:

• Improves query performance through execution plan reuse
• Prevents SQL injection attacks
• Reduces parsing overhead in the database

Pipeline Integration

Use the Oracle Database query functions you publish here as nodes inside the Pipeline Designer to keep plant-floor activity aligned with enterprise data. Drag the query node into your flow, bind parameters to upstream outputs or constants, and adjust error handling or retry behavior directly on the canvas.

When orchestrating larger workflows, visit the Connector Nodes page for guidance on where Oracle nodes fit within multi-system automation patterns.

Common Use Cases

Production Events Query

Scenario: Retrieve production events from MES tables for a specific time range and production line.

SELECT 
    event_id,
    line_id,
    event_type,
    quantity_produced,
    event_time,
    operator_name
FROM ProductionEvents
WHERE event_time >= TO_DATE('((startDate))', 'YYYY-MM-DD')
  AND event_time < TO_DATE('((endDate))', 'YYYY-MM-DD') + 1
  AND line_id = ((lineId))
ORDER BY event_time DESC;

Pipeline Integration: Feed real-time production data to dashboards, trigger alerts based on thresholds, or sync with ERP systems.

---

Writing Work Order Progress

Scenario: Update work order completion details from barcode scanners or PLC events.

UPDATE WorkOrders
SET
    completed_quantity = ((completedQuantity)),
    scrap_quantity = ((scrapQuantity)),
    status = ((newStatus)),
    last_updated_at = SYSTIMESTAMP,
    last_updated_by = ((userId))
WHERE work_order_id = ((workOrderId));

Pipeline Integration: Trigger on event streams to keep ERP and MES systems synchronized in real time.

---

Inventory Transaction Insert

Scenario: Record inventory movements from automation equipment into ERP tables.

INSERT INTO InventoryTransactions (
    item_number,
    location_code,
    quantity_change,
    transaction_type,
    transaction_time,
    operator_id
) VALUES (
    ((itemNumber)),
    ((locationCode)),
    ((quantityChange)),
    ((transactionType)),
    SYSTIMESTAMP,
    ((operatorId))
);

Pipeline Integration: Combine with Modbus, OPC UA, or REST nodes to maintain accurate inventory records.