Resolve exception thrown in Spectrum Management Console (when trying to create a Named Connection): "Reason: jave.sql.SQLException:ORA-00604: error occurred at recursive SQL level 1 ORA-01882: timezone region not found"

Products affected: Spectrum™

Issue

When testing a Named Connection to an Oracle 11g database in the Spectrum 9 Management Console, the following exception is thrown: "Reason: jave.sql.SQLException:ORA-00604: error occurred at recursive SQL level 1 ORA-01882: timezone region not found"

Cause

This issue is related to a JAVA configuration problem.

Resolution

UPDATED: September 19, 2017
This issue is resolved by the Java option: -Doracle.jdbc.timezoneAsRegion=false this can be applied in one of two places, as detailed below.


A) Adding the Java option to the java.vmargs file:

1.     Edit the C:\Program Files\Pitney Bowes\Spectrum\server\modules\spatial\java.vmargs file in a text editor
2.     Add the -Doracle.jdbc.timezoneAsRegion=false option, IE:
 
-Xmx1024m -Djava.io.tmpdir=../app/tmp -Dcom.mapinfo.midev.dp.fdo.native.dir=../modules/spatial/lib -Dcom.mapinfo.raster.mrsid.native.dir=../modules/spatial/lib -Dcom.mapinfo.raster.ecw.native.dir=../modules/spatial/lib -Doracle.jdbc.timezoneAsRegion=false

3.     Save the java.vmargs file
4.     Restart the server



B) Adding the Java option to the Named Connection:


1.     Open the Management Console
2.     Navigate to Modules > Location Intelligence > Tools > Named Connections
3.     Highlight the Oracle connection and select Modify
4.     Under Key paste: oracle.jdbc.timezoneAsRegion
5.     Under value paste: false
6.     Click Test to test the connection