Oracle Spatial query problem

[Frank Warmerdam]

Mark Adams wrote:
> We’re working with mapserver and PHP mapscript, we have a build of 
> mapserver that supports Oracle Spatial. We’re using Oracle 9i. Mostly it 
> is working fine, however we have discovered a fundamental problem, not 
> sure if this is a know design limitation or not. When we query a layer 
> that has the connection type "ORACLESPATIAL", if any of the queried 
> fields has a field VALUE length > 256 characters in any of the queried 
> records, we get the following error:
> 
>  
> 
> “<b>Fatal error</b>:  [MapServer Error]: msOracleSpatialLayerGetShape(): 
> Error: ORA-1406: fetched column value was truncated  . Query statement: 
> SELECT Field1, field2, field3, Field4, field5, field6, Field7, field8, 
> field9, Field10, field11, field12, GEOM FROM MyTable WHERE Field1= 9506 
> .Check your data statement.”
> 
>  
> 
> This error appears on the line:
> 
> $oShape = $Layer->getShape($oRes->tileindex,$oRes->shapeindex)
> 
>  
> 
> Note that it doesn’t matter what the actual field length is, if a field 
> is defined as varchar(4000) but the field value is only 250 characters 
> it will work, but as soon as the value is greater than 256 characters it 
> fails.
> 
>  
> 
> Is this a known limitation, or a bug? I’m hoping it can be resolved, 
> because if not it will blow up what was looking like a fine solution.

Mark,

Skimming the code, I believe you can increase the value of TEXT_SIZE near
the beginning of maporaclespatial.c to increase this limit.  Fernando Simon
would need to give the final word on the best approach.  I certainly feel
that this ought to be handled more dynamically at some point.

Looking into the OGR Oracle code, I'm not sure if it handles this properly
either.  It supports binding fields with widths larger than 256, but I'm not
sure what it would do with a variable length field with no defined limit (if
such things exist).

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | President OSGF, http://osgeo.org