Skip to main content
  • EN

I have a point featureclass and a geodatabase table which I have joined using SQL Creator. When I add an inspector and run the workbench, I cannot see any geometries from the featureclass which I have joined in SQL creator. Here is the query written in SQL creator.

select a.FCGLOBALID,a.FEATURECLASS, a.OPERATION, a.PROCESSSTATUS,
a.MXASSETNUM, b.ADDRESS, b.CITYNAME,b.DIVISION,b.FACILITYID, b.GISSYSTEMTYPE,b.GLOBALID, b.MXASSETNUM,
b.MXCREATIONSTATE,b.MXSITEID,b.OPCENTER,b.PATROLNUMBER,b.SERVICENUMBER,b.STATENAME,b.STATUS,
b.STREETADDRESS,b.STYLEDEFAULT,b.TOWNGASNAME,b.ZIPCODE, b.shape
from arceam.pub_to_p_delta a left join arceam.p_metersetting b
on a.FCGLOBALID = b.GLOBALID
where a.PROCESSSTATUS='N' and a.FEATURECLASS='P_METERSETTING'

This query work fine in sql developer but when I run the workbench, the execution is successful but I get following errors

BADNEWS: File C:\apps\FME\metafile\oracle_spatialDef.fmi could not be opened (tabrdr_classic.cpp:2756 - tabrdr::ClassicTableReader::TableReaderSource::open)
BADNEWS: An error occurred while fetching from query `SELECT SDO_VERSION FROM DUAL' : OCI_NO_DATA (ocistmt.cpp:663)
BADNEWS: An error occurred while fetching from query `select a.FCGLOBALID,a.FEATURECLASS, a.OPERATION, a.PROCESSSTATUS,
a.MXASSETNUM, b.ADDRESS, b.CITYNAME,b.DIVISION,b.FACILITYID, b.GISSYSTEMTYPE,b.GLOBALID, b.MXASSETNUM,
b.MXCREATIONSTATE,b.MXSITEID,b.OPCENTER,b.PATROLNUMBER,b.SERVICENUMBER,b.STATENAME,b.STATUS,
b.STREETADDRESS,b.STYLEDEFAULT,b.TOWNGASNAME,b.ZIPCODE, b.shape
from arceam.pub_to_p_delta a left join arceam.p_metersetting b
on a.FCGLOBALID = b.GLOBALID
where a.PROCESSSTATUS='N' and a.FEATURECLASS='P_METERSETTING'' : OCI_NO_DATA (ocistmt.cpp:663)
BADNEWS: An error occurred while fetching from query `select a.FCGLOBALID,a.FEATURECLASS, a.OPERATION, a.PROCESSSTATUS,
a.MXASSETNUM, b.ADDRESS, b.CITYNAME,b.DIVISION,b.FACILITYID, b.GISSYSTEMTYPE,b.GLOBALID, b.MXASSETNUM,
b.MXCREATIONSTATE,b.MXSITEID,b.OPCENTER,b.PATROLNUMBER,b.SERVICENUMBER,b.STATENAME,b.STATUS,
b.STREETADDRESS,b.STYLEDEFAULT,b.TOWNGASNAME,b.ZIPCODE, b.shape
from arceam.pub_to_p_delta a left join arceam.p_metersetting b
on a.FCGLOBALID = b.GLOBALID
where a.PROCESSSTATUS='N' and a.FEATURECLASS='P_METERSETTING'' : ORA-01002: fetch out of sequence (ocistmt.cpp:663)
BADNEWS: File C:\apps\FME\metafile\geodatabaseDefline_.fmi could not be opened (tabrdr_classic.cpp:2756 - tabrdr::ClassicTableReader::TableReaderSource::open) - (tabrdr_classic.cpp:1743 - tabrdr::ClassicTableReader::getRow) - (tabrdr_classic.cpp:1743 - tabrdr::ClassicTableReader::getRow)
Error running translation.

FME Desktop version, I am using is 2018.1.0.0

A possible workaround is to cast the shape column to WKB and then use the GeometryReplacer to recuperate the geometry inside FME. Example:

select sde.st_asbinary(b.shape) as shape 
from arceam.p_metersetting b

Hi David, I did the changes you suggested but got following errors

A possible workaround is to cast the shape column to WKB and then use the GeometryReplacer to recuperate the geometry inside FME. Example:

select sde.st_asbinary(b.shape) as shape 
from arceam.p_metersetting b

Hi David, I made the suggested change and got following errors

 

0684Q00000ArMDzQAN.png

Try the exact same query in e.g. SQLDeveloper or TOAD and see if you get the same ORA-01002 error.

Could you try using b.SHAPE?

Most databases (in your case ORACLE) return attribute names as uppercase.

And have you specified the SHAPE field as the geometry name in the database parameters?

The SQLCreator has to be told what the geometry field name is.

Try the exact same query in e.g. SQLDeveloper or TOAD and see if you get the same ORA-01002 error.

This query works but see the shape column

Could you try using b.SHAPE?

Most databases (in your case ORACLE) return attribute names as uppercase.

And have you specified the SHAPE field as the geometry name in the database parameters?

The SQLCreator has to be told what the geometry field name is.

Hi Erik,

I am new to FME and not sure how to specify the SHAPE field as the geometry name in database parameters, Thanks.

This query works but see the shape column

That's as expected, the shape column has actually become a BLOB due to the cast.

Try again in FME, if that doesn't work consider restarting your PC just to be sure that there's not an issue with the Oracle client.

If everything fails, try using WKT rather than WKB (sde.st_astext).

Hi Erik,

I am new to FME and not sure how to specify the SHAPE field as the geometry name in database parameters, Thanks.

Even though the documentation mentions the database parameters, they are not available (as on the reader).

So I tested it (and it worked) with a simple table and these settings:

Notice the uppercase name for the exposed attributes.

The first encountered geometry field will be used for geometry.

Hope this helps.

Hi Erik,

I am new to FME and not sure how to specify the SHAPE field as the geometry name in database parameters, Thanks.

Although: from other posts I notice you are using the SDE geometry type in the database.

That is stored as a BLOB and can not be used in the SQLCreator as output.

I suggest (as a workaround) using the Geodatabase reader and an Oracle reader for the metersetting table.

Then the FeatureJoiner to do the join in FME.

A possible workaround is to cast the shape column to WKB and then use the GeometryReplacer to recuperate the geometry inside FME. Example:

select sde.st_asbinary(b.shape) as shape 
from arceam.p_metersetting b

Hi David, I used WKB and WKT both and GeometryReplacer and this time I could at least see the geometry of the feature but a bunch of errors as below

0684Q00000ArMFMQA3.png

Even though the documentation mentions the database parameters, they are not available (as on the reader).

So I tested it (and it worked) with a simple table and these settings:

Notice the uppercase name for the exposed attributes.

The first encountered geometry field will be used for geometry.

Hope this helps.

Hi Erik, I have exposed SHAPE attribute in the SQL Creator parameter but no luck :(

Hi Erik, I have exposed SHAPE attribute in the SQL Creator parameter but no luck :(

Apparently the SHAPE attribute is not an Oracle (SDO) geometry, but a SDE geometry. Those are stored as BLOB (Binary Large Object) in a database and only to be read using the ESRI geodatabase reader, not a SQLCreator.

Hi David, I used WKB and WKT both and GeometryReplacer and this time I could at least see the geometry of the feature but a bunch of errors as below

I have seen that "fetch out of sequence" a lot when working with Oracle and storing in SDO Geometry. You have a bad geometry in there.

I have had to add a where a.shape.sdo_gtype = the correct geometry type. You can also use the SDO_UTIL.GETNUMVERTICES(a.shape) >= 1 for points and > 1 for lines.

Apparently the SHAPE attribute is not an Oracle (SDO) geometry, but a SDE geometry. Those are stored as BLOB (Binary Large Object) in a database and only to be read using the ESRI geodatabase reader, not a SQLCreator.

I had a reader earlier but the number of records are so high that the reader goes on reading every feature before performing a join resulting in performance hit.

Reply


Community Stats

31,808
Posts
120,815
Replies
39,431
Members

Latest FME

Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.