I have a SDE.ST_GEOMETRY column in an Oracle 18c/10.7.1 geodatabase.

I want to convert the SDE.ST_GEOMETRY shape column to SDO_GEOMETRY using SQL.


  1. Convert the SDE.ST_GEOMETRY shape column to well-known text using ST_AsText.
  • to_char(sde.st_astext(shape))
  • LINESTRING ( 675939.0727821561 4861635.038876267, 675881.8875467349 4861589.914457718, 675879.1765119368 4861586.041565847, 675877.9556495566 4861580.512319708, 675879.8962382395 4861575.485510156, 675976.9673410757 4861477.697030321, 675982.1957818093 4861474.598758776, 675988.0051615685 4861472.468589587, 675994.20185149 4861471.306795442, 676000.9793965323 4861470.919485278, 676006.788765802 4861471.306795442, 676011.6298439335 4861472.275034143, 676150.6204311419 4861517.834283836)
  1. From there, I can convert the well-known text to SDO_GEOMETRY using SDO_UTIL.FROM_WKTGEOMETRY:
  • sdo_util.from_wktgeometry(sde.st_astext(shape))


Why are there so many nulls in the SDO_GEOMETRY result? Can they be avoided?

For what it's worth, I'm using Toad for Oracle (version

  • Read through the ESRI docs on the SDE types; those are geometric predicates like 'has Z Dimension' etc., and likely correct. Here is the ST_GEOMETRY parameter list.
    – geozelot
    Jan 12, 2020 at 9:57

3 Answers 3


I have access to Oracle not ESRI so I can't help with all aspects.

The WKT looks fine, though the space after the first ( is normally not there.

Try using the sdo_geometry(WKT,srid) constructor supplying the SRID rather than the sdo_util function.

Take the WKT as a string and directly constructed the SDO_GEOMETRY with it rather than using st_astext, this will isolate the problem.

  • You appear to have created two accounts so please follow these instructions to merge them.
    – PolyGeo
    Jul 1, 2020 at 7:35

I have seen some application software, like TOAD, display Oracle VARRAYs in ways like this.

The SDO_GEOMETRY may be fine which is why I suggest querying it for sdo_util.GetNumVertices (tests sdo_ordinate_array)and sdo_util.getNumElem() (tests sdo_elem_info_array)

See my answer to your other thread here:


  • SDO_GEOMETRY supports an ST_IsValid request, so basic validation would be more useful than retrieving vertex counts.
    – Vince
    Jan 12, 2020 at 14:26
  • 1
    I agree with Simon. I think you are trying to use Toad to do your spatial work. This is fraught as the Toad data grid GUI presents varrays in an unexpected fashion. The tell is when your SDO_ELEM_INFO varray is padded out to 99 elements with NULLs and when the SDO_ORDINATES varray is padded to 99 or truncated at 99 elements. I believe your process to convert the data from SDE is SDO is just fine but Toad is tricking you into thinking its not. Cast your results into a wkt or json CLOB and all will look as expected.
    – pauldzy
    Jan 12, 2020 at 14:58
  • @pauldzy and Simon: You were right. Toad was the problem. gis.stackexchange.com/a/413874/62572
    – User1974
    May 15, 2022 at 17:20

I think @pauldzy's comment was the correct answer.

I believe your process to convert the data from SDE is SDO is just fine but Toad is tricking you into thinking its not. Cast your results into a wkt or json CLOB and all will look as expected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.