ORA-01882: timezone region not found lucee CFML coldfusion

dev support
1

Lucee 5.4.4.38
oracle driver: 21.8.0.0-ojdbc11
ubuntu 22.04
tomcat: 9.0.58-1ubuntu0.1
openjdk-11-jre-headless:amd64 11.0.21+9-0ubuntu1~22.04

The web admin interface for declaring datasources offers the option to specify the database server timezone so dates in string format without tz can get it set correctly and that the lucee server timezone is the default.

The code below triggers: ‘ORA-01882: timezone region not found’


<cfset this.datasources = {
    "perms" = {
        "class" = "oracle.jdbc.OracleDriver",
        "bundleName" = "org.lucee.oracle",
        "bundleVersion" = "21.8.0.0-ojdbc11",
        "connectionString" = "jdbc:oracle:thin:@oracle.my.edu:1521:UHPROD",
        "username" = "PERMISSIONS",
        "password" = "encrypted:a15e666ac2ad10645bdf8b206edc9d0b9bd82c56d828f23a3b0d9b57bc8557ff"
// none of these make the error go away:
        // "timezone" = "UTC"
        // "timezone" = "America/Denver"
        // "timezone" = "MST"
    }
}>

<cfquery name="userid_qry" datasource="perms" result="local.qry_result">
        select userid from users where userid = 'not found exmaple'
</cfquery>

<cfoutput query="userid_qry">
        #userid#
</cfoutput>

Any advice ?

2

The solution was to add:

"Environment="CATALINA_OPTS=-Doracle.jdbc.timezoneAsRegion=false"

to the `tomcat9.service file via:

sudo systemctl edt --full tomcat9.service.

Later I’ll create a override unit file so my changes aren’t clobbered with a ubuntu tomcat update.

In other configurations $CATALINA_HOME/bin/setenv.sh is probably the right spot.

FWIW, chatGPT did a better job of summarizing search results on ORA-01882 than I was. Given that info I was able (1) translate the eclipse/java settings to tomcat/CATALINA_OPTS and try the various possibilities until I found one that worked.

2 Likes