Resolving Ignition Alarm Journal ORA-00920 Errors on Oracle

Where does the Alarm Journal query stop?

The request starts in the client when the Alarm Journal Table widget asks for events in its time window. The client calls the gateway. The gateway builds SQL for the alarm journal profile and sends it over JDBC to Oracle. The query stops at Oracle's SQL parser. The parser rejects the statement, and the error travels back up the same path. The client logs it and paints the table with the 301 "Comm Error" overlay.

The client Output Console shows the whole chain in one entry:

WARN  [AlarmJournalTable-AWT-EventQueue-2] Error fetching alarms.
java.util.concurrent.ExecutionException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator

Read it from the inside out. SQLSyntaxErrorException with ORA-00920 is a parse failure raised by the database. GatewayException is the gateway passing that failure to the client. Layer one and the network are fine: the query reached Oracle and Oracle answered. The overlay says "Comm Error", but the fault is the SQL dialect.

Why does Oracle reject the journal SQL?

The Oracle trace file captures the statement the gateway sent:

SELECT e."id", e."eventid", e."source", e."displaypath", e."priority", e."eventtime", e."eventtype", e."eventflags"
FROM "ALARM_EVENTS" e
WHERE e."eventtime" >= :1 AND e."eventtime" <= :2
  AND (e."eventflags" & :3 > 0 OR (e."eventflags" & :4 = 0))

The filter on eventflags uses a bitmask. The widget passes flag masks in bind variables :3 and :4, and the SQL uses & to AND them against the stored flags. That works in databases where & is a bitwise operator. Oracle SQL has no & operator. The parser reaches & where it expects a comparison, finds nothing it recognizes, and raises ORA-00920. Oracle does bitwise AND only through a function:

AND (BITAND(e."eventflags", :3) > 0 OR BITAND(e."eventflags", :4) = 0)

The journal writes can still succeed. Inserts do not use the bitmask filter, so the tables fill with events. Only the read path in the Alarm Journal Table breaks.

The trace also shows a second issue. Every identifier is double-quoted, including "ALARM_EVENTS". Oracle folds unquoted identifiers to upper case when it creates an object. A quoted identifier matches only its exact case. If the journal tables exist in upper case but the profile uses a lower-case name, the quoted reference does not match. You then get a table-not-found error instead of ORA-00920. Fix both issues, or the table stays blank after the upgrade.

How do you confirm which fault you have?

1. Read the gateway version on the gateway status page. The & query shows up on 7.6 builds below 7.6.7. It was reported on 7.6.3 running on Linux.
2. Open the client Output Console and find the Error fetching alarms entry. ORA-00920 means the bitmask syntax. A table-not-found ORA error means the identifier case does not match.
3. Enable an Oracle SQL trace for the gateway session and reproduce the error by opening the table. Search the trace for eventflags" &. If you find it, the gateway is using the non-Oracle syntax.
4. Check the physical table name in a SQL tool. Run SELECT table_name FROM user_tables WHERE UPPER(table_name) LIKE 'ALARM%'; as the datasource user. The case returned is the case the profile must use.
5. Confirm the data exists. Run SELECT COUNT(*) FROM ALARM_EVENTS;. A non-zero count shows the write path works and the problem is read-only.

Which fix path fits the installation?

Two things are broken: the operator syntax and the identifier case. They have different remedies. The operator syntax is built into the gateway's query builder. No profile setting changes it.

Recommendation: On a 7.6 system, upgrade to 7.6.7 and uppercase the table names in the profile. This stays on the same branch, so project and module compatibility do not change. Take the 7.7 or 7.8 route only if a major upgrade is already planned. After that upgrade, run the same trace check, because the fix depends on the build and not only on the branch.

What is the procedure for 7.6.7 plus the case fix?

1. Take a gateway backup (.gwbk) from the gateway configuration page. Then export the alarm journal tables or snapshot the schema.
2. Install the 7.6.7 gateway over the existing 7.6 installation, using the platform installer for your OS. Restart the gateway and confirm the version on the status page.
3. Launch the clients again so they pick up the new client libraries. A client still running against the old build gives misleading results.
4. In the gateway configuration, open the alarm journal profile. Expand the Advanced section and set the table name fields to the exact case in user_tables. For the tables in this case, that is ALARM_EVENTS and its companion table, in upper case.
5. Save the profile. Confirm the datasource status still shows valid. A wrong schema user produces the same table-not-found symptom as a case mismatch.

Pitfalls that recur:

• If you change the profile table names to a case that does not exist, the gateway may create a new, empty quoted table. Journal writes then split between two tables. Check user_tables for duplicates after saving.
• Oracle trace files record the SQL text as parsed. If the trace still shows & after the upgrade, the gateway did not take the new build. Check for a stale service, or a second gateway pointed at the same datasource.
• Other alarm journal consumers use the same filter path, including scripted journal queries. Test those as well as the widget.

How do you verify the journal read path end to end?

1. Open a client, then open the Alarm Journal Table with a time range that covers known events. The 301 overlay should not appear.
2. Clear the Output Console, then refresh the table. Confirm that no Error fetching alarms entry appears.
3. Toggle the table's event filters (for example, active versus cleared) so that different bitmask values bind to :3 and :4. Confirm the rows change and no error is raised.
4. Re-run the Oracle session trace and find the journal SELECT. The eventflags predicate must read BITAND(e."eventflags", :3) > 0, with no & in the statement. The FROM clause must name the upper-case table that holds the data.

FAQ