We use Geoserver and Oracle 220.127.116.11 64 bit, with spatial extension. We have a view on a table on another schema (access with a synonymous).
The layer associated to the view works, but the layer preview catchs an error when the user clicks on a feature. The error is ORA-13226:"interface not supported without a spatial index", but this error occurs only when the user clicks on a feature in the layer preview.
We have debug the oracle calls and we notice that the query that generates the error is
SELECT COUNT ( * ) AS gt_result_
FROM (SELECT *
FROM (SELECT /*+ ALL_ROWS OPTIMIZER_FEATURES_ENABLE('8.1.7') */
WHERE SDO_RELATE (GEOM,
'mask=anyinteract querytype=WINDOW') =
WHERE ROWNUM <= 50) gt_limited_;
Note that the same query generates the same error if it's made into sqlplus.
The problem is that in this version (geoserver 2.1.0 with gt-jdbc.jar, ver. 2.7.1) the query has done with the optimizer hint /*+ ALL_ROWS OPTIMIZER_FEATURES_ENABLE('8.1.7') */ and it seems not working on 11g version. We have found this statement into org.geotools.jdbc.JDBCDataStore.java ver. 2.7.1. The previos version doesn't contain this hint (and works).