Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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;
share|improve this question
    
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 –  iant May 21 '12 at 7:40

1 Answer 1

up vote 3 down vote accepted

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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