Skip to main content
  • EN
Hi all,

 

 

This is the issue:

 

I have an Oracle spatial (point) table where at least one record has an empty geometry field. The field isn't null, it's completely empty. This record caused my initial workbench to crash with an spatial index creation error.

 

 

I identified the offending table and started analyzing the table starting with a workbench containg only the table (using an Oracle spatial reader) and an inspector. That one gave this error:

 

An error occurred converting an Oracle OCINumber object to an integer: `OCI-22060: argument u2] is an invalid or uninitialized number'. (serverType=`ORACLE8i', serverName=`pkgnnat', userName=`KGNAT_HAVL', password=`***', dbname=`')

Error converting Oracle Spatial object geometry type to an integer: message was `An error occurred converting an Oracle OCINumber object to an integer: `OCI-22060: argument u2] is an invalid or uninitialized number'. (serverType=`ORACLE8i', serverName=`pkgnnat', userName=`KGNAT_HAVL', password=`***', dbname=`')'

 

This error kept coming back whatever I tried:

 

A geometryfilter (looking for null type object): error

 

Geometrextractor/ geometryremover: error

 

 

Command line Oracle spatial functions yield this error:

 

ORA-06502: PL/SQL: numeric or value error

 

ORA-06512: at "MDSYS.SDO_GEOM", line 70

 

ORA-06512: at "MDSYS.SDO_GEOM", line 1851

 

 

And a query looking for an empty field fail as well.

 

 

BTW: I identified the record by looking at the number of records the workbench WAS able to read and check the next one.

 

 

Any suggestions?
Hi,

 

 

I'm not quite sure I understand what you mean by "The field isn't null, it's completely empty", but you could try to execute something like this on your feature table using e.g. SQL Developer, Toad, etc:

 

 

update MY_POINT_TABLE set SHAPE = sde.st_geometry_operators.St_geomfromwkb_f('POINT EMPTY', 0) where coalesce(SHAPE, '') = ''

 

 

This will insert an empty point geometry into all features where the SHAPE column contains either NULL or an empty string. You might need to modify the where-clause slightly if I've misunderstood the part about the empty field.

 

 

Remember to do a COMMIT after, if necessary.

 

 

It would be kind if you'd let us know if this works :-)

 

 

David

 

Sorry, there is an error below, the command should probably be more like this:

 

 

update MY_POINT_TABLE set GEOM = SDO_UTIL.FROM_WKTGEOMETRY('POINT EMPTY') where coalesce(GEOM, '') = ''

 

 

David
hi,

 

 

Thanks for the quick answer. This is what I meant when I wrote about the "empty" geometry:

 

 

An "valid" null geometry would have no ordinates but the value "NULL" for the SDO_GTYPE. That something I have seen before and is accepted by the client software (ESRI) I use. But here the entire record is empty.

 

 

SQL> select distinct(p.shape.sdo_gtype) from pkunstwerk p;

 

SHAPE.SDO_GTYPE

 

---------------

 

 

2001

 

 

You're solution would work but the workbench will have to convert about 200 tables every months so I am looking for an automatic solution.

 

Hi,

 

 

one solution could be to forego the Oracle reader and replace it with a SQLCreator. The SQLCreator should include the SHAPE field cast as WKB (hint: sde.st_asbinary()) and a CASE-clause to return an empty geometry if the field is empty.

 

 

Hope this makes sense.

 

 

David

Reply


Community Stats

30,162
Posts
114,482
Replies
37,809
Members
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.