Trying to create some locations fails with ORA-06502

[DanielTOsborne]

I'm creating locations from a CSV, and 200 of them worked fine, but half a dozen fail, all with the same error.

Here's the internal log from the error:

19-Dec-2024 17:22:07.854 INFO [https-openssl-nio-8243-exec-57] cwms.cda.ApiServlet.lambda$init$12 -5056569152225167330: Not Found.
        cwms.cda.api.errors.NotFoundException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                at cwms.cda.data.dao.JooqDao.buildNotFound(JooqDao.java:358)
                at cwms.cda.data.dao.JooqDao.wrapException(JooqDao.java:239)
                at cwms.cda.data.dao.JooqDao.connection(JooqDao.java:603)
                at cwms.cda.data.dao.LocationsDaoImpl.storeLocation(LocationsDaoImpl.java:221)
                at cwms.cda.api.LocationController.create(LocationController.java:312)
                at io.javalin.apibuilder.CrudFunction$3.invoke$lambda-0(CrudHandler.kt:32)
                at io.javalin.apibuilder.CrudFunctionHandler.handle(CrudHandler.kt)
                at cwms.cda.security.KeyAccessManager.manage(KeyAccessManager.java:30)
                at cwms.cda.security.MultipleAccessManager.manage(MultipleAccessManager.java:41)
                at io.javalin.http.JavalinServlet.addHandler$lambda-0(JavalinServlet.kt:96)
                at io.javalin.http.JavalinServlet$lifecycle$2$1$1.invoke(JavalinServlet.kt:43)
                at io.javalin.http.JavalinServlet$lifecycle$2$1$1.invoke(JavalinServlet.kt:43)
                at io.javalin.http.JavalinServletHandler.executeNextTask(JavalinServletHandler.kt:99)
                at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$lambda-1(JavalinServletHandler.kt:85)
                at java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:995)
                at java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2137)
                at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$javalin(JavalinServletHandler.kt:85)
                at io.javalin.http.JavalinServletHandler.executeNextTask$lambda-11$lambda-10(JavalinServletHandler.kt:119)
                at java.util.concurrent.CompletableFuture.uniApply(CompletableFuture.java:616)
                at java.util.concurrent.CompletableFuture.uniApplyStage(CompletableFuture.java:628)
                at java.util.concurrent.CompletableFuture.thenApply(CompletableFuture.java:1996)
                at io.javalin.http.JavalinServletHandler.executeNextTask(JavalinServletHandler.kt:119)
                at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$lambda-1(JavalinServletHandler.kt:85)
                at java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:995)
                at java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2137)
                at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$javalin(JavalinServletHandler.kt:85)
                at io.javalin.http.JavalinServlet.service(JavalinServlet.kt:89)
                at javax.servlet.http.HttpServlet.service(HttpServlet.java:623)
                at cwms.cda.ApiServlet.service(ApiServlet.java:949)
                at javax.servlet.http.HttpServlet.service(HttpServlet.java:623)
                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:199)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
                at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:168)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
                at org.apache.catalina.filters.HttpHeaderSecurityFilter.doFilter(HttpHeaderSecurityFilter.java:129)
                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:168)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
                at org.apache.catalina.filters.CorsFilter.handleNonCORS(CorsFilter.java:331)
                at org.apache.catalina.filters.CorsFilter.doFilter(CorsFilter.java:158)
                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:168)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
                at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:168)
                at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
                at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:597)
                at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130)
                at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:660)
                at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:660)
                at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
                at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
                at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:241)
                at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
                at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346)
                at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:388)
                at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
                at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:936)
                at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1791)
                at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
                at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190)
                at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
                at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
                at java.lang.Thread.run(Thread.java:750)
        Caused by: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "CWMS_20.CWMS_LOC", line 3700
ORA-06512: at "CWMS_20.CWMS_LOC", line 2046
ORA-06512: at "CWMS_20.CWMS_LOC", line 3658
ORA-20025: LOCATION_ID_NOT_FOUND: The Location: "NF Gunnison blw LerouxCr" does not exist.
ORA-06512: at "CWMS_20.CWMS_LOC", line 1453
ORA-06512: at "CWMS_20.CWMS_ERR", line 80
ORA-06512: at "CWMS_20.CWMS_LOC", line 245
ORA-01403: no data found
ORA-06512: at "CWMS_20.CWMS_LOC", line 213
ORA-06512: at "CWMS_20.CWMS_LOC", line 182
ORA-06512: at "CWMS_20.CWMS_LOC", line 274
ORA-06512: at "CWMS_20.CWMS_LOC", line 1054
ORA-06512: at "CWMS_20.CWMS_LOC", line 3628
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:553)
                at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
                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.GeneratedMethodAccessor101.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.$Proxy60.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)
                at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:405)
                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_LOC_PACKAGE.call_STORE_LOCATION2(CWMS_LOC_PACKAGE.java:5543)
                at usace.cwms.db.jooq.dao.CwmsDbLocJooq.store_aroundBody12(CwmsDbLocJooq.java:178)
                at usace.cwms.db.jooq.dao.CwmsDbLocJooq$AjcClosure13.run(CwmsDbLocJooq.java:1)
                at org.aspectj.runtime.reflect.JoinPointImpl.proceed(JoinPointImpl.java:183)
                at usace.cwms.db.aspect.CwmsDbTimerAspect.setupTimer(CwmsDbTimerAspect.java:65)
                at usace.cwms.db.aspect.CwmsDbTimerAspect.wrapCwmsDbJooqWithTimer(CwmsDbTimerAspect.java:25)
                at usace.cwms.db.jooq.dao.CwmsDbLocJooq.store_aroundBody14(CwmsDbLocJooq.java:168)
                at usace.cwms.db.jooq.dao.CwmsDbLocJooq$AjcClosure15.run(CwmsDbLocJooq.java:1)
                at org.aspectj.runtime.reflect.JoinPointImpl.proceed(JoinPointImpl.java:183)
                at usace.cwms.db.aspect.jooq.SQLExceptionWrapperAspect.translateToSQLException(SQLExceptionWrapperAspect.java:51)
                at usace.cwms.db.jooq.dao.CwmsDbLocJooq.store(CwmsDbLocJooq.java:168)
                at cwms.cda.data.dao.LocationsDaoImpl.lambda$storeLocation$1(LocationsDaoImpl.java:226)
                at org.jooq.impl.DefaultDSLContext.lambda$connection$12(DefaultDSLContext.java:697)
                at org.jooq.impl.DefaultDSLContext.connectionResult(DefaultDSLContext.java:658)
                at org.jooq.impl.DefaultDSLContext.connection(DefaultDSLContext.java:696)
                at cwms.cda.data.dao.JooqDao.connection(JooqDao.java:601)
                ... 59 more
        Caused by: Error : 6502, Position : 0, Sql = BEGIN "CWMS_20"."CWMS_LOC"."STORE_LOCATION2" (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 ) ; END;, OriginalSql = { call "CWMS_20"."CWMS_LOC"."STORE_LOCATION2" (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }, Error Msg = ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "CWMS_20.CWMS_LOC", line 3700
ORA-06512: at "CWMS_20.CWMS_LOC", line 2046
ORA-06512: at "CWMS_20.CWMS_LOC", line 3658
ORA-20025: LOCATION_ID_NOT_FOUND: The Location: "NF Gunnison blw LerouxCr" does not exist.
ORA-06512: at "CWMS_20.CWMS_LOC", line 1453
ORA-06512: at "CWMS_20.CWMS_ERR", line 80
ORA-06512: at "CWMS_20.CWMS_LOC", line 245
ORA-01403: no data found
ORA-06512: at "CWMS_20.CWMS_LOC", line 213
ORA-06512: at "CWMS_20.CWMS_LOC", line 182
ORA-06512: at "CWMS_20.CWMS_LOC", line 274
ORA-06512: at "CWMS_20.CWMS_LOC", line 1054
ORA-06512: at "CWMS_20.CWMS_LOC", line 3628
ORA-06512: at line 1

                at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
                ... 101 more

My test case uses cwms-python, but I have seen this error before without it.

#!python3
import sys
import cwms
import pandas as pd

API_KEY = '<api key>'
API_ROOT = "https://url.internal/spk-data/"
OFFICE_ID = 'SPK'

#this code changes the CDA instance that you are connected to 
cwms.api.init_session(api_root=API_ROOT, api_key=f"apikey {API_KEY}")

locations = cwms.get_locations(OFFICE_ID, units='EN')
locations = locations.df['name'].drop_duplicates().to_list()

print(f"Total of {len(locations)} locations currently.")

# Read list of new locations from CSV
df = pd.read_csv('locations_to_add.csv', skiprows=3, skip_blank_lines=True, keep_default_na=False)

# Remove rows marked as existing in CWMS
df = df[df['Available in CWMS DB'] != 'YES']

# Remove rows with no location set (blank/invalid row in CSV)
df = df[df['CWMS Location'] != '']

count = 0
# Now loop, creating locations
for index, row in df.iterrows():
	if row['CWMS Location'] in locations:
		continue

	data = {
		"office-id": OFFICE_ID,
		"name": row['CWMS Location'],
		"latitude": row['Decimal Lat'] if row['Decimal Lat'] else 0,
		"longitude": row['Decimal Long'] if row['Decimal Long'] else 0,
		"active": True,
		"location-kind": 'SITE',
		"public-name": row['Gage Loc/Name'],
		"timezone-name": ('US/Pacific' if row['State'] in ['CA', 'NV'] else 'US/Mountain'),
		"nation": "US",
		"elevation": row['Gage Elevation'] if row['Gage Elevation'] else 0,
		"state-initial": row['State'],
		"county-name": row['County'],
		"nearest-city": row['Nearest City'],
		"horizontal-datum": row['Gage Horizontal Datum'] if row['Gage Horizontal Datum'] else 'NAD83',
		"vertical-datum": row['Gage Vertical Datum'] if row['Gage Vertical Datum'] else 'NGVD29',
		"elevation-units": "ft"
	}
	
	print(f"Will store location: {row['CWMS Location']}")
	try:
		cwms.store_location(data=data)
		count += 1
	except Exception as e:
		print(f"Failed to store location: {row['CWMS Location']}", file=sys.stderr)
		print(f"{str(e)}: {repr(e)}", file=sys.stderr)

print(f"Added a total of {count} new locations.")

locations_to_add.csv

[MikeNeilson]

Base location is too big: "NF Gunnison blw LerouxCr"
(NOTE: that's an error that should come back to the user, still on the todo list.)

base: 24
sub location: 32
I believe the - that separates them does not count towards the total.

[DanielTOsborne]

Base location is too big: "NF Gunnison blw LerouxCr" (NOTE: that's an error that should come back to the user, still on the todo list.)

base: 24 sub location: 32 I believe the - that separates them does not count towards the total.

It's exactly 24 characters though. I went through my entire spreadsheet of 200+ locations, and highlighted every one that was > 24, so I can shorten them. There's also one that fails that's shorter.

I made them all base locations... because the location naming guidelines implies I should. So they're all 24 characters or less.

[MikeNeilson]

Yep, I should definitely not rely on just a visual for that count. Would then be one of the other columns. That SQL stack trace might let me find it, I'll check.

[MikeNeilson]

It's the public name.

CREATE TABLE AT_PHYSICAL_LOCATION
(
  LOCATION_CODE       NUMBER(14)                NOT NULL,
  BASE_LOCATION_CODE  NUMBER(14)                NOT NULL,
  SUB_LOCATION_ID     VARCHAR2(32),
  TIME_ZONE_CODE      NUMBER(14),
  COUNTY_CODE         NUMBER(14),
  LOCATION_TYPE       VARCHAR2(32),
  ELEVATION           NUMBER,
  VERTICAL_DATUM      VARCHAR2(16),
  LONGITUDE           NUMBER,
  LATITUDE            NUMBER,
  HORIZONTAL_DATUM    VARCHAR2(16),
  PUBLIC_NAME         VARCHAR2(57),
  LONG_NAME           VARCHAR2(80),
  DESCRIPTION         VARCHAR2(1024),
  ACTIVE_FLAG         VARCHAR2(1)               NOT NULL,
  LOCATION_KIND       NUMBER(14)                NOT NULL,
  MAP_LABEL           VARCHAR2(50),
  PUBLISHED_LATITUDE  NUMBER,
  PUBLISHED_LONGITUDE NUMBER,
  OFFICE_CODE         NUMBER(14),
  NATION_CODE         VARCHAR2(2),
  NEAREST_CITY        VARCHAR2(50),
  CONSTRAINT AT_PHYSICAL_LOCATION_PK  PRIMARY KEY (LOCATION_CODE) USING INDEX
)

Unfortunately there's just not a good way to get that from the error message itself, so far as I know, so we're just going to need some sort of overhaul in the CDA/Database interaction for verification.