2

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;
  • How did you define the query? You probably have an error in the query due to wrong quoting: use double quotes for column aliases and single quotes for strings – unicoletti May 21 '12 at 6:49
  • @unicoletti I have edited my questioin I can do the query using sql developer and no such error there. – kinkajou May 21 '12 at 6:51
  • Try turning logging up to verbose and see what the full query and error message are in the logs – Ian Turton May 21 '12 at 7:40
3

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;
  • that was the cause and it solved it :) – kinkajou May 21 '12 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.