Oracle has recently endorsed OPC UA in its reference IoT (Internet of Things) architecture (link). 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 format of

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 like this

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 huge 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, there was no need to store the data in the higher layers after its processing. This in turn reduced excessive duplication and removed unnecessary overhead from the plant management layers.

The end result enabled easy use of automation data in calculating important diagnostic and quality values. With the help of the OPC UA Java SDK, the creation of the OPC UA Java client was easy and fast. The requirements for secure, fast and expandable communications were implemented with ease. We’re happy to help if you want to embrace direct OPC UA capabilities in your Oracle database solution!