Oracle Database with Embedded OPC UA
- August 2, 2016
Introduction
Oracle has recently endorsed OPC UA in its reference to IoT (Internet of Things) architecture. In this solution, OPC UA acts as a bridge between plant devices and plant management layers. In our recent customer case, we integrated our Java OPC UA client directly into the Oracle database, handling the plant data. This solution enabled the MES (Manufacturing Execution System) in question to access the plant device floor directly and securely from its database. The solution helped to reduce the amount of intermediate layers storing the device floor data. The solution also enhanced security as all the traffic could go through secure OPC UA connections between the different networks.
Example OPC UA architecture
The example architecture consists of an OPC UA server in the automation end, which provides all the automation data from one endpoint. The OPC UA server may use various strategies accessing the automation data depending on the used automation provider. The OPC UA server may also be an aggregating server, which combines data from multiple OPC UA servers into a single address space.
Using Java in Oracle database
Running Java inside the Oracle database needed some wrapper PL/SQL functions, which pointed to the Java program. These were not hard to construct, although finding all the necessary Java options in the Oracle database was not a trivial task. The necessary Oracle Java permissions were, among others, file system, socket, security provider, runtime and logging permissions. All of the above were needed in a sophisticated and large OPC UA Java client program.
An equally important part was using a correct JDBC database connection from the Java program. In the server side it was simply the default connection object from the driver manager class. Connections from the Java side were needed when passing Oracle typed objects back into the PL/SQL object scope.
Next we’ll be looking at actual code examples showing SQL queries of OPC UA data.
Code examples
The Oracle PL/SQL wrapper functions for Java programs were in the following format:
FUNCTION read_history ( databaseId_in IN VARCHAR2,
startDate_in IN TIMESTAMP,
endDate_in IN TIMESTAMP,
period_in IN VARCHAR2 )
RETURN T_UADATAVALUE_ARRAY
AS LANGUAGE JAVA NAME
'com.prosysopc.ua.oracle.OracleClient.readHistory(
java.lang.String,
java.sql.Date,
java.sql.Date,
java.lang.String)
return java.sql.Array';
This assumed that Java program returned objects constructed using an Oracle database connection. That way the Oracle database had typed access to a measurement, which consisted of ID, datetime and a value.
In the end, database users could access the data though OPC UA using SQL syntax below:
SELECT positionId, datetime, value
FROM table (p_opcua.read_history(
databaseId_in => 'PositionID',
startDate_in => SYSDATE - INTERVAL '8' HOUR,
endDate_in => SYSDATE,
period_in => '10 s'
));
The query returns all the values with their corresponding timestamps for hypothetical ‘PositionID’ from the last 8 hours.
Conclusion
The embedded OPC UA client enabled on-demand data querying within the database. Thanks to this, no data was needlessly stored in-between layers. With vast amounts of sensor data needed for the calculations, this procedure simplified the handling of the values. As the data itself was stored and backed up in the lower layers, it was unnecessary to store the data in the higher layers after processing. This, in turn, reduced excessive duplication and removed unnecessary overhead from the plant management layers.
The result enabled easy use of automation data in calculating important diagnostic and quality values. With the help of the Prosys OPC UA SDK for Java, creating the OPC UA Java client was easy and fast. The requirements for secure, fast, and expandable communications were easily implemented. We’re happy to help if you want to embrace direct OPC UA capabilities in your Oracle database solution!
Author Info
Jukka Asikainen
Lead Software Engineer