Re: Oracle Spatial SDO Aggregate Union fails with ORA-29913 ODCIAGGREGATEMERGE

1 - I have to get an GUID for the union of the GEOMETRIE, The inner statment contains indeed too much columns, but i'm used to work from inside out. This means that i want to have good debug information, before i start a level higher.

2 - When i perform the query:
SQL> SELECT SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE FROM DGDTW_TOPOGRAFIE;
SELECT SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE FROM DGDTW_TOPOGRAFIE
*
FOUT in regel 1:
.ORA-03113: Einde-van-bestand op communicatiekanaal

On Jun 12, 11:10 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "Eduard Witteveen" <eywittev..._at_gmail.com> schreef in berichtnews:f6a38868-94f7-42f8-bbc7-6fad5dd6e172_at_2g2000hsn.googlegroups.com...
>
>
>
> > Hello,
>
> > I have a (application-)database which is filled with polygons, also
> > there is a administrative coupling on a guid. The guid is unique for
> > the administative part, but can contain more than 1 polygon. For this
> > reason i want to union the polygons, so i have 1 geometry for 1 guid.
> > Well, i managed to do this trick on the table that contains the point
> > information, however doing this on the table with the polygons gives
> > met the following error: ORA-29913 : blabla error blabla
> > ODCIAGGREGATEMERGE
> > This error is generated by the function: SDO_AGGR_UNION
>
> > I also tried to put filtering on the input of the function, but it
> > doesnt seem to make any difference(see query 3).
>
> > Can somebody help me how i can get a query / view with the information
> > guid + geometry?
>
> > Eduard Witteveen
>
> > ======================================================================
> > Below are the queries:
> > 1 - The version
> > 2 - The query i want to execute
> > 3 - The query with added checks for the SDO_AGGR_UNION
> > ======================================================================
>
> > SQL*Plus: Release 9.2.0.1.0 - Production on Do Jun 12 10:04:26 2008
>
> > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> > Voer wachtwoord in:
>
> > Verbonden met:
> > Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
> > With the Partitioning, OLAP and Oracle Data Mining options
> > JServer Release 9.2.0.7.0 - Production
>
> > SQL> select * from v$version
> > 2 where banner like 'Oracle%';
>
> > BANNER
> > ----------------------------------------------------------------
> > Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
>
> > SQL> SELECT
> > 2 DISTINCT(GUID),
> > 3 COUNT(GEOMETRIE) AS AANTAL,
> > 4 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE
> > 5 FROM
> > 6 (
> > 7 SELECT
> > 8 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,
> > 9 MULTI.PARENT AS PARENTGUID,
> > 10 GUIDS.ID AS CHILDGUID,
> > 11 TOPO.ID,
> > 12 TOPO.GEOMETRIE AS GEOMETRIE
> > 13 FROM DGDTW_TOPOGRAFIE TOPO
> > 14 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
> > 15 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
> > 16 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI
> > 17 ON MULTI.CHILD = GUIDS.ID
> > 18 AND ARCHIVE IS NULL
> > 19 WHERE TOPO.VERVAL IS NULL
> > 20 AND NOT TOPO.PARAMETERS = 515
> > 21 ORDER BY GUID
> > 22 ) LINES
> > 23 GROUP BY GUID;
> > SELECT
> > *
> > FOUT in regel 1:
> > .ORA-29913: Er is een fout opgetreden bij het uitvoeren van de
> > toelichting
> > ODCIAGGREGATEMERGE.
>
> > SQL> SELECT
> > 2 DISTINCT(GUID),
> > 3 COUNT(GEOMETRIE) AS AANTAL,
> > 4 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE
> > 5 FROM
> > 6 (
> > 7 SELECT
> > 8 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID,
> > 9 MULTI.PARENT AS PARENTGUID,
> > 10 GUIDS.ID AS CHILDGUID,
> > 11 TOPO.ID,
> > 12 TOPO.GEOMETRIE AS GEOMETRIE
> > 13 FROM DGDTW_TOPOGRAFIE TOPO
> > 14 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
> > 15 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
> > 16 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI
> > 17 ON MULTI.CHILD = GUIDS.ID
> > 18 AND ARCHIVE IS NULL
> > 19 WHERE TOPO.VERVAL IS NULL
> > 20 AND NOT TOPO.PARAMETERS = 515
> > 21 -- check the input for the SDO_AGGR_UNION
> > 22 AND TOPO.GEOMETRIE.SDO_GTYPE = 2003
> > 23 AND SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRIE, 0.001) = 'TRUE'
> > 24 ORDER BY GUID
> > 25 ) LINES
> > 26 GROUP BY GUID;
> > SELECT
> > *
> > FOUT in regel 1:
> > .ORA-29913: Er is een fout opgetreden bij het uitvoeren van de
> > toelichting
> > ODCIAGGREGATEMERGE.
>
> > SQL>
>
> Maybe I am not seeing things right here, but why are you selecting all those
> columns in your inner query when you only use the GEOMETRIE column on the
> outside? Why is the order by clause in the inner query?
>
> Did you try to call SDO_AGGR_UNION on some of the geometries without the
> complex query?
>
> Shakespeare
Received on Thu Jun 12 2008 - 07:33:12 CDT