Oracle Database™

Query data from an Oracle Database™.

Specific Settings

To configure the SQL connector for querying an Oracle Server, provide the following details:

• Thick mode: When enabled, uses the Oracle Client Library to access the database. More details here.
  • Oracle Client Library: Path to the installed Oracle Client Library on the host machine.
• Host: Address of the Oracle server.
• Port: Server port (default is 1521).
• Connection timeout: Maximum time allowed to establish the connection.
• Database: Name of the target database.
• Username: Username for authentication.
• Password: Corresponding password.

Thin vs. Thick Mode

OIBus supports both thin and thick modes via the oracledb driver. Thin mode does not require the Oracle Instant Client, making it easier to set up (no native binaries, no LD_LIBRARY_PATH — just pure JavaScript). Refer to the compatibility matrix below to determine which mode is appropriate for your Oracle version.

Oracle Database Version Compatibility with OIBus

Oracle DB Version	Thin Mode Support	Thick Mode Support (with Instant Client)	Notes
19c or newer	✅ Supported	✅ Supported	Recommended version
12.2 (12c R2)	✅ Supported	✅ Supported	Minimum version for thin mode
12.1 (12c R1)	⚠️ Not officially supported	✅ Supported	Thin mode may work but is not guaranteed
11.2 (11g R2)	❌ Not supported	✅ Supported (with Instant Client 11.2+)	Minimum version for thick mode
10g or older	❌ Not supported	❌ Not supported	Not compatible with OIBus

Oracle Instant Client

Oracle Instant Client is only required for thick mode.

In thick mode, OIBus’s compatibility with Oracle Database versions depends on the version of the Oracle Instant Client in use.

According to Oracle’s client-server interoperability policy, Oracle Instant Client 18.x can connect to Oracle Database 11.2 and newer.

Minimum Oracle Instant Client Version

The minimum supported version for Oracle Instant Client (Basic or Basic Light) is 18.5.0.0.0.

You can download the Oracle Instant Client here.

Thus, when using Oracle Instant Client 18.5.0.0.0 with OIBus, compatibility is ensured for Oracle Database 11.2 and above.

Best Practice for Database Access

For security and data integrity, it is strongly recommended to use a read-only user when connecting to the database.

Throttling settings

For South connectors capable of historical data retrieval, you have the flexibility to request data in intervals. These intervals can vary in size, depending on factors such as the chosen scan mode or the presence of prolonged network failures.

To handle such scenarios, the throttling settings enable you to divide large intervals into smaller sub-intervals, each no longer than the specified Max read interval (in seconds). These sub-intervals are requested with a delay defined by the Read delay setting (in milliseconds).

In certain situations, adding an overlap to the query can be beneficial. You can achieve this by configuring the overlap field (in milliseconds): it will subtract this specified number of milliseconds from the @StartTime variable of the subsequent query.

Item settings

In the South connector, each item can be configured to be queried according to the chosen scan mode. Multiple queries to the same database can be configured within the same South connector. OIBus will execute the queries one after another, prepare the output file, and then send it to North connectors.

Query

The query field in the South connector accepts SQL syntax and can utilize several internal variables. These variables serve various purposes, including enhancing data stream resilience in the event of a connection failure and breaking down large intervals into smaller chunks, which helps reduce the load on the server and network. For more information, refer to the big queries section.

Query variables

In OIBus, you can utilize the following internal variables that will be interpreted by the system:

• @StartTime: Initially, the @StartTime variable is set to the date of the first execution of the query. When results are retrieved from the database, the @StartTime value is updated to the most recent timestamp among those results in the field used as a reference (refer to the datetime fields section).
• @EndTime: The @EndTime variable is set to either the current time (now()) or the end of the sub-interval if a query is split.

SQL query with @StartTime and @EndTime

SELECT data_name AS dataName, value, timestamp FROM table WHERE timestamp > @StartTime AND timestamp < @EndTime

Datetime fields

In the South connector, you can specify an array of fields that are of datetime type. Each row indicate the format in which OIBus will parse this field in order to convert it to an internal UTC date. Here are the details for configuring datetime fields:

• Field name: The field name in the SELECT section of the query.
• Reference field: Use this field as a reference for the next @StartTime value (refer to query variables).
• Type: The type of data in the result.
• Timezone (for string, Date, DateTime, DateTime2, SmallDateTime types): The timezone of the datetime stored in the database.
• Format (for string only): The string format of the datetime stored in the database.
• Locale (for string only): The locale to use when the format contains locale-specific strings (such as MMM format for months).

OIBus internally uses these dates in ISO UTC format, and the output datetime format can be set in the serialization section. All datetime fields are converted using the same serialization settings.

Conversion in SQL query

If the timestamp field is utilized as a reference of type string, formatted as yyyy-MM-dd HH:mm:ss, the @StartTime and @EndTime will be injected into the query as yyyy-MM-dd HH:mm:ss format strings.

In the subsequent query, the datetime field (retrieved from the database) is a DateTime object converted into a string (timestamp). OIBus will interpret the timestamp (string) field from the query as a reference field. However, injecting @StartTime and @EndTime as string variables in the same format may lead to unexpected behavior due to the inconsistency between the injected @StartTime and @EndTime variables (string type) and the datetime field (formatted as DateTime from the database).

Bad SQL query with @StartTime and @EndTime and convert

SELECT data_name AS dataName, value, convert(datetime, DATETIME) AS timestamp FROM table
WHERE datetime > @StartTime AND datetime <= @EndTime

In the following case, the uniformity in formats guarantees proper functionality.

Correct SQL query with @StartTime and @EndTime

SELECT data_name AS dataName, value, convert(datetime, DATETIME) AS timestamp FROM table
WHERE convert(datetime, DATETIME) > @StartTime AND convert(datetime, DATETIME) <= @EndTime

CSV Serialization

OIBus offers the option to serialize retrieved data into CSV files, and you can customize the serialization process with the following settings:

• Filename: The name of the file where the result will be stored. You can use several internal variables like @ConnectorName (the name of the connector) and @CurrentDate (the current date in yyyy_MM_dd_HH_mm_ss_SSS format - it is uncorrelated to the datetime format of the serialization, used for datetime fields).
• Delimiter: The delimiter to use in the CSV.
• Compression: Choose whether to compress the file with gzip or not.
• Output datetime format: Specify the format of the datetime in the CSV. OIBus will only convert the datetime fields specified. The @CurrentDate variable used in the output filename won't be affected.
• Output timezone: The timezone to use for storing the datetime.

Splitting large queries

In situations where a query may impose a significant load on the server, especially when a large time interval is requested and the @StartTime and @EndTime query variables are utilized, you can split the query into several sub-queries with smaller intervals. This can be achieved by configuring the Max read interval field in the throttling settings.