2 Replies Latest reply: Feb 21, 2014 4:02 AM by Paul Dziemiela RSS

    SDO_GEOMETRY and datapump query - not possible?

    Paul Dziemiela

      Using 11.2.0.3

       

      Hi folks,

       

      I move spatial data quite a bit with Oracle datapump but almost always I just toss entire tables about.  Recently I was thinking about using the query functionality of datapump to filter and possibly spatially order my data.  This seems like a reasonable idea.  However I find that datapump just does not seem to recognize SDO_GEOMETRY columns as being SDO_GEOMETRY.

       

      Take this for instance.  Let's say I would like to filter out all multi-geometries from my export.

       

         QUERY="WHERE SDO_UTIL.GETNUMELEM(ku$.shape) = 1"

       

         ORA-06553: PLS-306: wrong number or types of arguments in call to 'GETNUMELEM'

       

      I tried other functions including user functions and always get the same result.  If I get ornery and try to force the issue via

       

         QUERY="WHERE SDO_UTIL.GETNUMELEM(CAST(ku$.shape AS SDO_GEOMETRY)) = 1"

       

         ORA-00932: inconsistent datatypes: expected - got BINARY

       

      Which seems to imply the type interface is just not available in datapump.  Perhaps by the time datapump gets the geometry its been crunched into some kind of binary representation?

       

      So my guess is that we just can't do this.  Anyone else know more?  Am I missing something simple?

       

      Cheers,

       

      Paul

       

      p.s. I trolled through metalink for anything on the matter and found nothing.