[mapserver-users] ms4w (fastcgi) and oracle 10?

Wed Nov 19 00:03:35 PST 2008

Hi,

Sorry, I cannot help you much further. I checked, however, the SQL against our Oracle 10g Spatial, and I got the same error 'SDO_GEOM' is not a procedure or is undefined. Reason is a typo in SDO_FILTER parameters.  Now it reads
WHERE SDO_FILTER( GEOM, MDSYS.SDO_GEOM(2003
It should read 
WHERE SDO_FILTER( GEOM, MDSYS.SDO_GEOMETRY(2003

If it is not just copy and paste error then it looks like MapServer is generating bad SQL

-Jukka Rahkonen-

> -----Alkuperäinen viesti-----
> Lähettäjä: zach cruise [mailto:zachc1980 at gmail.com] 
> Lähetetty: 18. marraskuuta 2008 21:21
> Vastaanottaja: mapserver-users at lists.osgeo.org
> Kopio: Rahkonen Jukka; Richard Polk; Smith,Michael 
> ERDC-CRREL-NH; Jeff McKenna
> Aihe: Re: [mapserver-users] ms4w (fastcgi) and oracle 10?
> 
> sql*plus returns this error:
> ORA-06553: PLS-221: 'SDO_GEOM' is not a procedure or is 
> undefined 06553. 00000 - "PLS-%s: %s"
> *Cause:
> *Action:
> Error at Line:x Column:x
> 
> "select ID, GEOM from STATES" works though. i have oracle 
> locator not spatial, if that matters. also column GEOM is 
> type SDO_GEOMETRY with MDSYS.SPATIAL_INDEX.
> 
> On Tue, Nov 18, 2008 at 12:49 PM, Rahkonen Jukka 
> <Jukka.Rahkonen at mmmtike.fi> wrote:
> > Hi,
> >
> > Have you checked that the SQL statement SELECT ID, GEOM FROM STATES 
> > WHERE SDO_FILTER( GEOM, MDSYS.SDO_GEOM(2003, 90112, 
> > NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO
> > _ORDINATE_ARRAY(-179,-89.5166667,179,83.5166667) 
> ),'querytype=window') 
> > = 'TRUE'
> >
> > works from SQL*Plus or something else that you are using 
> with your Oracle? If not, then investigate and adjust the 
> DATA string accordingly.
> >
> > -Jukka Rahkonen-
> >
> >
> >
> >
> >
> > -----Alkuperäinen viesti-----
> > Lähettäjä: mapserver-users-bounces at lists.osgeo.org puolesta: zach 
> > cruise
> > Lähetetty: ti 18.11.2008 19:44
> > Vastaanottaja: mapserver-users at lists.osgeo.org
> > Kopio: Richard Polk; Smith,Michael ERDC-CRREL-NH
> > Aihe: Re: [mapserver-users] ms4w (fastcgi) and oracle 10?
> >
> > thanks mike, but i get the same ora-xxxxx errors when using either:
> >
> > shp2img -m oracle.map -o oracle.png -all_debug 10 
> > msOracleSpatialLayerOpen called with: GEOM from STATES 
> using unique ID 
> > srid 90112 msOracleSpatialLayerOpen. Shared connection not 
> available. 
> > Creating one.
> > 
> msConnPoolRegister(layername,username/password at databasename,019A68E8)
> > msOracleSpatialLayerFreeItemInfo was called.
> > msOracleSpatialLayerWhichShapes was called.
> > msOracleSpatialLayerWhichShapes. Using this Sql to retrieve 
> the data:
> > SELECT ID, GEOM FROM STATES WHERE SDO_FILTER( GEOM, 
> > MDSYS.SDO_GEOM(2003, 90112,
> > 
> NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-179
> > ,-89.5166667,179,83.5166667)
> > ),'querytype=window') = 'TRUE'
> > msOracleSpatialLayerWhichShapes(): OracleSpatial error. Error:
> > ORA-29900: operator binding does not exist
> > ORA-06540: PL/SQL: compilation error
> > ORA-06553: PLS-907: cannot load library unit MDSYS.SDO_3GL 
> (referenced 
> > by MDSYS.SDO_FILTER)  . Query statement: SELECT ID, GEOM 
> FROM STATES 
> > WHERE SDO_FILTER( GEOM, MDSYS.SDO_GEOM(2003, 90112, 
> > NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO
> > _ORDINATE_ARRAY(-179,-89.5166667,179,83.5166667) 
> ),'querytype=window') 
> > = 'TRUE' . Check your data statement.
> >
> > using 4.8 with /ignored-libmap/oracle10g/libmap.dll. how do 
> i tell if 
> > i have native or ogr support for oracle 10g? i also edited 
> httpd.conf 
> > (did i need to?) from 
> > http://mapserver.gis.umn.edu/docs/howto/oracle_spatial_howto, but 
> > still no luck.
> >
> > On Tue, Nov 18, 2008 at 6:42 AM, Smith, Michael ERDC-CRREL-NH 
> > <Michael.Smith at usace.army.mil> wrote:
> >> Zach,
> >>
> >> Your DATA statement is almost correct. If you are going to use the 
> >> UNIQUE keyword (for supporting queries), you must put a 
> column name 
> >> after UNIQUE and before the SRID.
> >>
> >> Either of these would work:
> >>
> >> DATA ' GEOM from STATES using UNIQUE ID srid 90112'
> >>
> >> or
> >>
> >> DATA ' GEOM from STATES using srid 90112'
> >>
> >> Assuming you have a column named ID, of course.
> >>
> >> Mike
> >>
> >>
> >> --
> >> Michael Smith
> >> RSGIS Center
> >> ERDC - CRREL
> >> US Army Corps of Engineers
> >>
> >>
> >>
> >>
> >> On 11/17/08  8:21 PM, "zach cruise" <zachc1980 at gmail.com> wrote:
> >>
> >>> troubleshooting info on 4.8 box with 
> /ignored-libmap/oracle10g/libmap.dll:
> >>> shp2img -m oracle.map -o oracle.png -all_debug 10 
> >>> msOracleSpatialLayerOpen called with: GEOMETRY from STATES using 
> >>> unique srid 90112 msOracleSpatialLayerOpen. Shared connection not 
> >>> available. Creating one.
> >>> 
> msConnPoolRegister(layername,username/password at databasename,019A68E8
> >>> ) msOracleSpatialLayerFreeItemInfo was called.
> >>> msOracleSpatialLayerWhichShapes was called.
> >>> msOracleSpatialLayerWhichShapes(): OracleSpatial error. Error 
> >>> parsing OracleSpatial DATA variable. Must 
> be:'geometry_column FROM 
> >>> table_name [USING UNIQUE <column> SRID srid# FUNCTION]' or 
> >>> 'geometry_column FROM (SELECT stmt) [USING UNIQUE <column> SRID 
> >>> srid# FUNCTION]'. If want to set the FUNCTION statement 
> you can use: 
> >>> FILTER, RELATE, GEOMRELATE or NONE. Your data statement: 
> GEOM from 
> >>> STATES using unique srid 90112
> >>>
> >>> On Mon, Nov 17, 2008 at 7:34 PM, zach cruise 
> <zachc1980 at gmail.com> wrote:
> >>>> btw i got oracle 10 client (with tnsnames.ora) on 4.8 box. other 
> >>>> than returning multiple ora-xxxx errors, 4.8 also returns "web 
> >>>> template error" if i use 
> /ignored-libmap/oracle10g/libmap.dll. this 
> >>>> error is even for only shapefile maps. error goes away when i 
> >>>> switch back to normal libmap.dll.
> >>>>
> >>>> On Mon, Nov 17, 2008 at 5:56 PM, zach cruise 
> <zachc1980 at gmail.com> wrote:
> >>>>> looks like mod_fastcgi is not an option with ms4w and oracle 10.
> >>>>> anyway it had little benefits.
> >>>>>
> >>>>> i now got mapserver 5.2 and 4.8:
> >>>>> i thought 5.2 had native built-in support for oracle 
> 10, but it is 
> >>>>> giving me "OracleSpatial is not supported" error. i 
> also thought 
> >>>>> 4.8 could get ogr support for oracle 10 by following 
> the steps on
> >>>>>
> >> 
> http://www.maptools.org/ms4w/index.phtml?page=README_INSTALL.html#ora
> >> cle-10g>>>
> >> .
> >>>>> 4.8 connects, but returns multiple ORA-xxxx errors.
> >>>>>
> >>>>> so:
> >>>>> do i have to install oracle 10 client locally and make 
> changes to 
> >>>>> httpd.conf for 5.2 to work?
> >>>>> how do i find correct srid for 4.8 to work? (DATA 
> "GEOMETRY from 
> >>>>> (select GEOMETRY from STATES where NAME = 'New York')") any 
> >>>>> performance difference between native built-in and ogr supports?
> >>>>>
> >>>>> On Fri, Nov 14, 2008 at 6:22 PM, Jeff McKenna 
> >>>>> <jmckenna at gatewaygeomatics.com> wrote:
> >>>>>> zach cruise wrote:
> >>>>>>>
> >>>>>>> does current ms4w with fastcgi work with oracle 10?
> >>>>>>>
> >>>>>>> here're warnings
> >>>>>>> 
> http://www.maptools.org/ms4w/?page=README_INSTALL.html#f-fastcgi
> >>>>>>>
> >>>>>>> 
> http://www.nabble.com/fastcgi-connections-to-Oracle-and-SDE-on-W
> >>>>>>> indows-to1
> >>>>>>> 8394152.html
> >>>>>>> in testing this was successful with PostgreSQL 8.3.1 
> / PostGIS 
> >>>>>>> 1.3.3, but unsuccessful with Oracle 10.2.0.1.0 (Apache logs: 
> >>>>>>> "mod_fcgid: get overlap result error")
> >>>>>>>
> >>>>>>> if not, any workarounds?
> >>>>>>
> >>>>>> I tested and wrote that note...and am not aware of a 
> workaround 
> >>>>>> yet. You should also send this question to the MS4W 
> mailing list (subscribe at:
> >>>>>> http://lists.maptools.org/mailman/listinfo/ms4w-users)
> >>>>>>
> >>>>>>
> >>>>>> --
> >>>>>> Jeff McKenna
> >>>>>> FOSS4G Consulting and Training Services 
> >>>>>> http://www.gatewaygeomatics.com/
> >>>>>>
> >>>>>>
> >>>>>> _______________________________________________
> >>>>>> mapserver-users mailing list
> >>>>>> mapserver-users at lists.osgeo.org
> >>>>>> http://lists.osgeo.org/mailman/listinfo/mapserver-users
> >>>>>>
> >>>>>
> >>>>
> >>> _______________________________________________
> >>> mapserver-users mailing list
> >>> mapserver-users at lists.osgeo.org
> >>> http://lists.osgeo.org/mailman/listinfo/mapserver-users
> >>
> >>
> > _______________________________________________
> > mapserver-users mailing list
> > mapserver-users at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/mapserver-users
> >
> >
> 