select * from gds.emb a
where a.DATAHORA >=to_date('01/01/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and a.DATAHORA <to_date('01/02/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and sdo_RELATE(a.GEOM,@Value(_geometry),'mask=anyinteract')='TRUE'
Do you need to use the SQLExecutor?
I would use a reader for the ESRI feature and feed that to a FeatureReader transformer, reading the Oracle table and defining the spatial filter in the FeatureReader.
That is a lot easier.
Hi @patiaserica, would you be able to elaborate on the issues you're experiencing? Can you share any of the error messages?
I suspect it's the contents of the "_geometry" attribute that's not recognised by Oracle, what is the geometry encoding type used in the GeometryExtractor? If you use OGC Well Known Text, you could try:
select * from gds.emb a
where a.DATAHORA >=to_date('01/01/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and a.DATAHORA <to_date('01/02/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and sdo_RELATE(a.GEOM,SDO_UTIL.FROM_WKTGEOMETRY('@Value(_geometry)'),'mask=anyinteract')='TRUE'
Notice the use of FROM_WKTGEOMETRY() to parse the Well Known Text-string into an Oracle spatial object.
I suspect it's the contents of the "_geometry" attribute that's not recognised by Oracle, what is the geometry encoding type used in the GeometryExtractor? If you use OGC Well Known Text, you could try:
select * from gds.emb a
where a.DATAHORA >=to_date('01/01/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and a.DATAHORA <to_date('01/02/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and sdo_RELATE(a.GEOM,SDO_UTIL.FROM_WKTGEOMETRY('@Value(_geometry)'),'mask=anyinteract')='TRUE'
Notice the use of FROM_WKTGEOMETRY() to parse the Well Known Text-string into an Oracle spatial object.
Yes, I am using OGC WKT and already tried that one, but having this kind of error:
ORA-06512: em "MDSYS.MD", line 1723
ORA-06512: em "MDSYS.MDERR", line 17
ORA-06512: em "MDSYS.SDO_CS", line 3019
ORA-06512: em "MDSYS.SDO_3GL", line 205'
I suspect it's the contents of the "_geometry" attribute that's not recognised by Oracle, what is the geometry encoding type used in the GeometryExtractor? If you use OGC Well Known Text, you could try:
select * from gds.emb a
where a.DATAHORA >=to_date('01/01/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and a.DATAHORA <to_date('01/02/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and sdo_RELATE(a.GEOM,SDO_UTIL.FROM_WKTGEOMETRY('@Value(_geometry)'),'mask=anyinteract')='TRUE'
Notice the use of FROM_WKTGEOMETRY() to parse the Well Known Text-string into an Oracle spatial object.
Maybe the problem is lack of spatial index?
Maybe the problem is lack of spatial index?
Not sure, but that ORA-message is purely Oracle not not directly related to FME. Try executing the same query in Toad or SQLDeveloper and see if you get more information.
Do you need to use the SQLExecutor?
I would use a reader for the ESRI feature and feed that to a FeatureReader transformer, reading the Oracle table and defining the spatial filter in the FeatureReader.
That is a lot easier.
Worked perfectly!
It is possible to do that.
You have to built the SDO string. I made a workbench to do that (after quite some fiddling). Explanation on ORACLE site is used to create it.
(it works as long as the geometry fits in a varchar as I pass it trough an fme attribute, after which you need to go for a PLSQL which I have not managed to do yet)
The WKT/WKB I tried as well, but you cant pass the SRID with the version fme uses. That is where oracle yields an error. You need updated wkt aka WKT2 for that.
To pass the srid you need
creating the sdo geometry string and executing the spatial query using a sql executor is faster then using a feature reader.
