[Geoserver-users] Oracle exception with Geoserver1.6.5

GeoServer GeoServer User
1

The Oracle exception - ORA-00942: table or view does not exist - is thrown when geoserver issues the SQL query to an Oracle datastore when a prepended schema name is expected or required. This error was reproduced by pasting the geoserver generated SQL into SQL Developer to issue the query, and worked only when the schema name was prepended to the table name and double quotes removed.

I specificed a schema in the data store definition, but this seems only to be used to filter the tables to those within that schema when defining a new feature type(expected behaviour), NOT when requesting requesting features from the data source.

Attached is the geoserver log file and some screen shots illustrating the problem. Note also that use of the double quotes enclosing the table name with the prepended schema name causes an ‘ORA-00972 identifier too long’ exception to the thrown. Query works correctly when double quotes are removed in SQL Developer.

Please confirm if this is a known issue and if there is a workaround.

Brendan Feary
Yarra Consulting Inc.
Contractor to BC Ministry of Health
Victoria, BC. Canada
250.727.8540
bjfeary@anonymised.com

geoserver.log (18.9 KB)

Geoserver Oracle Spatial Problem.doc (110 KB)

2

Brendan Feary ha scritto:

The Oracle exception - ORA-00942: table or view does not exist - is thrown when geoserver issues the SQL query to an Oracle datastore when a prepended schema name is expected or required. This error was reproduced by pasting the geoserver generated SQL into SQL Developer to issue the query, and worked only when the schema name was prepended to the table name and double quotes removed.
I specificed a schema in the data store definition, but this seems only to be used to filter the tables to those within that schema when defining a new feature type(expected behaviour), NOT when requesting requesting features from the data source.
Attached is the geoserver log file and some screen shots illustrating the problem. Note also that use of the double quotes enclosing the table name with the prepended schema name causes an 'ORA-00972 identifier too long' exception to the thrown. Query works correctly when double quotes are removed in SQL Developer.
Please confirm if this is a known issue and if there is a workaround.

Nope, it's not a known issue and I know no workaround.
Let me understand this one a bit better... it seems you connect with
a certain user, but you're trying to use tables outside of the
user schema, right? Are you specifying the schema in your Oracle
connection parameters? The datastore is coded in a way that allows
access to a single schema at a time, if you need to deal with
more than one schema, you have to create multiple datastores
each with a different schema.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

3

Hi,

In Oracle it is possible to access tables from another schema without
using the schema prefix. You have to make a public synonym for the
tables you want to access from another schema and you must grant select
privileges on these tables to PUBLIC (or the user of your datastore).

Kind regards,

Mark Terlien
Senior Consultant Geo-ICT

-----Oorspronkelijk bericht-----
Van: Andrea Aime [mailto:aaime@anonymised.com]
Verzonden: maandag 13 oktober 2008 14:50
Aan: Brendan Feary
CC: geoserver-users@lists.sourceforge.net
Onderwerp: Re: [Geoserver-users] Oracle exception with Geoserver1.6.5

Brendan Feary ha scritto:

The Oracle exception - ORA-00942: table or view does not exist - is
thrown when geoserver issues the SQL query to an Oracle datastore when

a

prepended schema name is expected or required. This error was

reproduced

by pasting the geoserver generated SQL into SQL Developer to issue the

query, and worked only when the schema name was prepended to the table

name and double quotes removed.

I specificed a schema in the data store definition, but this seems

only

to be used to filter the tables to those within that schema when
defining a new feature type(expected behaviour), NOT when requesting
requesting features from the data source.

Attached is the geoserver log file and some screen shots illustrating
the problem. Note also that use of the double quotes enclosing the

table

name with the prepended schema name causes an 'ORA-00972 identifier

too

long' exception to the thrown. Query works correctly when double

quotes

are removed in SQL Developer.

Please confirm if this is a known issue and if there is a workaround.

Nope, it's not a known issue and I know no workaround.
Let me understand this one a bit better... it seems you connect with
a certain user, but you're trying to use tables outside of the
user schema, right? Are you specifying the schema in your Oracle
connection parameters? The datastore is coded in a way that allows
access to a single schema at a time, if you need to deal with
more than one schema, you have to create multiple datastores
each with a different schema.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

------------------------------------------------------------------------
-
This SF.Net email is sponsored by the Moblin Your Move Developer's
challenge
Build the coolest Linux based applications with Moblin SDK & win great
prizes
Grand prize is a trip for two to an Open Source event anywhere in the
world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users