[mapserver-users] Query Error in getFeatureRequest on Oracle DB
Stefan Schantz
sschantz at bfs.de
Fri Dec 18 06:22:17 EST 2009
Hello,
I have a layer that gets its data from an Oracle database. The layer can
be visualized without any problems.
As soon as I send a getFeatureRequest, the following error message appears
"msOracleSpatialLayerGetItems (): Query error. Can not retrieve column
list "
In the UMN log file are 2 SQLs. The one with the spatial SQL query works
with the
Oracle Developer and I get a result. Why doesn't get my UMN (version
5.2) any data?
Thanks for your support!
Regards
Stefan Schantz
############# Ausschnitt Layerdefinition##############
LAYER
NAME "aero_nukspez_cs137"
STATUS ON
DEBUG ON
TYPE POINT
PROJECTION
"init=epsg:25832"
END
METADATA
...
END
DUMP TRUE
CONNECTIONTYPE oraclespatial
CONNECTION
"***/***@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=***)(PORT=
1521)))(CONNECT_DATA=(SID=***)))"
PROCESSING "CLOSE_CONNECTION=DEFER"
DATA "GEOM from (SELECT
geom,
P_PROBE.PROBEENTNAHME_BEGINN,
P_PROBE.PROBEENTNAHME_ENDE,
S_MESSGROESSE.BEZEICHNUNG,
to_char(P_MESSWERT.MESSWERT, '0.99999') AS WERT,
S_MESS_EINHEIT1.BEZEICHNUNG,
P_MESSWERT.MESSFEHLER,
P_PROBE.MITTELUNGSDAUER,
S_NETZ_BETREIBER.BEZEICHNUNG,
dwd_mess_utm.mst_id AS ID,
P_ENTNAHMEORT.KOORD_X_UTM,
P_ENTNAHMEORT.KOORD_Y_UTM,
((R_VORGANG.OID)-(255297803876564992)),
R_VORGANG.AUSFUEHRUNGSDATUM,
P_MESSUNG.STATUS_1,
P_MESSUNG.STATUS_2,
P_MESSUNG.STATUS_4,
S_STATUS_BMU.BEZEICHNUNG,
P_PROBE.IST_TESTDATENSATZ
FROM
dwd_mess_utm,IMIS.P_ENTNAHMEORT,IMIS.S_MESS_EINHEIT
S_MESS_EINHEIT1,IMIS.S_MESSGROESSE,IMIS.S_STATUS_BMU,IMIS.P_MESSUNG,IMIS.S_NETZ_BETREIBER,IMIS.P_MESSWERT,IGS2.R_VORGANG_MESSUNG_P,IMIS.P_PROBE,IMIS.S_MESS_STELLE
S_MESS_STELLE14,IGS2.R_VORGANG
WHERE
(R_VORGANG_MESSUNG_P.S_OID = R_VORGANG.OID(+)
AND R_VORGANG_MESSUNG_P.T_PROBE_ID(+) = P_MESSUNG.PROBE_ID
AND R_VORGANG_MESSUNG_P.T_MESSUNGS_ID(+) = P_MESSUNG.MESSUNGS_ID
AND p_probe.mst_id = dwd_mess_utm.mst_id
AND P_ENTNAHMEORT.PROBE_ID = P_PROBE.PROBE_ID
AND P_MESSUNG.STATUS_5 = S_STATUS_BMU.STATUS_5
AND S_MESS_STELLE14.NETZBETREIBER_ID =
S_NETZ_BETREIBER.NETZBETREIBER_ID
AND P_MESSUNG.PROBE_ID = P_PROBE.PROBE_ID
AND P_MESSWERT.MESSGROESSE_ID = S_MESSGROESSE.MESSGROESSE_ID
AND P_ENTNAHMEORT.PROBE_ID = P_PROBE.PROBE_ID
AND P_PROBE.MST_ID = S_MESS_STELLE14.MST_ID
AND P_MESSWERT.PROBE_ID = P_MESSUNG.PROBE_ID
AND P_MESSWERT.MESSUNGS_ID = P_MESSUNG.MESSUNGS_ID
AND P_MESSWERT.MEH_ID = S_MESS_EINHEIT1.MEH_ID
AND (P_PROBE.UMW_ID IN ('L31'))
AND (P_MESSWERT.MESSGROESSE_ID IN (373))
AND (P_ENTNAHMEORT.STAAT_ID IN (0))
AND (P_PROBE.DATENBASIS_ID IN (1))
AND (P_PROBE.NETZBETREIBER_ID IN ('W'))
AND P_PROBE.PROBEENTNAHME_BEGINN = TO_DATE('16.12.2009
06:00','DD.MM.YYYY HH24:MI')))
USING UNIQUE ID SRID 25832"
TEMPLATE "../templates/getFeature_ODL.html"
...
###########################end layer definition###########################
################# MS LOGFILE######################################
[Fri Dec 18 08:36:05 2009].932804 msOracleSpatialLayerClose. Cleaning
Oracle Cache.
[Fri Dec 18 08:36:05 2009].932814 msOracleSpatialLayerClose. Release the
Oracle Pool.
[Fri Dec 18 08:36:05 2009].932816
msConnPoolRelease(aero_nukspez_cs137,i****/***@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=***)(PORT=
1521)))(CONNECT_DATA=(SID=***))),82368a8)
[Fri Dec 18 08:36:05 2009].932981
msConnPoolClose(***/***@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=***)(PORT=
1521)))(CONNECT_DATA=(SID=***))),82368a8)
[Fri Dec 18 08:36:06 2009].470320 msOracleSpatialLayerOpen called with:
GEOM from (SELECT
geom,
P_PROBE.PROBEENTNAHME_BEGINN,
P_PROBE.PROBEENTNAHME_ENDE,
S_MESSGROESSE.BEZEICHNUNG,
to_char(P_MESSWERT.MESSWERT, '0.99999') AS WERT,
S_MESS_EINHEIT1.BEZEICHNUNG,
P_MESSWERT.MESSFEHLER,
P_PROBE.MITTELUNGSDAUER,
S_NETZ_BETREIBER.BEZEICHNUNG,
dwd_mess_utm.mst_id AS ID,
P_ENTNAHMEORT.KOORD_X_UTM,
P_ENTNAHMEORT.KOORD_Y_UTM,
((R_VORGANG.OID)-(255297803876564992)),
R_VORGANG.AUSFUEHRUNGSDATUM,
P_MESSUNG.STATUS_1,
P_MESSUNG.STATUS_2,
P_MESSUNG.STATUS_4,
S_STATUS_BMU.BEZEICHNUNG,
P_PROBE.IST_TESTDATENSATZ
FROM
dwd_mess_utm,IMIS.P_ENTNAHMEORT,IMIS.S_MESS_EINHEIT
S_MESS_EINHEIT1,IMIS.S_MESSGROESSE,IMIS.S_STATUS_BMU,IMIS.P_MESSUNG,IMIS.S_NETZ_BETREIBER,IMIS.P_MESSWERT,IGS2.R_VORGANG_MESSUNG_P,IMIS.P_PROBE,IMIS.S_MESS_STELLE
S_MESS_STELLE14,IGS2.R_VORGANG
WHERE
(R_VORGANG_MESSUNG_P.S_OID = R_VORGANG.OID(+)
AND R_VORGANG_MESSUNG_P.T_PROBE_ID(+) = P_MESSUNG.PROBE_ID
AND R_VORGANG_MESSUNG_P.T_MESSUNGS_ID(+) = P_MESSUNG.MESSUNGS_ID
AND p_probe.mst_id = dwd_mess_utm.mst_id
AND P_ENTNAHMEORT.PROBE_ID = P_PROBE.PROBE_ID
AND P_MESSUNG.STATUS_5 = S_STATUS_BMU.STATUS_5
AND S_MESS_STELLE14.NETZBETREIBER_ID =
S_NETZ_BETREIBER.NETZBETREIBER_ID
AND P_MESSUNG.PROBE_ID = P_PROBE.PROBE_ID
AND P_MESSWERT.MESSGROESSE_ID = S_MESSGROESSE.MESSGROESSE_ID
AND P_ENTNAHMEORT.PROBE_ID = P_PROBE.PROBE_ID
AND P_PROBE.MST_ID = S_MESS_STELLE14.MST_ID
AND P_MESSWERT.PROBE_ID = P_MESSUNG.PROBE_ID
AND P_MESSWERT.MESSUNGS_ID = P_MESSUNG.MESSUNGS_ID
AND P_MESSWERT.MEH_ID = S_MESS_EINHEIT1.MEH_ID
AND (P_PROBE.UMW_ID IN ('L31'))
AND (P_MESSWERT.MESSGROESSE_ID IN (373))
AND (P_ENTNAHMEORT.STAAT_ID IN (0))
AND (P_PROBE.DATENBASIS_ID IN (1))
AND (P_PROBE.NETZBETREIBER_ID IN ('W'))
AND P_PROBE.PROBEENTNAHME_BEGINN = TO_DATE('16.12.2009
06:00','DD.MM.YYYY HH24:MI')))
USING UNIQUE ID SRID 25832
[Fri Dec 18 08:36:06 2009].494014 msOracleSpatialLayerOpen. Shared
connection not available. Creating one.
[Fri Dec 18 08:36:06 2009].494020
msConnPoolRegister(aero_nukspez_cs137,***/***@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=***)(PORT=
1521)))(CONNECT_DATA=(SID=***))),82368a8)
[Fri Dec 18 08:36:06 2009].500778 msOracleSpatialLayerFreeItemInfo was
called.
[Fri Dec 18 08:36:06 2009].500796 msOracleSpatialLayerInitItemInfo was
called.
[Fri Dec 18 08:36:06 2009].500972 msOracleSpatialLayerWhichShapes was
called.
[Fri Dec 18 08:36:06 2009].501042 msOracleSpatialLayerWhichShapes. Using
this Sql to retrieve the data: SELECT ID, WERT, GEOM FROM (SELECT
geom,
P_PROBE.PROBEENTNAHME_BEGINN,
P_PROBE.PROBEENTNAHME_ENDE,
S_MESSGROESSE.BEZEICHNUNG,
to_char(P_MESSWERT.MESSWERT, '0.99999') AS WERT,
S_MESS_EINHEIT1.BEZEICHNUNG,
P_MESSWERT.MESSFEHLER,
P_PROBE.MITTELUNGSDAUER,
S_NETZ_BETREIBER.BEZEICHNUNG,
dwd_mess_utm.mst_id AS ID,
P_ENTNAHMEORT.KOORD_X_UTM,
P_ENTNAHMEORT.KOORD_Y_UTM,
((R_VORGANG.OID)-(255297803876564992)),
R_VORGANG.AUSFUEHRUNGSDATUM,
P_MESSUNG.STATUS_1,
P_MESSUNG.STATUS_2,
P_MESSUNG.STATUS_4,
S_STATUS_BMU.BEZEICHNUNG,
P_PROBE.IST_TESTDATENSATZ
FROM
dwd_mess_utm,IMIS.P_ENTNAHMEORT,IMIS.S_MESS_EINHEIT
S_MESS_EINHEIT1,IMIS.S_MESSGROESSE,IMIS.S_STATUS_BMU,IMIS.P_MESSUNG,IMIS.S_NETZ_BETREIBER,IMIS.P_MESSWERT,IGS2.R_VORGANG_MESSUNG_P,IMIS.P_PROBE,IMIS.S_MESS_STELLE
S_MESS_STELLE14,IGS2.R_VORGANG
WHERE
(R_VORGANG_MESSUNG_P.S_OID = R_VORGANG.OID(+)
AND R_VORGANG_MESSUNG_P.T_PROBE_ID(+) = P_MESSUNG.PROBE_ID
AND R_VORGANG_MESSUNG_P.T_MESSUNGS_ID(+) = P_MESSUNG.MESSUNGS_ID
AND p_probe.mst_id = dwd_mess_utm.mst_id
AND P_ENTNAHMEORT.PROBE_ID = P_PROBE.PROBE_ID
AND P_MESSUNG.STATUS_5 = S_STATUS_BMU.STATUS_5
AND S_MESS_STELLE14.NETZBETREIBER_ID =
S_NETZ_BETREIBER.NETZBETREIBER_ID
AND P_MESSUNG.PROBE_ID = P_PROBE.PROBE_ID
AND P_MESSWERT.MESSGROESSE_ID = S_MESSGROESSE.MESSGROESSE_ID
AND P_ENTNAHMEORT.PROBE_ID = P_PROBE.PROBE_ID
AND P_PROBE.MST_ID = S_MESS_STELLE14.MST_ID
AND P_MESSWERT.PROBE_ID = P_MESSUNG.PROBE_ID
AND P_MESSWERT.MESSUNGS_ID = P_MESSUNG.MESSUNGS_ID
AND P_MESSWERT.MEH_ID = S_MESS_EINHEIT1.MEH_ID
AND (P_PROBE.UMW_ID IN ('L31'))
AND (P_MESSWERT.MESSGROESSE_ID IN (373))
AND (P_ENTNAHMEORT.STAAT_ID IN (0))
AND (P_PROBE.DATENBASIS_ID IN (1))
AND (P_PROBE.NETZBETREIBER_ID IN ('W'))
AND P_PROBE.PROBEENTNAHME_BEGINN = TO_DATE('16.12.2009
06:00','DD.MM.YYYY HH24:MI'))) WHERE SDO_FILTER( GEOM,
MDSYS.SDO_GEOMETRY(2003, 25832,
NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(814703.455,5375543.26,827001.233,5393241.81)
),'querytype=window') = 'TRUE'
[Fri Dec 18 08:36:06 2009].543163 msOracleSpatialLayerFreeItemInfo was
called.
[Fri Dec 18 08:36:06 2009].543169 msOracleSpatialLayerClose was called.
Layer name: aero_nukspez_cs137. Layer connection:
***/***@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=***)(PORT=
1521)))(CONNECT_DATA=(SID=***)))
[Fri Dec 18 08:36:06 2009].543172 msOracleSpatialLayerClose. Cleaning
layerinfo handlers.
[Fri Dec 18 08:36:06 2009].543201 msOracleSpatialLayerClose. Cleaning
Oracle Cache.
[Fri Dec 18 08:36:06 2009].543236 msOracleSpatialLayerClose. Release the
Oracle Pool.
[Fri Dec 18 08:36:06 2009].543238
msConnPoolRelease(aero_nukspez_cs137,***/***@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=***)(PORT=
1521)))(CONNECT_DATA=(SID=***))),82368a8)
[Fri Dec 18 08:36:06 2009].543297 msOracleSpatialLayerOpen called with:
GEOM from (SELECT
geom,
P_PROBE.PROBEENTNAHME_BEGINN,
P_PROBE.PROBEENTNAHME_ENDE,
S_MESSGROESSE.BEZEICHNUNG,
to_char(P_MESSWERT.MESSWERT, '0.99999') AS WERT,
S_MESS_EINHEIT1.BEZEICHNUNG,
P_MESSWERT.MESSFEHLER,
P_PROBE.MITTELUNGSDAUER,
S_NETZ_BETREIBER.BEZEICHNUNG,
dwd_mess_utm.mst_id AS ID,
P_ENTNAHMEORT.KOORD_X_UTM,
P_ENTNAHMEORT.KOORD_Y_UTM,
((R_VORGANG.OID)-(255297803876564992)),
R_VORGANG.AUSFUEHRUNGSDATUM,
P_MESSUNG.STATUS_1,
P_MESSUNG.STATUS_2,
P_MESSUNG.STATUS_4,
S_STATUS_BMU.BEZEICHNUNG,
P_PROBE.IST_TESTDATENSATZ
FROM
dwd_mess_utm,IMIS.P_ENTNAHMEORT,IMIS.S_MESS_EINHEIT
S_MESS_EINHEIT1,IMIS.S_MESSGROESSE,IMIS.S_STATUS_BMU,IMIS.P_MESSUNG,IMIS.S_NETZ_BETREIBER,IMIS.P_MESSWERT,IGS2.R_VORGANG_MESSUNG_P,IMIS.P_PROBE,IMIS.S_MESS_STELLE
S_MESS_STELLE14,IGS2.R_VORGANG
WHERE
(R_VORGANG_MESSUNG_P.S_OID = R_VORGANG.OID(+)
AND R_VORGANG_MESSUNG_P.T_PROBE_ID(+) = P_MESSUNG.PROBE_ID
AND R_VORGANG_MESSUNG_P.T_MESSUNGS_ID(+) = P_MESSUNG.MESSUNGS_ID
AND p_probe.mst_id = dwd_mess_utm.mst_id
AND P_ENTNAHMEORT.PROBE_ID = P_PROBE.PROBE_ID
AND P_MESSUNG.STATUS_5 = S_STATUS_BMU.STATUS_5
AND S_MESS_STELLE14.NETZBETREIBER_ID =
S_NETZ_BETREIBER.NETZBETREIBER_ID
AND P_MESSUNG.PROBE_ID = P_PROBE.PROBE_ID
AND P_MESSWERT.MESSGROESSE_ID = S_MESSGROESSE.MESSGROESSE_ID
AND P_ENTNAHMEORT.PROBE_ID = P_PROBE.PROBE_ID
AND P_PROBE.MST_ID = S_MESS_STELLE14.MST_ID
AND P_MESSWERT.PROBE_ID = P_MESSUNG.PROBE_ID
AND P_MESSWERT.MESSUNGS_ID = P_MESSUNG.MESSUNGS_ID
AND P_MESSWERT.MEH_ID = S_MESS_EINHEIT1.MEH_ID
AND (P_PROBE.UMW_ID IN ('L31'))
AND (P_MESSWERT.MESSGROESSE_ID IN (373))
AND (P_ENTNAHMEORT.STAAT_ID IN (0))
AND (P_PROBE.DATENBASIS_ID IN (1))
AND (P_PROBE.NETZBETREIBER_ID IN ('W'))
AND P_PROBE.PROBEENTNAHME_BEGINN = TO_DATE('16.12.2009
06:00','DD.MM.YYYY HH24:MI')))
USING UNIQUE ID SRID 25832
[Fri Dec 18 08:36:06 2009].543311
msConnPoolRequest(aero_nukspez_cs137,***/***@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=***)(PORT=
1521)))(CONNECT_DATA=(SID=***)))) -> got 82368a8
[Fri Dec 18 08:36:06 2009].550280 msOracleSpatialLayerFreeItemInfo was
called.
[Fri Dec 18 08:36:06 2009].550283 msOracleSpatialLayerGetItems was called.
[Fri Dec 18 08:36:06 2009].562636 msOracleSpatialLayerGetItems(): Query
error. Cannot retrieve column list
[Fri Dec 18 08:36:06 2009].562686 mapserv request processing time
(msLoadMap not incl.): 0.093s
[Fri Dec 18 08:36:06 2009].562706 msFreeMap(): freeing map at 82052a8.
_______________________________________________
Mapserver-DE mailing list
Mapserver-DE at freegis.org
https://freegis.org/mailman/listinfo/mapserver-de
More information about the mapserver-users
mailing list