ORA-00907 with filter in getfeature request - geoserver 2.14.0

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

ORA-00907 with filter in getfeature request - geoserver 2.14.0

Martin Zeller
Hi,

we use geoserver 2.14.0 with oracle and postgis. A postgis getfeature
request with filter works:
http://xxx:8080/geoserver/wfs?&REQUEST=getFeature&Typename=xxx:xxxxx_postgis&Version=1.0.0&service=WFS&PROPERTYNAME=name&filter=<ogc:Filter
xmlns:ogc="http://ogc.org"
xmlns:gml="http://www.opengis.net/gml"><And><ogc:PropertyIsLike wildCard='*'
singleChar='.'
escape='!'><ogc:PropertyName>name</ogc:PropertyName><ogc:Literal>**</ogc:Literal></ogc:PropertyIsLike></And></ogc:Filter>

Whereas a similar request to an oracle store doesn't work:
http://xxx:8080/geoserver_read/wfs?&REQUEST=getFeature&Typename=xxx:xxxx_oracle&Version=1.0.0&service=WFS&filter=<ogc:Filter
xmlns:ogc="http://ogc.org"
xmlns:gml="http://www.opengis.net/gml"><And><ogc:PropertyIsLike wildCard='*'
singleChar='.'
escape='!'><ogc:PropertyName>NAME</ogc:PropertyName><ogc:Literal>Wien*</ogc:Literal></ogc:PropertyIsLike></And></ogc:Filter>

This request results in an exception:
java.io.IOException: Error occured calculating bounds for xxxx_oracle
ORA-00907: missing right parenthesis

Excerpt from the log file:


       
       
Please note the problem in the resulting sql statement: ...FROM
gisdb.xxxx_oracleWHERE...

What's the problem?

Thanks
Martin





--
Sent from: http://osgeo-org.1560.x6.nabble.com/GeoServer-User-f3786390.html


_______________________________________________
Geoserver-users mailing list

Please make sure you read the following two resources before posting to this list:
- Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer


[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: ORA-00907 with filter in getfeature request - geoserver 2.14.0

Martin Zeller
(nabble stripped the excerpt from the logfile)

Caused by: java.io.IOException: Error occured calculating bounds for
xxxx_oracle
        at org.geotools.jdbc.JDBCDataStore.getBounds(JDBCDataStore.java:1360)
        at
org.geotools.jdbc.JDBCFeatureSource.getBoundsInternal(JDBCFeatureSource.java:514)
        ... 117 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00907: missing right
parenthesis

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
        at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
        at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59)
        at
oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:747)
        at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)
        at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)
        at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
        at
oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343)
        at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)
        at
oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
        at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.geotools.jdbc.JDBCDataStore.getBounds(JDBCDataStore.java:1329)
        ... 118 more
Caused by: Error : 907, Position : 102, Sql = SELECT
SDO_AGGR_MBR(SDOGEOMETRY)FROM (SELECT * FROM ( SELECT * FROM
gisdb.xxxx_oracleWHERE (NAME LIKE 'Wien%'  AND NAME IS NOT NULL )) WHERE
ROWNUM <= 50) GT2_BOUNDS_, OriginalSql = SELECT
SDO_AGGR_MBR(SDOGEOMETRY)FROM (SELECT * FROM ( SELECT * FROM
gisdb.xxxx_oracleWHERE (NAME LIKE 'Wien%'  AND NAME IS NOT NULL )) WHERE
ROWNUM <= 50) GT2_BOUNDS_, Error Msg = ORA-00907: missing right parenthesis
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)



--
Sent from: http://osgeo-org.1560.x6.nabble.com/GeoServer-User-f3786390.html


_______________________________________________
Geoserver-users mailing list

Please make sure you read the following two resources before posting to this list:
- Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer


[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: ORA-00907 with filter in getfeature request - geoserver 2.14.0

Martin Zeller
In reply to this post by Martin Zeller
On 25.10.2018 08:55, Martin Zeller wrote:


> we use geoserver 2.14.0 with oracle and postgis. A postgis getfeature
> request with filter works:
> http://xxx:8080/geoserver/wfs?&REQUEST=getFeature&Typename=xxx:xxxxx_postgis&Version=1.0.0&service=WFS&PROPERTYNAME=name&filter=<ogc:Filter
> xmlns:ogc="http://ogc.org"
> xmlns:gml="http://www.opengis.net/gml"><And><ogc:PropertyIsLike
> wildCard='*'
> singleChar='.'
> escape='!'><ogc:PropertyName>name</ogc:PropertyName><ogc:Literal>**</ogc:Literal></ogc:PropertyIsLike></And></ogc:Filter>
>
> Whereas a similar request to an oracle store doesn't work:
> http://xxx:8080/geoserver_read/wfs?&REQUEST=getFeature&Typename=xxx:xxxx_oracle&Version=1.0.0&service=WFS&filter=<ogc:Filter
> xmlns:ogc="http://ogc.org"
> xmlns:gml="http://www.opengis.net/gml"><And><ogc:PropertyIsLike
> wildCard='*'
> singleChar='.'
> escape='!'><ogc:PropertyName>NAME</ogc:PropertyName><ogc:Literal>Wien*</ogc:Literal></ogc:PropertyIsLike></And></ogc:Filter>
>
> This request results in an exception:
> java.io.IOException: Error occured calculating bounds for xxxx_oracle
> ORA-00907: missing right parenthesis
>
> Excerpt from the log file:
>
> Please note the problem in the resulting sql statement: ...FROM
> gisdb.xxxx_oracleWHERE...
>

Same problem with geoserver 2.13.3


_______________________________________________
Geoserver-users mailing list

Please make sure you read the following two resources before posting to this list:
- Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer


[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: ORA-00907 with filter in getfeature request - geoserver 2.14.0

geowolf
Unsure, the Oracle SQL encoder is the same as the PostGIS one, save the spatial part (they have the same superclass),
the "like" encoding is basically the same, so not sure how this is happening.
First, make sure that you are using the oracle plugin corresponding to the geoserver release (sometimes people use
an older plugin with a newer geoserver, that causes troubles). If the issue is still there, open a ticket on the issue tracker,
with a small example dataset and a request to make it happen.

Cheers
Andrea

On Thu, Oct 25, 2018 at 11:13 AM Martin Zeller <[hidden email]> wrote:
On 25.10.2018 08:55, Martin Zeller wrote:


> we use geoserver 2.14.0 with oracle and postgis. A postgis getfeature
> request with filter works:
> http://xxx:8080/geoserver/wfs?&REQUEST=getFeature&Typename=xxx:xxxxx_postgis&Version=1.0.0&service=WFS&PROPERTYNAME=name&filter=<ogc:Filter
> xmlns:ogc="http://ogc.org"
> xmlns:gml="http://www.opengis.net/gml"><And><ogc:PropertyIsLike
> wildCard='*'
> singleChar='.'
> escape='!'><ogc:PropertyName>name</ogc:PropertyName><ogc:Literal>**</ogc:Literal></ogc:PropertyIsLike></And></ogc:Filter>
>
> Whereas a similar request to an oracle store doesn't work:
> http://xxx:8080/geoserver_read/wfs?&REQUEST=getFeature&Typename=xxx:xxxx_oracle&Version=1.0.0&service=WFS&filter=<ogc:Filter
> xmlns:ogc="http://ogc.org"
> xmlns:gml="http://www.opengis.net/gml"><And><ogc:PropertyIsLike
> wildCard='*'
> singleChar='.'
> escape='!'><ogc:PropertyName>NAME</ogc:PropertyName><ogc:Literal>Wien*</ogc:Literal></ogc:PropertyIsLike></And></ogc:Filter>
>
> This request results in an exception:
> java.io.IOException: Error occured calculating bounds for xxxx_oracle
> ORA-00907: missing right parenthesis
>
> Excerpt from the log file:
>
> Please note the problem in the resulting sql statement: ...FROM
> gisdb.xxxx_oracleWHERE...
>

Same problem with geoserver 2.13.3


_______________________________________________
Geoserver-users mailing list

Please make sure you read the following two resources before posting to this list:
- Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer


[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users


--

Regards, Andrea Aime == GeoServer Professional Services from the experts! Visit http://goo.gl/it488V for more information. == Ing. Andrea Aime @geowolf Technical Lead GeoSolutions S.A.S. Via di Montramito 3/A 55054 Massarosa (LU) phone: +39 0584 962313 fax: +39 0584 1660272 mob: +39 339 8844549 http://www.geo-solutions.it http://twitter.com/geosolutions_it ------------------------------------------------------- Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia. This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail.



_______________________________________________
Geoserver-users mailing list

Please make sure you read the following two resources before posting to this list:
- Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer


[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: ORA-00907 with filter in getfeature request - geoserver 2.14.0

Olyster
In reply to this post by Martin Zeller
Hi,

if you copy the oracle query from the log and run it in your oracle client,
you should get the same error.

Try to make it work directly in Oracle first.



--
Sent from: http://osgeo-org.1560.x6.nabble.com/GeoServer-User-f3786390.html


_______________________________________________
Geoserver-users mailing list

Please make sure you read the following two resources before posting to this list:
- Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer


[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: ORA-00907 with filter in getfeature request - geoserver 2.14.0

johnnyotoole
Hi,

I came across this problem now too with GeoServer 2.14.0 against an Oracle
12.1.0.2 database with geoserver-2.14.0-oracle-plugin and ojdbc8.jar.

The problem seems to be that the generated SQL is missing a space between
the table name and the "WHERE". If I run the SQL as per the log file I get
the ORA-00907. Add a space and it works.

The same query works on a different environment with GeoServer 2.13.1, so
I'm going to first try upgrading the problem environment forwards to 2.14.1.
If that doesn't work I'll go backwards to 2.13.1.

John




--
Sent from: http://osgeo-org.1560.x6.nabble.com/GeoServer-User-f3786390.html


_______________________________________________
Geoserver-users mailing list

Please make sure you read the following two resources before posting to this list:
- Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer


[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: ORA-00907 with filter in getfeature request - geoserver 2.14.0

geowolf
Hi,
the issue has been fixed already, the fix seems to have been included in 2.14.1:

Cheers
Andrea

On Tue, Dec 18, 2018 at 3:26 PM johnnyotoole <[hidden email]> wrote:
Hi,

I came across this problem now too with GeoServer 2.14.0 against an Oracle
12.1.0.2 database with geoserver-2.14.0-oracle-plugin and ojdbc8.jar.

The problem seems to be that the generated SQL is missing a space between
the table name and the "WHERE". If I run the SQL as per the log file I get
the ORA-00907. Add a space and it works.

The same query works on a different environment with GeoServer 2.13.1, so
I'm going to first try upgrading the problem environment forwards to 2.14.1.
If that doesn't work I'll go backwards to 2.13.1.

John




--
Sent from: http://osgeo-org.1560.x6.nabble.com/GeoServer-User-f3786390.html


_______________________________________________
Geoserver-users mailing list

Please make sure you read the following two resources before posting to this list:
- Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer


[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users


--

Regards, Andrea Aime == GeoServer Professional Services from the experts! Visit http://goo.gl/it488V for more information. == Ing. Andrea Aime @geowolf Technical Lead GeoSolutions S.A.S. Via di Montramito 3/A 55054 Massarosa (LU) phone: +39 0584 962313 fax: +39 0584 1660272 mob: +39 339 8844549 http://www.geo-solutions.it http://twitter.com/geosolutions_it ------------------------------------------------------- Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia. This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail.



_______________________________________________
Geoserver-users mailing list

Please make sure you read the following two resources before posting to this list:
- Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer


[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users