Geoserver WMS gets oracle error when fetching with BBOX coordinates

GeoServer GeoServer User
,
1

Hi Team,
In our GEOSERVER instance, when we make a WMS call to fetch data from a oracle table with spatial data, we get below error response.
Geoserver Layer Preview option works without any issues but only when we make call with particular bbox coordinates we get below error.

Caused by: java.sql.SQLException: java.sql.SQLException: ORA-29903: Fout bij uitvoering van ODCIIndexFetch()-routine.
ORA-13236: Interne fout bij verwerken R-structuur: [].
ORA-13236: Interne fout bij verwerken R-structuur: [Snapshot too old or Recursive fetch error].
ORA-13236: Interne fout bij verwerken R-structuur: [Snapshot too old or Recursive fetch error].
ORA-13234: Benaderen van indextabel voor R-structuur [MDRT table] is mislukt.
ORA-29400: Fout van gegevenscartridge.
ORA-00942: Tabel of view bestaat niet.
.

	at org.geotools.jdbc.JDBCFeatureReader.runQuery(JDBCFeatureReader.java:282)
	at org.geotools.jdbc.JDBCFeatureReader.<init>(JDBCFeatureReader.java:172)
	at org.geotools.jdbc.JDBCFeatureSource.getReaderInternal(JDBCFeatureSource.java:614)
	... 138 more
Caused by: java.sql.SQLException: ORA-29903: Fout bij uitvoering van ODCIIndexFetch()-routine.
ORA-13236: Interne fout bij verwerken R-structuur: [].
ORA-13236: Interne fout bij verwerken R-structuur: [Snapshot too old or Recursive fetch error].
ORA-13236: Interne fout bij verwerken R-structuur: [Snapshot too old or Recursive fetch error].
ORA-13234: Benaderen van indextabel voor R-structuur [MDRT table] is mislukt.
ORA-29400: Fout van gegevenscartridge.
ORA-00942: Tabel of view bestaat niet.

Any advise on this?
Please help to resolve.

With best regards,
karthik

2

It seems that Geoserver wants to read the spatial index tables from Oracle, but either they do not exist, or the Oracle user that Geoserver is using has not been granted rights to see them.

For the first issue, create a spatial index, for the second one, this Spatial Operators maybe help with giving the proper rights.

-Jukka Rahkonen-

3

Thanks Jukka for your suggestion.

We do have the tables with all the grants on the username.
Geoserver layer preview option is able to fetch the entire tabel data and display.
we have tried rebuilding the spatial index but still when we make a WMS call with BBOX coordinates, we get this error.

https://ourgeoserver.com/geoserver/kpn/wms?SERVICE=WMS&VERSION=1.3.0&REQUEST=GetMap&FORMAT=image%2Fpng&TRANSPARENT=TRUE&VIEWPARAMS=DETAILRIGHTS%3A2ab5b6f1&LAYERS=workspacename%3Alayername&CRS=EPSG%3A3857&STYLES&FORMAT_OPTIONS=dpi%3A84&WIDTH=1879&HEIGHT=386&BBOX=529173.546818275%2C6860003.532138249%2C567468.4979891486%2C6867857.436794551

-Karthik

4

Hi,

The errors come directly from Oracle. Ask your db-admin to log the incoming SQL statements and analyze and correct the issues in the database. Oracle says with ORA-00942 in clear Dutch that for the user who made the query, some table or view does not exist. It seems that Oracle does not give any help for free Spatial query Show Errors: ORA-29903 ORA-13236 ORA-13236.

-Jukka Rahkonen-