Oracle Database™

Connect to and query Oracle Database™ using OIBus with support for both thin and thick connection modes.

Version Compatibility

Oracle DB Version	Thin Mode	Thick Mode	Notes
19c+	✅	✅	Recommended
12.2 (12c R2)	✅	✅	Minimum for thin mode
12.1 (12c R1)	⚠️	✅	Thin mode not officially supported
11.2 (11g R2)	❌	✅	Minimum for thick mode
10g or older	❌	❌	Not compatible

Specific Settings

Setting	Description	Example Value
Thick mode	Use Oracle Client Library for database access. Requires Instant Client installation.	Enabled/Disabled
Oracle Client Library	Path to Oracle Instant Client. Required when thick mode is enabled.	C:\oracle\instantclient_23_5
Host	IP address or hostname of the Oracle server.	192.168.1.10
Port	Server port. Default: 1521.	1521
Connection timeout	Maximum time in milliseconds to establish connection. Default: 15000.	15000
Database	Name or SID of the target database.	ORCL
Username	Authentication username.	db_user
Password	Authentication password.	••••••••

Connection Modes

Mode	Description	Requirements	Benefits
Thin	Pure JavaScript implementation	None	Easy setup, no native binaries
Thick	Uses Oracle Client Library	Instant Client 18.5+	Full feature support

Oracle Instant Client

• Required only for thick mode
• Minimum version: 18.5.0.0.0
• Supports Oracle Database 11.2+
• Download: Oracle Instant Client

Security Best Practices

• Use a read-only database user
• Prefer thin mode when possible for simpler deployment

Group Settings

Items can be organised into groups. Each group defines a shared collection schedule and default throttling settings. Items in the same group are still fetched one at a time in sequence — the group simply provides common defaults that individual items can override.

Setting	Description	Example Value
Name	Unique label for the group within this connector.	Group A
Scan mode	Schedule used to collect all items in the group.	Every 1 min
Throttling	Default throttling values (Max read interval, Read delay, Overlap) inherited by items in the group.	3600, 200, 0

Item Settings

Each item can be individually configured with its own query parameters and datetime handling. Items inherit their scan mode and throttling defaults from their group, but each setting can be overridden per item by disabling Sync with group.

Throttling Settings

Throttling controls how OIBus paces historical data requests. These settings appear on each group (for connectors that support groups) or on each item (for single-item connectors). Items in a group can override the group defaults by disabling the Sync with group toggle.

Setting	Description	Example Value
Max read interval	Maximum duration of each sub-query in seconds. Larger time ranges are automatically split into chunks not exceeding this value.	3600
Read delay	Pause in milliseconds between consecutive sub-queries. Helps prevent server overload and manages rate limits.	1000
Overlap	Time in milliseconds subtracted from @StartTime of each query to capture late-arriving data from the previous interval.	60000

How Throttling Works

1. Interval splitting — A 24-hour range with Max read interval = 3600 (1 hour) is split into 24 separate 1-hour sub-queries.
2. Read delay — A pause is inserted between sub-queries to manage server load.
3. Overlap — With Overlap = 60000 (1 minute), a query for [10:00–11:00] actually requests [9:59–11:00], ensuring no late-arriving data is missed.

Overlap applies to the full query range

The overlap value is subtracted from the start of the overall query, not from the start of each individual sub-interval when a large range is split into chunks.

Recommended Configurations

Scenario	Max read interval	Read delay	Overlap
Stable network, small datasets	3600 (1 hour)	500	0 (no overlap)
Unstable network	1800 (30 min)	2000	0 (no overlap)
Large historical retrievals	7200 (2 hours)	1000	0 (no overlap)
Real-time with occasional gaps	900 (15 min)	200	15000 (15 sec)

Query Configuration

The query field accepts standard SQL syntax with support for internal variables that enhance data retrieval resilience and performance optimization.

Query Variables

Setting	Description	Example Value
@StartTime	Initialized to first execution time, then updated to the most recent timestamp from reference field	2024-01-15T10:00:00.000Z
@EndTime	Set to current time (now()) or sub-interval end when queries are split	2024-01-15T11:00:00.000Z

Example Query:

SELECT device_id, value, reading_time
FROM sensor_data
WHERE reading_time > @StartTime
AND reading_time < @EndTime
ORDER BY reading_time

Query Splitting

For handling large datasets, queries can be automatically divided into smaller time-based chunks based on the Max read interval throttling setting.

Datetime Field Configuration

Setting	Description	Example Value
Field name	Name of the datetime field in your SELECT statement	timestamp, reading_time
Reference field	Designates which field determines the @StartTime for subsequent queries	reading_time
Type	Data type of the datetime field. Available values differ by connector — see the connector-specific note below.	iso-string, unix-epoch
Timezone	Timezone of the stored datetime (for string/date types)	UTC, Europe/Paris
Format	Format pattern for string-based datetimes	yyyy-MM-dd HH:mm:ss
Locale	Locale for format elements (e.g., month names)	en_US, fr_FR

Type Consistency Requirement

When using datetime conversions in queries, keep the type consistent in both the SELECT list and the WHERE clause:

Problematic approach (may cause unexpected behavior):

SELECT value, CONVERT(datetime, string_field) AS timestamp
FROM table
WHERE string_field > @StartTime

Recommended approach:

SELECT value, CONVERT(datetime, string_field) AS timestamp
FROM table
WHERE CONVERT(datetime, string_field) > @StartTime

Data Flow Process

1. OIBus executes each configured query sequentially.
2. Results are consolidated into structured output.
3. The max instant is extracted from the reference datetime field, converted to UTC, and stored as the next @StartTime.
4. Final output is formatted according to serialization settings.
5. Processed data is sent to configured North connectors.

Best Practices

1. Choose reference fields with consistent, increasing values.
2. For string datetimes, ensure the format matches between database storage and query variables.
3. Add indexes on datetime fields for better query performance.
4. Use the Max read interval to split large time ranges into manageable chunks.

CSV Serialization

OIBus provides flexible options for serializing retrieved data into CSV format with customizable output settings.

File Configuration

Setting	Description	Example Value
Filename	Name pattern for output files.	data_@ConnectorName_@CurrentDate.csv
Delimiter	Character used to separate values in the CSV file	COMMA (,), SEMI_COLON (;), DOT (.), COLON (:), PIPE (|), SLASH (/), TAB (\t), NON_BREAKING_SPACE
Compression	Enable gzip compression for output files	Enabled/Disabled

Filename Variables

The following variables can be used in filename patterns:

• @ConnectorName: Automatically replaced with the connector's name
• @CurrentDate: Inserts current timestamp in fixed yyyy_MM_dd_HH_mm_ss_SSS format

Temporal Data Handling

Setting	Description	Example Value
Output datetime format	Format pattern for datetime fields in the CSV (does not affect @CurrentDate in filenames)	yyyy-MM-dd HH:mm:ss
Output timezone	Timezone used for datetime values in the CSV	UTC or Europe/Paris

Important Notes

1. The @CurrentDate variable in filenames uses a fixed format (yyyy_MM_dd_HH_mm_ss_SSS) regardless of the datetime format setting
2. Only datetime fields specified in your configuration will be formatted according to these settings
3. Timezone conversion only applies to datetime values in the CSV content, not to the filename timestamp

Oracle datetime field types

The following Type values are available for datetime field configuration:

Type	Description
String	String representation parsed with a custom format
DateTime	Oracle DATE or TIMESTAMP column
ISO String	ISO 8601 string (e.g. 2024-01-15T10:30:00.000Z)
UNIX epoch (s)	Unix timestamp in seconds
UNIX epoch (ms)	Unix timestamp in milliseconds

Oracle item request timeout

Each Oracle item has an additional Request timeout field (milliseconds) that sets the maximum execution time for that specific query.