Oracle database with embedded OPC UA
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.
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.
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!
Lead Software Engineer
Expertise and responsibility areas: OPC & OPC UA product development and project work
About Prosys OPC Ltd
Prosys OPC is a leading provider of professional OPC software and services with over 20 years of experience in the field. OPC and OPC UA (Unified Architecture) are communications standards used especially by industrial and high-tech companies.
Newest blog posts
The blog post describes step-by-step TalentLMS sign-up and course payment process
Updates to the blog post on importing Information Models from NodeSet files
Master's thesis evaluation of OPC UA protocol and information modeling for use in heavily regulated manufacturing fields