Ratings Tab fails to load data

RMA internal CDA instance at https://dc3-vl-cwms-rdr:8443/swt-data/ for office SWT fails to load ratings tab data.

CWMSVUE console output shows:

>  Aug 01, 2025 1:16:22 PM hec.cwmsVue.CwmsRatingPanelFX loadRatingCatalog
SEVERE: Error getting rating from database
hec.db.DbIoException: mil.army.usace.hec.cwms.http.client.CwmsHttpResponseException: Unknown error occurred for request: GET https://dc3-vl-cwms-rdr:8443/swt-data/ratings/metadata?page-size=1000&timezone=UTC&office=SWT
Error code: 500
{"message":"Failed to process request: org.jooq.exception.DataAccessException: SQL [declare \"b7\" boolean; \"b8\" boolean; \"b9\" boolean; \"b10\" boolean; begin \"b7\" := case ? when 0 then false when 1 then true end; \"b8\" := case ? when 0 then false when 1 then true end; \"b9\" := case ? when 0 then false when 1 then true end; \"b10\" := case ? when 0 then false when 1 then true end; ? := \"CWMS_20\".\"CWMS_RATING\".\"RETRIEVE_RATINGS_XML_DATA\" (\"P_EFFECTIVE_TW\" => ?, \"P_SPEC_ID_MASK\" => ?, \"P_START_DATE\" => cast(? as date), \"P_END_DATE\" => cast(? as date), \"P_TIME_ZONE\" => ?, \"P_RETRIEVE_TEMPLATES\" => \"b7\", \"P_RETRIEVE_SPECS\" => \"b8\", \"P_RETRIEVE_RATINGS\" => \"b9\", \"P_RECURSE\" => \"b10\", \"P_INCLUDE_POINTS\" => ?, \"P_OFFICE_ID_MASK\" => ?); end;]; ORA-20047: SESSION_OFFICE_ID_NOT_SET: Session office id is not set by the application\nORA-06512: at \"CWMS_20.CWMS_ERR\", line 80\nORA-06512: at \"CWMS_20.CWMS_UTIL\", line 542\nORA-01422: exact fetch returns more than requested number of rows\nORA-06512: at \"CWMS_20.CWMS_UTIL\", line 536\nORA-06512: at \"CWMS_20.CWMS_RATING\", line 1188\nORA-06512: at \"CWMS_20.CWMS_RATING\", line 2159\nORA-06512: at \"CWMS_20.CWMS_RATING\", line 2309\nORA-06512: at \"CWMS_20.CWMS_RATING\", line 2429\nORA-06512: at line 1\n","incidentIdentifier":"-131891602331431830","details":{}}
        at wcds.dbi.cwms.data.api.CwmsDaoImpl.wrapIOException(CwmsDaoImpl.java:77)
        at wcds.dbi.cwms.data.api.CwmsRatingCdaDao.retrieveFullRatingCatalog(CwmsRatingCdaDao.java:566)
        at hec.cwmsVue.CwmsRatingPanelFX.loadRatingCatalog(CwmsRatingPanelFX.java:141)
        at hec.cwmsVue.CwmsRatingPanelFX.eagerLoadData(CwmsRatingPanelFX.java:130)
        at hec.cwmsVue.CwmsLazyLoadedPanelFX.loadData(CwmsLazyLoadedPanelFX.java:58)
        at hec.cwmsVue.CwmsLazyLoadedPanelFX.loadData(CwmsLazyLoadedPanelFX.java:23)
        at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: mil.army.usace.hec.cwms.http.client.CwmsHttpResponseException: Unknown error occurred for request: GET https://dc3-vl-cwms-rdr:8443/swt-data/ratings/metadata?page-size=1000&timezone=UTC&office=SWT
Error code: 500
{"message":"Failed to process request: org.jooq.exception.DataAccessException: SQL [declare \"b7\" boolean; \"b8\" boolean; \"b9\" boolean; \"b10\" boolean; begin \"b7\" := case ? when 0 then false when 1 then true end; \"b8\" := case ? when 0 then false when 1 then true end; \"b9\" := case ? when 0 then false when 1 then true end; \"b10\" := case ? when 0 then false when 1 then true end; ? := \"CWMS_20\".\"CWMS_RATING\".\"RETRIEVE_RATINGS_XML_DATA\" (\"P_EFFECTIVE_TW\" => ?, \"P_SPEC_ID_MASK\" => ?, \"P_START_DATE\" => cast(? as date), \"P_END_DATE\" => cast(? as date), \"P_TIME_ZONE\" => ?, \"P_RETRIEVE_TEMPLATES\" => \"b7\", \"P_RETRIEVE_SPECS\" => \"b8\", \"P_RETRIEVE_RATINGS\" => \"b9\", \"P_RECURSE\" => \"b10\", \"P_INCLUDE_POINTS\" => ?, \"P_OFFICE_ID_MASK\" => ?); end;]; ORA-20047: SESSION_OFFICE_ID_NOT_SET: Session office id is not set by the application\nORA-06512: at \"CWMS_20.CWMS_ERR\", line 80\nORA-06512: at \"CWMS_20.CWMS_UTIL\", line 542\nORA-01422: exact fetch returns more than requested number of rows\nORA-06512: at \"CWMS_20.CWMS_UTIL\", line 536\nORA-06512: at \"CWMS_20.CWMS_RATING\", line 1188\nORA-06512: at \"CWMS_20.CWMS_RATING\", line 2159\nORA-06512: at \"CWMS_20.CWMS_RATING\", line 2309\nORA-06512: at \"CWMS_20.CWMS_RATING\", line 2429\nORA-06512: at line 1\n","incidentIdentifier":"-131891602331431830","details":{}}
        at mil.army.usace.hec.cwms.http.client.HttpRequestBuilderImpl$HttpRequestExecutorImpl.checkError(HttpRequestBuilderImpl.java:278)
        at mil.army.usace.hec.cwms.http.client.HttpRequestBuilderImpl$HttpRequestExecutorImpl.handleExecutionError(HttpRequestBuilderImpl.java:264)
        at mil.army.usace.hec.cwms.http.client.HttpRequestBuilderImpl$HttpRequestExecutorImpl.execute(HttpRequestBuilderImpl.java:247)
        at mil.army.usace.hec.cwms.data.api.client.controllers.RatingController.retrieveRatingMetadata(RatingController.java:96)
        at wcds.dbi.cwms.data.api.CwmsRatingCdaDao.retrieveFullRatingCatalog(CwmsRatingCdaDao.java:551)
        ... 8 more

CDA server log shows the following:

> 01-Aug-2025 20:16:23.128 SEVERE [https-jsse-nio-8443-exec-2] cwms.cda.api.rating.RatingMetadataController.getAll -131891602331431830: Failed to process request: org.jooq.exception.DataAccessException: SQL [declare "b7" boolean; "b8" boolean; "b9" boolean; "b10" boolean; begin "b7" := case ? when 0 then false when 1 then true end; "b8" := case ? when 0 then false when 1 then true end; "b9" := case ? when 0 then false when 1 then true end; "b10" := case ? when 0 then false when 1 then true end; ? := "CWMS_20"."CWMS_RATING"."RETRIEVE_RATINGS_XML_DATA" ("P_EFFECTIVE_TW" => ?, "P_SPEC_ID_MASK" => ?, "P_START_DATE" => cast(? as date), "P_END_DATE" => cast(? as date), "P_TIME_ZONE" => ?, "P_RETRIEVE_TEMPLATES" => "b7", "P_RETRIEVE_SPECS" => "b8", "P_RETRIEVE_RATINGS" => "b9", "P_RECURSE" => "b10", "P_INCLUDE_POINTS" => ?, "P_OFFICE_ID_MASK" => ?); end;]; ORA-20047: SESSION_OFFICE_ID_NOT_SET: Session office id is not set by the application
ORA-06512: at "CWMS_20.CWMS_ERR", line 80
ORA-06512: at "CWMS_20.CWMS_UTIL", line 542
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "CWMS_20.CWMS_UTIL", line 536
ORA-06512: at "CWMS_20.CWMS_RATING", line 1188
ORA-06512: at "CWMS_20.CWMS_RATING", line 2159
ORA-06512: at "CWMS_20.CWMS_RATING", line 2309
ORA-06512: at "CWMS_20.CWMS_RATING", line 2429
ORA-06512: at line 1

	java.util.concurrent.CompletionException: org.jooq.exception.DataAccessException: SQL [declare "b7" boolean; "b8" boolean; "b9" boolean; "b10" boolean; begin "b7" := case ? when 0 then false when 1 then true end; "b8" := case ? when 0 then false when 1 then true end; "b9" := case ? when 0 then false when 1 then true end; "b10" := case ? when 0 then false when 1 then true end; ? := "CWMS_20"."CWMS_RATING"."RETRIEVE_RATINGS_XML_DATA" ("P_EFFECTIVE_TW" => ?, "P_SPEC_ID_MASK" => ?, "P_START_DATE" => cast(? as date), "P_END_DATE" => cast(? as date), "P_TIME_ZONE" => ?, "P_RETRIEVE_TEMPLATES" => "b7", "P_RETRIEVE_SPECS" => "b8", "P_RETRIEVE_RATINGS" => "b9", "P_RECURSE" => "b10", "P_INCLUDE_POINTS" => ?, "P_OFFICE_ID_MASK" => ?); end;]; ORA-20047: SESSION_OFFICE_ID_NOT_SET: Session office id is not set by the application
ORA-06512: at "CWMS_20.CWMS_ERR", line 80
ORA-06512: at "CWMS_20.CWMS_UTIL", line 542
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "CWMS_20.CWMS_UTIL", line 536
ORA-06512: at "CWMS_20.CWMS_RATING", line 1188
ORA-06512: at "CWMS_20.CWMS_RATING", line 2159
ORA-06512: at "CWMS_20.CWMS_RATING", line 2309
ORA-06512: at "CWMS_20.CWMS_RATING", line 2429
ORA-06512: at line 1

		at java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:273)
		at java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:280)
		at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1606)
		at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
		at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
		at java.lang.Thread.run(Thread.java:750)
	Caused by: org.jooq.exception.DataAccessException: SQL [declare "b7" boolean; "b8" boolean; "b9" boolean; "b10" boolean; begin "b7" := case ? when 0 then false when 1 then true end; "b8" := case ? when 0 then false when 1 then true end; "b9" := case ? when 0 then false when 1 then true end; "b10" := case ? when 0 then false when 1 then true end; ? := "CWMS_20"."CWMS_RATING"."RETRIEVE_RATINGS_XML_DATA" ("P_EFFECTIVE_TW" => ?, "P_SPEC_ID_MASK" => ?, "P_START_DATE" => cast(? as date), "P_END_DATE" => cast(? as date), "P_TIME_ZONE" => ?, "P_RETRIEVE_TEMPLATES" => "b7", "P_RETRIEVE_SPECS" => "b8", "P_RETRIEVE_RATINGS" => "b9", "P_RECURSE" => "b10", "P_INCLUDE_POINTS" => ?, "P_OFFICE_ID_MASK" => ?); end;]; ORA-20047: SESSION_OFFICE_ID_NOT_SET: Session office id is not set by the application
ORA-06512: at "CWMS_20.CWMS_ERR", line 80
ORA-06512: at "CWMS_20.CWMS_UTIL", line 542
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "CWMS_20.CWMS_UTIL", line 536
ORA-06512: at "CWMS_20.CWMS_RATING", line 1188
ORA-06512: at "CWMS_20.CWMS_RATING", line 2159
ORA-06512: at "CWMS_20.CWMS_RATING", line 2309
ORA-06512: at "CWMS_20.CWMS_RATING", line 2429
ORA-06512: at line 1

		at org.jooq_3.18.7.ORACLE18C.debug(Unknown Source)
		at org.jooq_3.18.7.ORACLE18C.debug(Unknown Source)
		at org.jooq.impl.Tools.translate(Tools.java:3470)
		at org.jooq.impl.Tools.translate(Tools.java:3458)
		at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:802)
		at org.jooq.impl.AbstractRoutine.executeCallableStatement(AbstractRoutine.java:611)
		at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:463)
		at org.jooq.impl.Tools.attach(Tools.java:1610)
		at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:374)
		at usace.cwms.db.jooq.codegen.packages.CWMS_RATING_PACKAGE.call_RETRIEVE_RATINGS_XML_DATA(CWMS_RATING_PACKAGE.java:3502)
		at cwms.cda.data.dao.RatingMetadataDao.getRatingSet(RatingMetadataDao.java:267)
		at cwms.cda.data.dao.RatingMetadataDao.retrieveRatings(RatingMetadataDao.java:231)
		at cwms.cda.data.dao.RatingMetadataDao.lambda$null$3(RatingMetadataDao.java:196)
		at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1604)
		... 3 more
	Caused by: java.sql.SQLException: ORA-20047: SESSION_OFFICE_ID_NOT_SET: Session office id is not set by the application
ORA-06512: at "CWMS_20.CWMS_ERR", line 80
ORA-06512: at "CWMS_20.CWMS_UTIL", line 542
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "CWMS_20.CWMS_UTIL", line 536
ORA-06512: at "CWMS_20.CWMS_RATING", line 1188
ORA-06512: at "CWMS_20.CWMS_RATING", line 2159
ORA-06512: at "CWMS_20.CWMS_RATING", line 2309
ORA-06512: at "CWMS_20.CWMS_RATING", line 2429
ORA-06512: at line 1

		at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
		at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
		at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
		at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
		at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
		at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
		at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:265)
		at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:86)
		at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:965)
		at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
		at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
		at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1358)
		at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778)
		at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4251)
		at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081)
		at sun.reflect.GeneratedMethodAccessor51.invoke(Unknown Source)
		at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
		at java.lang.reflect.Method.invoke(Method.java:498)
		at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
		at com.sun.proxy.$Proxy14.execute(Unknown Source)
		at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219)
		at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4695)
		at org.jooq.impl.AbstractRoutine.execute0(AbstractRoutine.java:622)
		at org.jooq.impl.AbstractRoutine.executeCallableStatement(AbstractRoutine.java:582)
		... 11 more
	Caused by: Error : 20047, Position : 0, Sql = declare "b7" boolean; "b8" boolean; "b9" boolean; "b10" boolean; begin "b7" := case :1  when 0 then false when 1 then true end; "b8" := case :2  when 0 then false when 1 then true end; "b9" := case :3  when 0 then false when 1 then true end; "b10" := case :4  when 0 then false when 1 then true end; :5  := "CWMS_20"."CWMS_RATING"."RETRIEVE_RATINGS_XML_DATA" ("P_EFFECTIVE_TW" => :6 , "P_SPEC_ID_MASK" => :7 , "P_START_DATE" => cast(:8  as date), "P_END_DATE" => cast(:9  as date), "P_TIME_ZONE" => :10 , "P_RETRIEVE_TEMPLATES" => "b7", "P_RETRIEVE_SPECS" => "b8", "P_RETRIEVE_RATINGS" => "b9", "P_RECURSE" => "b10", "P_INCLUDE_POINTS" => :11 , "P_OFFICE_ID_MASK" => :12 ); end;, OriginalSql = declare "b7" boolean; "b8" boolean; "b9" boolean; "b10" boolean; begin "b7" := case ? when 0 then false when 1 then true end; "b8" := case ? when 0 then false when 1 then true end; "b9" := case ? when 0 then false when 1 then true end; "b10" := case ? when 0 then false when 1 then true end; ? := "CWMS_20"."CWMS_RATING"."RETRIEVE_RATINGS_XML_DATA" ("P_EFFECTIVE_TW" => ?, "P_SPEC_ID_MASK" => ?, "P_START_DATE" => cast(? as date), "P_END_DATE" => cast(? as date), "P_TIME_ZONE" => ?, "P_RETRIEVE_TEMPLATES" => "b7", "P_RETRIEVE_SPECS" => "b8", "P_RETRIEVE_RATINGS" => "b9", "P_RECURSE" => "b10", "P_INCLUDE_POINTS" => ?, "P_OFFICE_ID_MASK" => ?); end;, Error Msg = ORA-20047: SESSION_OFFICE_ID_NOT_SET: Session office id is not set by the application
ORA-06512: at "CWMS_20.CWMS_ERR", line 80
ORA-06512: at "CWMS_20.CWMS_UTIL", line 542
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "CWMS_20.CWMS_UTIL", line 536
ORA-06512: at "CWMS_20.CWMS_RATING", line 1188
ORA-06512: at "CWMS_20.CWMS_RATING", line 2159
ORA-06512: at "CWMS_20.CWMS_RATING", line 2309
ORA-06512: at "CWMS_20.CWMS_RATING", line 2429
ORA-06512: at line 1

Start with cwms.cda.api.rating.RatingMetadataController.getAll and cwms.cda.data.dao.RatingMetadataDao.java, especially the reported lines 196, 231, and 267, then trace the /ratings/metadata request into the database call. Reproduce the SWT request and determine why the session office ID and exact-fetch errors occur; done means the Ratings tab loads without the reported HTTP 500.