Oracle Database with Embedded OPC UA

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

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!

A generic vector graphic of an author avatar

Jukka Asikainen

Lead Software Engineer

Email: jukka.asikainen@prosysopc.com

Related Posts

i-GuSystem – From MTConnect to OPC UA: Future-Proof CNC Data Collection with Forge

i-GuSystem Ltd., a Finnish specialist in CNC program transfer and production data acquisition, adopted Prosys OPC UA Forge to extend its data integration from MTConnect into the OPC UA era, ensuring future-proof and scalable solutions. To meet the growing demand for OPC UA, i-GuSystem integrated Prosys OPC UA Forge as a future-proof layer. Forge enables direct OPC UA connectivity with output in XML, ensuring full compatibility with iguXMLsync and downstream systems like VisualFactory, MES, Azure cloud, and Power BI.
The first Forge deployment was completed in just two hours, delivering reliable results and proving the scalability of i-GuSystem’s solutions. By adopting Forge, i-GuSystem has seamlessly extended its CNC data expertise into the OPC UA era, future-proofing its architecture while continuing to provide rapid, hands-on results for its customers.

Read More »

Interested in this topic?

Get updated about new posts through our newsletter!