[GIS] Publishing oracle spatial table in geoserver

geoserveroracle-spatial

I published oracle view in geoserver. using this link. But when I see this in layer preview I get following xml response:

<?xml version="1.0" encoding="UTF-8" standalone="no"?><!DOCTYPE ServiceExceptionReport SYSTEM "http://192.168.70.65:80/geoserver/schemas/wms/1.1.1/WMS_exception_1_1_1.dtd"> <ServiceExceptionReport version="1.1.1" >   <ServiceException code="internalError">
      Rendering process failed
java.io.IOException
null
ORA-00904: &quot;NEPALCLUSTER&quot;: invalid identifier

</ServiceException></ServiceExceptionReport>



 CREATE OR REPLACE FORCE VIEW "myview"."d_DAILY" ("nepalCluster", "nepalclutter", "ZONE", "MI_PRINX", "MI_STYLE", "GEOLOC", "VAL") AS 
  select 
  c.nepalCluster,c.nepalclutter,c.zone,c.mi_prinx,c.mi_style,c.geoloc,NVL(coalesce(optz.VAL,opth.VAL),-1) VAL
from 
CLUSTER_REGION c
left join 
(select 
* 
from gDaily a) optz
on 
optz.CELL_CLUSTER = c.nepalCluster
left join
(select 
* 
from zDaily b) opth
on 
opth.CELL_CLUSTER = c.nepalCluster;

Best Answer

When creating a view/table, or any other object for that matter, in Oracle it is best not to use double quotes around identifiers because that tells Oracle that the name is case sensitive. Oracle by default is not case sensitive (even for username/passwords, if I recall correctly).

So, when in Oracle and Postgres you name an object (table, column, etc) with double quotes like:

 "d_DAILY"

you are instructing Oracle to consider case when retrieving it, so all the following identifiers won't work:

 d_DAILY, D_DAILY, d_daily,....

Since this 'feature' is rarely used most client software fails because it simply reverts to using uppercase identifiers.

To fix this error I suggest you recreate the view without the double quotes around the view and the column definitions like the following:

CREATE OR REPLACE FORCE VIEW myview.d_DAILY (nepalCluster, nepalclutter, ZONE, MI_PRINX, MI_STYLE, GEOLOC, VAL) AS 
select c.nepalCluster,c.nepalclutter,c.zone,c.mi_prinx,c.mi_style,c.geoloc,NVL(coalesce    (optz.VAL,opth.VAL),-1) VAL
from 
CLUSTER_REGION c
left join 
(select 
 * 
from gDaily a) optz
on 
optz.CELL_CLUSTER = c.nepalCluster
left join
(select 
* 
from zDaily b) opth
on 
opth.CELL_CLUSTER = c.nepalCluster;
Related Question