Ratings Tab fails to load data #1209
Description
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