SDE_MERGE Download page

Scroll to the bottom for a zip. Header:

create or replace type SDE_MERGE_i as object
(
  geom 	mdsys.sdo_geometry,

  STATIC FUNCTION ODCIAggregateInitialize
  ( sctx IN OUT SDE_MERGE_i
  ) RETURN number,

  MEMBER FUNCTION ODCIAggregateIterate
  (   self IN OUT SDE_MERGE_i
    , item IN mdsys.sdo_geometry
  )	RETURN number,

  MEMBER FUNCTION ODCIAggregateTerminate
  (   self IN 	  SDE_MERGE_i
    , ret  OUT    mdsys.sdo_geometry
    , flgs IN     number
  ) RETURN number,

  MEMBER FUNCTION ODCIAggregateMerge
  (   self IN OUT SDE_MERGE_i
    , ctx2 IN     SDE_MERGE_i
  ) RETURN number
);
/

Body:

CREATE OR REPLACE TYPE BODY SDE_MERGE_i
IS
    STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT SDE_MERGE_i)
    	return number
    IS
    BEGIN
        sctx := SDE_MERGE_i( geom => SDO_GEOMETRY( null, NULL, NULL, null, null) );
		return( ODCIConst.Success);
    END;

    MEMBER FUNCTION ODCIAggregateIterate
    (
    	self IN OUT SDE_MERGE_i
      , item IN 	mdsys.sdo_geometry
    ) return number
    IS
    BEGIN
        /* --- First time we get here we just copy item to self (20170706 SDE) */

        if( self.geom.SDO_ORDINATES is null)
        then
       		SELF.geom := item;
        	return( ODCIConst.Success);
        end if;

		SELF.geom := sdo_util.Append ( SELF.geom, item);
		return( ODCIConst.Success);
    END;

    MEMBER FUNCTION ODCIAggregateTerminate
    (
    	self 	IN  SDE_MERGE_i
      , ret  	OUT mdsys.sdo_geometry
      , flgs 	IN 	number
    ) return number
    IS
		cC0		CONSTANT int := 0;
        cC1		CONSTANT int := 1;
        cC2		CONSTANT int := 2;
        cC3		CONSTANT int := 3;
        cC4		CONSTANT int := 4;

    	tBuf 	varchar( 1000);

		TYPE t_Numbers IS TABLE OF integer 	INDEX BY BINARY_INTEGER;

        TYPE t_geomInfoRecord IS RECORD
        (
            	geom			mdsys.sdo_geometry
            ,	hash			int
			,	Prev			int
			,	prevItems		t_Numbers
			,	Next			int
			,	nextItems		t_Numbers
			,	isStartingGeo	int
        );

  		TYPE t_geomInfo 	IS TABLE OF t_geomInfoRecord 	INDEX BY BINARY_INTEGER;
		geomInfo 			t_geomInfo;

        /**
         * @name getHashCode
         * @author S.Deckers
         * @date 26-07-2017
         * @description
         */

        FUNCTION GETHASHCODE( theGeom mdsys.sdo_geometry)
            return int
        AS
            p1 		mdsys.sdo_geometry;
            p2 		mdsys.sdo_geometry;
            hash	int;
        BEGIN
            p1 := SDO_LRS.GEOM_SEGMENT_START_PT	( theGeom);
            p2 := SDO_LRS.GEOM_SEGMENT_END_PT	( theGeom);

            hash := p1.sdo_ordinates( 1) + p1.sdo_ordinates( 2) + p2.sdo_ordinates( 1) + p2.sdo_ordinates( 2);
            return( hash);
        END;

		/**
		 * @name Swap
		 * @author S.Deckers
		 * @date 08-AUG-2017
		 * @description Swaps geometry
		 */

		FUNCTION Swap( g mdsys.sdo_geometry) return mdsys.sdo_geometry
		as
			swapped	mdsys.sdo_geometry := new mdsys.sdo_geometry( SDO_GTYPE     => g.SDO_GTYPE
																, SDO_SRID      => g.SDO_SRID
																, SDO_POINT     => g.SDO_POINT
																, SDO_ELEM_INFO => g.SDO_ELEM_INFO
																, SDO_ORDINATES => new mdsys.SDO_ORDINATE_ARRAY( ) );
			k 		int := 1;
			dims	int := 3;
			cnt		int;
			x		int;
			y		int;
			z		int;
		begin

			cnt	:= g.SDO_ORDINATES.COUNT / dims;

			for i in REVERSE 1..cnt
			loop
				x := (i-1)*dims+1;
				y := (i-1)*dims+2;
				z := (i-1)*dims+3;

				swapped.sdo_ordinates.Extend( dims);

				swapped.sdo_ordinates( k) := g.sdo_ordinates( x);
				k:=k+1;

				swapped.sdo_ordinates( k) := g.sdo_ordinates( y);
				k:=k+1;

				swapped.sdo_ordinates( k) := g.sdo_ordinates( z);
				k:=k+1;
			end loop;

			return( swapped);
		END;

		/**
		 * @name getStartingGeo
		 * @author S.Deckers
		 * @date 08-AUG-2017
		 * @description return index starting geo
		 */

		FUNCTION getStartingGeo return int
		AS
		BEGIN
			for i in 1..geomInfo.COUNT
			loop

				if(	geomInfo( i).isStartingGeo = 1) then
					return( i);
				end if;

			end loop;

			raise_application_error( -20101, 'getStartingGeo:Unsupported situation');
		END;

        /**
         * @name Sort
         * @author S.Deckers
         * @date 09-08-2017
         * @description Sorts geometries and swaps direction if needed
         */

    	PROCEDURE Sort
        AS
			n			int := 1; -- Loop counter
			i 			int;
			tNext 		int;
			tNextNext	int;
			tVal		int;

			/**
			 * @name simplifyStructure
			 * @author S.Deckers
			 * @date 01-Sept-2017
			 * @description The initial structure contains geometries pointing to connected geometries. If a geometry is connected
			 * to multiple geometries, its previousItems/nextItems have more then 1 member. If this is the case, the set of
			 * geometries cannot be merged. At this point in code, only single connections exist and we can simplify the structure
			 * to make manipulaton of it easier. Since the previous-/nextItems collection only contain a single item, we store it
			 * in Next/Prev members
			 */

			PROCEDURE simplifyStructure
			AS
			BEGIN
				for x in 1..geomInfo.COUNT
				loop
					/* --- Previous items (20170901 SDE) */

					if( geomInfo( x).prevItems.COUNT > 1) then
						tBuf := utl_lms.format_message( 'simplifyStructure:prevItems contains %s items',
                            TO_CHAR( geomInfo( x).prevItems.COUNT));
						raise_application_error( -20102, tBuf);
					end if;

					if( geomInfo( x).prevItems.COUNT = 0) then
						geomInfo( x).Prev := 0;
					else
						geomInfo( x).Prev := geomInfo( x).prevItems( 1);
					end if;

					/* --- Next items (20170901 SDE) */

					if( geomInfo( x).nextItems.COUNT > 1) then
						tBuf := utl_lms.format_message( 'simplifyStructure:nextItems contains %s items',
                            TO_CHAR( geomInfo( x).nextItems.COUNT));
						raise_application_error( -20103, tBuf);
					end if;

					if( geomInfo( x).nextItems.COUNT = 0) then
						geomInfo( x).Next := 0;
					else
						geomInfo( x).Next := geomInfo( x).nextItems( 1);
					end if;
				end loop;

				/* --- At this point the prev-/nextItems collections have no more use (20170901 SDE)*/

				for x in 1..geomInfo.COUNT
				loop
					geomInfo( x).prevItems.Delete;
					geomInfo( x).nextItems.Delete;
				end loop;
			END;

			/**
			 * @name setStartingGeo
			 * @author S.Deckers
			 * @date 01-Sept-2017
			 * @description Set starting geometry for creating a sorted collection of geometries
			 */

			PROCEDURE setStartingGeo
			AS
			BEGIN
				for x in 1..geomInfo.COUNT
				loop

					if( geomInfo( x).Prev=0) then
						geomInfo( x).isStartingGeo := 1;
						exit;
					end if;

					if( geomInfo( x).Next=0) then
						geomInfo( x).isStartingGeo := 1;
						exit;
					end if;

				end loop;
			END;

        BEGIN
			simplifyStructure 	( );
			setStartingGeo		( );

			i := getStartingGeo();

			/* --- Begin at Starting geo and get next until all geometries are retrieved (20170808 SDE) */

			LOOP
				tNext := geomInfo( i).Next;

				if( n = 1)
				then
					-- Swap first item
					if( tNext = 0) then
						geomInfo( i).geom := Swap( geomInfo( i).geom);
						tVal := geomInfo( i).Next;
						geomInfo( i).Next := geomInfo( i).Prev;
						geomInfo( i).Prev := tVal;

						tNext := geomInfo( i).Next;
						n := n+1;
					end if;
				else
					-- End of list
					if( tNext = 0) then
						return;
					end if;
				end if;

				tNextNext := geomInfo( tNext).Next;
				if( i = tNextNext) then
					-- Swap item
					geomInfo( tNext).geom := Swap( geomInfo( tNext).geom);
					tVal := geomInfo( tNext).Next;
					geomInfo( tNext).Next := geomInfo( tNext).Prev;
					geomInfo( tNext).Prev := tVal;
				end if;

				i := geomInfo( i).Next;
				n := n+1;
			END LOOP;
        END;

		/**
		 * @name dumpOords
		 * @author S.Deckers
		 * @date 08-08-2017
		 * @description
		 */

		PROCEDURE dumpOords( sdo_ordinates SDO_ORDINATE_ARRAY)
		as
		begin
			for i in 1..sdo_ordinates.COUNT
			loop
				tBuf := utl_lms.format_message( 'i=%s v=%s', TO_CHAR( i), TO_CHAR( sdo_ordinates( i)));
				dbms_output.put_line( tBuf);
			end loop;
		END;

		/**
		 * @name dumpCoordinates
		 * @author S.Deckers
		 * @date 08-08-2017
		 * @description
		 */

		PROCEDURE dumpCoordinates( sdo_ordinates SDO_ORDINATE_ARRAY)
		as
			x 		number;
			y 		number;
			z 		number;
			dims 	number := 3;
		BEGIN
			for i in 1..sdo_ordinates.COUNT / dims
			loop
				x := sdo_ordinates( i*3-2);
				y := sdo_ordinates( i*3-1);
				z := sdo_ordinates( i*3);
				tBuf := utl_lms.format_message( 'i=%s, x=%s, y=%s, z=%s', TO_CHAR( i), TO_CHAR( x), TO_CHAR( y), TO_CHAR( z) );
				dbms_output.put_line( tBuf);
			end loop;
		END;

		/**
		 * @name dumpElemInfo
		 * @author S.Deckers
		 * @date 08-08-2017
		 * @description
		 */

		procedure dumpElemInfo( sdo_elem_info SDO_ELEM_INFO_ARRAY)
		as
		begin
			for i in 1..sdo_elem_info.COUNT
			loop
				tBuf := utl_lms.format_message( 'i=%s v=%s', TO_CHAR( i), TO_CHAR( sdo_elem_info( i)));
				dbms_output.put_line( tBuf);
			end loop;
		end;

		/**
		 * @name getPrevUnConnectedCount
		 * @description return # of geometries connected on 'Prev'-side
		 */

		function getPrevConnectedCount( pos int) return int
		as
			tResult int := 0;
			tVal	int := 0;
		begin
			for n in 1..geomInfo( pos).prevItems.COUNT
			loop
				tVal := geomInfo( pos).prevItems( n);
				tResult := tResult+1;
			end loop;

			return( tResult);
		end;

		/**
		 * @name getNextConnectedCount
		 * @description return # of geometries connected on 'Next'-side
		 */

		function getNextConnectedCount( pos int) return int
		as
			tResult int := 0;
			tVal	int := 0;
		begin
			for n in 1..geomInfo( pos).nextItems.COUNT
			loop
				tResult := tResult+1;
			end loop;

			return( tResult);
		end;

		/**
		 * @name dumpUnSimplified
		 * @purpose dumpUnSimplified structure
		 */

		PROCEDURE DumpSingleConnected
		AS
			res1 		int;
			res2 		int;
		BEGIN
			dbms_output.put_line( 'dumpSingleConnected');

			for i in 1..geomInfo.COUNT
			loop
				res1 := getPrevConnectedCount( pos => i);
				res2 := getNextConnectedCount( pos => i);

				if( res1 != 0 and res2 != 0)
				then
					tBuf := utl_lms.format_message( 'i=%s, hash=%s, nextCount=%s, prevCount=%s',
                     TO_CHAR( i), TO_CHAR( geomInfo( i).hash), TO_CHAR( res1), TO_CHAR( res2));
					dbms_output.put_line( tBuf);
					continue;
				end if;

				tBuf := utl_lms.format_message( 'i=%s, hash=%s', TO_CHAR( i), TO_CHAR( geomInfo( i).hash));
				dbms_output.put_line( tBuf);
			end loop;
		END;

        /**
         * @name Dump GeoInfo structure
         * @author S.Deckers
         * @date 16-07-2017
         * @description
         */

    	PROCEDURE Dump
        AS
			tPrevUnConnectedCount int := 0;
			tNextUnConnectedCount int := 0;

			/**
			 * @name collectionIsSimplified
			 * @purpose If prev-/nextItems not exists the collection is simplified
			 */

			FUNCTION collectionIsSimplified return BOOLEAN
			AS
			BEGIN
				for i in 1..geomInfo.COUNT
				loop
					if( geomInfo( i).nextItems.COUNT > 0) then
						return( FALSE);
					end if;

					if( geomInfo( i).prevItems.COUNT > 0) then
						return( FALSE);
					end if;
				end loop;

				return( TRUE);
			END;

			/**
			 * @name DumpPrev
			 * @purpose Dump previous members
			 */

			PROCEDURE DumpPrev( pos int)
			AS
				tVal int;
			BEGIN
				tBuf := utl_lms.format_message( '  DumpPrev:%s items for pos:%s', TO_CHAR( geomInfo( pos).prevItems.COUNT), TO_CHAR( pos));
				dbms_output.put_line( tBuf);

				for n in 1..geomInfo( pos).prevItems.COUNT
				loop
					tVal := geomInfo( pos).prevItems( n);
					tBuf := utl_lms.format_message( '    n=%s, Prev=%s', TO_CHAR( n), TO_CHAR( tVal));
					dbms_output.put_line( tBuf);
				end loop;
			END;

			/**
			 * @name DumpNext
			 * @purpose Dump next members
			 */

			PROCEDURE DumpNext( pos int)
			AS
				tVal int;
			BEGIN
				tBuf := utl_lms.format_message( '  DumpNext:%s items for pos:%s', TO_CHAR( geomInfo( pos).nextItems.COUNT), TO_CHAR( pos));
				dbms_output.put_line( tBuf);

				for n in 1..geomInfo( pos).nextItems.COUNT
				loop
					tVal := geomInfo( pos).nextItems( n);
					tBuf := utl_lms.format_message( '    n=%s, Next_=%s', TO_CHAR( n), TO_CHAR( tVal));
					dbms_output.put_line( tBuf);
				end loop;
			END;

			/**
			 * @name dumpUnSimplified
			 * @purpose dumpUnSimplified structure
			 */

			PROCEDURE dumpUnSimplified
			AS
			BEGIN
				dbms_output.put_line( 'dumpUnSimplified');

			   	for i in 1..geomInfo.COUNT
				loop
					tPrevUnConnectedCount := getPrevConnectedCount( pos => i);
					tNextUnConnectedCount := getNextConnectedCount( pos => i);

					tBuf :=         utl_lms.format_message( 'i=%s', 				TO_CHAR( i));
					tBuf := tBuf || utl_lms.format_message( ', prevCount=%s', 		TO_CHAR( tPrevUnConnectedCount));
					tBuf := tBuf || utl_lms.format_message( ', nextCount=%s', 		TO_CHAR( tNextUnConnectedCount));
					tBuf := tBuf || utl_lms.format_message( ', isStartingGeo=%s', 	TO_CHAR( geomInfo( i).isStartingGeo));

					dbms_output.put_line( tBuf);

					DumpPrev( pos => i);
					DumpNext( pos => i);
				end loop;
			END;

			/**
			 * @name dumpSimplified
			 * @purpose dumpSimplified structure
			 */

			PROCEDURE dumpSimplified
			AS
			BEGIN
				dbms_output.put_line( 'Simplified');

				for i in 1..geomInfo.COUNT
				loop
					tBuf :=         utl_lms.format_message( 'i=%s', 				TO_CHAR( i));
					tBuf := tBuf || utl_lms.format_message( ', hash=%s', 			TO_CHAR( geomInfo( i).hash));
					tBuf := tBuf || utl_lms.format_message( ', next=%s', 			TO_CHAR( geomInfo( i).next));
					tBuf := tBuf || utl_lms.format_message( ', previous=%s', 		TO_CHAR( geomInfo( i).Prev));
					tBuf := tBuf || utl_lms.format_message( ', isStartingGeo=%s', 	TO_CHAR( geomInfo( i).isStartingGeo));
					dbms_output.put_line( tBuf);
				end loop;
			END;

        BEGIN
 			if( collectionIsSimplified() = TRUE) then
				dumpSimplified();
				return;
			end if;

			dumpUnSimplified( );
        END;

		/**
		 * @name getConnectionInfo
		 * @description Given 2 connected geometries determine how they are connected, t.i. start connected to endpoint etc..
		 */

		FUNCTION getConnectionInfo
		(
			a  	in 	mdsys.sdo_geometry
		  , b	in 	mdsys.sdo_geometry
		)	return int
		AS
			p1 		               mdsys.sdo_geometry;
			p2 		               mdsys.sdo_geometry;
			tResult	               varchar( 20);
			cTol constant number := 0.2;
		BEGIN

			/* --- C1: A o----o o----o B  A drawn left to right, B left to right -> endpoint A connected to startpoint B (20170726 SDE)
						 1 -> 2 1 -> 2    */

			select SDO_LRS.GEOM_SEGMENT_END_PT( a) 		into p1 from dual;
			select SDO_LRS.GEOM_SEGMENT_START_PT( b) 	into p2 from dual;

			select sdo_geom.relate( SDO_CS.MAKE_2D( p1), 'DETERMINE', SDO_CS.MAKE_2D( p2), cTol) into tResult from dual;

			if( tResult = 'EQUAL')
			then
				return( cC1);
			end if;

			/* --- C2: A o----o o----o B  A drawn right to left, B right to left -> startpoint A connected to endpoint B (20170726 SDE)
						 2 <- 1 2 <- 1    */

			select SDO_LRS.GEOM_SEGMENT_START_PT( A) 	into p1 from dual;
			select SDO_LRS.GEOM_SEGMENT_END_PT( b) 		into p2 from dual;

			select sdo_geom.relate( SDO_CS.MAKE_2D( p1), 'DETERMINE', SDO_CS.MAKE_2D( p2), cTol) into tResult from dual;

			if( tResult = 'EQUAL')
			then
				return( cC2);
			end if;

			/* --- C3: A o----o o----o B  A drawn left to right, B right to left -> Endpoints connected (20170726 SDE)
						 1 -> 2 2 <- 1    */

			select SDO_LRS.GEOM_SEGMENT_END_PT( A) into p1 from dual;
			select SDO_LRS.GEOM_SEGMENT_END_PT( b) into p2 from dual;

			select sdo_geom.relate( SDO_CS.MAKE_2D( p1), 'DETERMINE', SDO_CS.MAKE_2D( p2), cTol) into tResult from dual;

			if( tResult = 'EQUAL')
			then
				return( cC3);
			end if;

			/* --- C4: A o----o o----o B  A drawn right to left, B left to right -> Startpoints connected (20170726 SDE)
						 2 <- 1 1 -> 2    */

			select SDO_LRS.GEOM_SEGMENT_START_PT( A) into p1 from dual;
			select SDO_LRS.GEOM_SEGMENT_START_PT( b) into p2 from dual;

			select sdo_geom.relate( SDO_CS.MAKE_2D( p1), 'DETERMINE', SDO_CS.MAKE_2D( p2), cTol) into tResult from dual;

			if( tResult = 'EQUAL')
			then
				return( cC4);
			end if;

			/* --- No connection (20170929 SDE) */

			return( cC0);
		END;

		/**
		 * @name setConnectionInfo
		 * @description Fill structure with info how geometries are connected
		 */

		PROCEDURE setConnectionInfo
		as
			k		int := 1;
			theCase	int;
		BEGIN

			for i in 1..geomInfo.COUNT
			loop

				for n in 1..geomInfo.COUNT
				loop

					/* --- Skip self (20170804 SDE) */

					if( n = i) then
						continue;
					end if;

					/* --- Only continue if we have connected geometries (20170804 SDE) */

					if( getConnectionInfo( a=>geominfo( i).geom, b=>geominfo( n).geom) = cC0 ) then
						continue;
					end if;

					/* --- Get connectioninfo (20170804 SDE) */

					theCase := getConnectionInfo( A=>geomInfo( i).geom, b=>geomInfo( n).geom);

					CASE( theCase)

						/* --- C1: A o----o o----o B  A drawn left to right, B left to right -> endpoint A connected to startpoint B (20170726 SDE)
									 1 -> 2 1 -> 2    */

						WHEN( cC1) THEN
							BEGIN
								geomInfo( i).nextItems( geomInfo( i).nextItems.COUNT+1) := n;
							END;

						/* --- C2: A o----o o----o B  A drawn right to left, B right to left -> startpoint A connected to endpoint B (20170726 SDE)
									 2 <- 1 2 <- 1    */

						WHEN( cC2) then
							BEGIN
								geomInfo( i).prevItems( geomInfo( i).prevItems.COUNT+1) := n;
							END;

						/* --- C3: A o----o o----o B  A drawn left to right, B right to left -> Endpoints connected (20170726 SDE)
									 1 -> 2 2 <- 1    */

						WHEN( cC3) THEN
							BEGIN
								geomInfo( i).nextItems( geomInfo( i).nextItems.COUNT+1) := n;
							END;

						/* --- C4: A o----o o----o B  A drawn right to left, B left to right -> Startpoints connected (20170726 SDE)
									 2 <- 1 1 -> 2    */

						WHEN( cC4) THEN
							BEGIN
								geomInfo( i).prevItems( geomInfo( i).prevItems.COUNT+1) := n;
							END;

						/* --- Can't handle this (20170726 SDE) */

					END CASE;

					k := k + 1;
				end loop;

			end loop;
		END;

		/**
		 * @name multipleConnectedGeometryCount
		 * @description return # of geometries with more then 2 connections on Prev/Next. If these geometries
		 * exists a Merged geometry cannot be created :
				   1       2     3
				o-->--o o-->--o-->--o
						 \    |
						  \	  |
						5  \  | 4
							\ |
							 \|
							  o
		 */

		FUNCTION multipleConnectedGeometryCount return integer
		AS
			prev_cnt	integer := 0;
			next_cnt	integer := 0;
			theCount 	integer := 0;
			geoCount	integer := 0;
		BEGIN
			for i in 1..geomInfo.COUNT
			loop
				prev_cnt := getPrevConnectedCount( pos => i);

				if( prev_cnt > 1) then
					theCount := theCount + 1;
					geoCount := geoCount + 1;
					continue;
				end if;

				next_cnt := getNextConnectedCount( pos => i);

				if( next_cnt > 1) then
					theCount := theCount + 1;
					geoCount := geoCount + 1;
				end if;
			end loop;

			return( geoCount);
		END;

		/**
		 * @name getSingleConnectedCount
		 * @description return # of geometries connected on a single side. We need 2 for sorting, the start- and end geometry
		 */

		FUNCTION getSingleConnectedCount return integer
		as
			res1		integer := 0;
			res2		integer := 0;
			theCount 	integer := 0;
		BEGIN

			for i in 1..geomInfo.COUNT
			loop
				res1 := getPrevConnectedCount( pos => i);

				if( res1 = 0) then
					theCount := theCount + 1;
				end if;

				res2 := getNextConnectedCount( pos => i);

				if( res2 = 0) then
					theCount := theCount + 1;
				end if;
			end loop;

			return( theCount);
		END;

		/**
		 * @name fillTempStructure
		 * @description Fills temporary structure with geometries
		 */

		PROCEDURE fillTempStructure
		(
			theGeom		IN  mdsys.sdo_geometry
		,	geoCount	OUT integer
		)
		AS
			cnt				int;
			gtype			int := theGeom.sdo_gtype;
			tripletCount	int;
		BEGIN
			geoCount := 0;

			/* --- If our temp geometry is a linestring/curve it can only be a single element (20170808 SDE) */

			if( gtype = 3002) then
				tBuf := utl_lms.format_message( 'gtype=3002, single geometry encountered');
				dbms_output.put_line( tBuf);
				geoCount := 1;
				return;
			end if;

			cnt := theGeom.sdo_elem_info.COUNT;
			tripletCount := cnt / 3;

			DECLARE
				elemInfoCounter		int := 1;
				triplet				int := 0;
				sdo_starting_offset	number;
				sdo_etype			number;
				sdo_interpretation 	number;
				tGeom				mdsys.sdo_geometry;
			BEGIN
				while elemInfoCounter < theGeom.sdo_elem_info.COUNT
				loop
					triplet := elemInfoCounter;

					if( triplet > 1) then
						triplet := (triplet - 1)/3 + 1;
					end if;

					-- get the sdo_elem_info elements (20170706 SDE)
					sdo_starting_offset := theGeom.sdo_elem_info( elemInfoCounter);
					sdo_etype 			:= theGeom.sdo_elem_info( elemInfoCounter+1);
					sdo_interpretation 	:= theGeom.sdo_elem_info( elemInfoCounter+2);

					/* --- If we encounter a Compound line the # of geometries making up this linestring is at the second position.*/

					if( sdo_etype = 4) then
						DECLARE
							geoCount2	   	number := sdo_interpretation; -- # geometries in compound linestring
							firstOffset		number := elemInfoCounter+3;
							lastOffset		number := (geoCount2-1)*3+firstOffset;
							firstValue		number := theGeom.sdo_elem_info( firstOffset);
							lastValue		number := theGeom.sdo_elem_info( lastOffset);
						BEGIN
							elemInfoCounter := lastOffset+3;
						END;

						geoCount := geoCount + 1;

						select SDO_UTIL.EXTRACT( theGeom, geoCount) into tGeom from dual;
						geomInfo( geoCount).geom 			:= tGeom;
						geomInfo( geoCount).isStartingGeo 	:= 0;
						geomInfo( geoCount).hash			:= GETHASHCODE( tGeom);

						continue;
					end if;

					-- Increase elemInfoCounter (20170706 SDE)
					elemInfoCounter := elemInfoCounter+3;
					geoCount := geoCount + 1;

					select SDO_UTIL.EXTRACT( theGeom, geoCount) into tGeom from dual;
					geomInfo( geoCount).geom 			:= tGeom;
					geomInfo( geoCount).isStartingGeo 	:= 0;
					geomInfo( geoCount).hash			:= GETHASHCODE( tGeom);
				end loop;
			end;
		END;

		/**
		 * @name collectionIs3D
		 * @description return TRUE if all the geometries are 3d
		 */

		FUNCTION collectionIs3D return boolean
		as
		BEGIN
		    for i in 1..geomInfo.COUNT
			loop
				if( geomInfo( i).geom.get_dims() = 2) then
					return( FALSE);
				end if;
			end loop;

			return( TRUE);
		END;

		/**
		 * @name Merge
		 * @author S.Deckers
		 * @date 09-Aug-2017
		 * @description Merge 2 geometries
		 */

		function Merge( geom1 mdsys.sdo_geometry, geom2 mdsys.sdo_geometry)
			return mdsys.sdo_geometry
		as
			v_o1		number; -- sdo_starting_offset
			v_e1		number; -- sdo_etype
			v_i1		number;	-- sdo_interpretation
			v_o2		number;
			v_e2		number;
			v_i2		number;

			elemInfo	mdsys.sdo_elem_info_array := new mdsys.sdo_elem_info_array	( );
			ordinates 	mdsys.sdo_ordinate_array  := new mdsys.sdo_ordinate_array	( );
			merged		mdsys.sdo_geometry;

			/**
			 * @name MergeOrdinates
			 * @author S.Deckers
			 * @date 01-Sept-2017
			 * @description
			 */

			function mergeOrdinates( n1 mdsys.sdo_ordinate_array, n2 mdsys.sdo_ordinate_array)
				return mdsys.sdo_ordinate_array
			as
				n3 mdsys.sdo_ordinate_array := new mdsys.sdo_ordinate_array( );
			begin
				for i in 1..n1.COUNT - 3
				loop
					n3.extend();
					n3( n3.COUNT) := n1( i);
				end loop;

				for i in 1..n2.COUNT
				loop
					n3.extend();
					n3( n3.COUNT) := n2( i);
				end loop;

				return( n3);
			end;

			/**
			 * @name isSupportedGeometry
			 * @author S.Deckers
			 * @date 01-Sept-2017
			 * @return
			 *  o TRUE supported geometry
			 *  o FALSE unsupported geometry
			 */

			function isSupportedGeometry( etype int, interpretation int) return boolean
			as
			begin

				/* --- Line */
				if( etype = 2 and interpretation = 1 ) then
					return( TRUE);
				end if;

				/* --- Arc */
				if( etype = 2 and interpretation = 2 ) then
					return( TRUE);
				end if;

				/* --- Compound linestring  */
				if( etype = 4 ) then
					return( TRUE);
				end if;

				tBuf := utl_lms.format_message( 'Unsupported:etype=%s, interpretation=%s', TO_CHAR( etype), TO_CHAR( interpretation));
				dbms_output.put_line( tBuf);

				return( FALSE);
			end;

			/**
			 * @name getMergedElemInfo
			 * @author S.Deckers
			 * @date 01-Sept-2017
			 * @description get resulting elemInfo for 2 merging eleminfos. We need to support for the following merge-cases :
			 * 	  #   elem1  elem2  merged
			 * 	  --  -----  -----  ------
			 * 	   1  line   line   CS
			 * 	   2  line   CS     CS
			 * 	   3  CS     line   CS
			 * 	   4  Arc    Line   CS
			 * 	   5  Line   Arc    CS
			 * 	   6  Arc    Arc    CS
			 * 	   7  CS     CS     CS
			 * 	   8  CS     Arc    CS
			 * 	   9  Arc    CS		CS
			 */

			procedure getMergedElemInfo
			(
				e1	in  mdsys.sdo_elem_info_array
			  , e2	in  mdsys.sdo_elem_info_array
			  , e3	out mdsys.sdo_elem_info_array
			)
			AS
				etype1 				int := 0;
				interpr1 			int := 0;
				etype2 				int := 0;
				interpr2 			int := 0;
			BEGIN
				/* --- Line + Line -> Line (20170901 SDE) */

				etype1 		:= e1( 2);
				interpr1 	:= e1( 3);
				etype2 		:= e2( 2);
				interpr2 	:= e2( 3);

				if( (etype1 = 2 and interpr1 = 1) AND (etype2 = 2 and interpr2 = 1) ) then
					e3   	:= new mdsys.sdo_elem_info_array( );
					e3.extend( 3);
					e3( 1) := 1;	-- offset=1
					e3( 2) := 2; 	-- etype=2
					e3( 3) := 1; 	-- interpr= 1
					return;
				end if;

				/* --- Line + Compound linestring -> Compound linestring (20170901 SDE) */

				if( (etype1 = 2 and interpr1 = 1) AND (etype2 = 4)) then
					DECLARE
						elemCount 	int := e2( 3) + 1;
						offSet		int := geom1.sdo_ordinates.COUNT() - 3;
						newSize		int := e1.COUNT() + e2.COUNT();
						n			int;
					BEGIN
						e3 := new mdsys.sdo_elem_info_array( );
						e3.extend( newSize);

						e3( 1) := 1;
						e3( 2) := 4;
						e3( 3) := elemCount;

						-- the Line (20170901 SDE)
						e3( 4) := 1;
						e3( 5) := 2;
						e3( 6) := 1;

						-- Copy Compound Linestring (20170901 SDE)
						n := 7;

						for i in 4..e2.COUNT() loop

							if (MOD( n-1, 3) = 0 ) then
								e3( n) := e2( i) + offSet;
								n := n + 1;
								continue;
							end if;

							e3( n) := e2( i);
							n := n + 1;
						end loop;
					END;

					return;
				end if;

				/* --- Compound linestring + Line -> Compound linestring (20170901 SDE) */

				if( (etype1 = 4) AND (etype2 = 2 and interpr2 = 1) ) then
					e3 := geom1.sdo_elem_info;
					e3.extend( 3);

					e3( 3) := geom1.sdo_elem_info( 3) + 1; --- Increase # of elements with 1

					e3( e3.COUNT() - 2 ) := geom1.sdo_ordinates.COUNT() - 2; 	--- Set new offset
					e3( e3.COUNT() - 1 ) := 2;  --- It is a line
					e3( e3.COUNT()) 	 := 1;
					return;
				end if;

				/* --- ARC + LINE -> Compound linestring (20170912 SDE)
						 type    elem_info			         ordinates
						 -----   -------------------          -----------------------------------
						 Arc           1,2,2                           1,10,0, 10,8,0, 15,10,0
						 line                 1,2,                                     15,10,0, 20,10,0
						 merged  1,4,2 1,2,2, 7,2,1           1,10,0, 10,8,0, 15,10,0, 20,10,0
				 */

				if( (etype1 = 2 and interpr1 = 2) AND (etype2 = 2 and interpr2 = 1) ) then
					e3 := new mdsys.sdo_elem_info_array( );
					e3.extend( 9);

					e3( 1) := 1;
					e3( 2) := 4;
					e3( 3) := 2;

					-- Arc
					e3( 4) := 1;
					e3( 5) := 2;
					e3( 6) := 2;

					-- Linestring
					e3( 7) := 7;
					e3( 8) := 2;
					e3( 9) := 1;

					return;
				end if;

				/* --- LINE + ARC -> Compound linestring (20170912 SDE) */

				if( (etype1 = 2 and interpr1 = 1) AND (etype2 = 2 and interpr2 = 2) ) then
					e3 := new mdsys.sdo_elem_info_array( );
					e3.extend( 9);

					e3( 1) := 1;
					e3( 2) := 4;
					e3( 3) := 2;

					-- Linestring
					e3( 4) := 1;
					e3( 5) := 2;
					e3( 6) := 1;

					-- Arc
					e3( 7) := geom1.sdo_ordinates.COUNT() - 2;
					e3( 8) := 2;
					e3( 9) := 2;

					return;
				end if;

				/*	--- ARC + ARC -> Compound linestring (20170912 SDE) */

				if( (etype1 = 2 and interpr1 = 2) AND (etype2 = 2 and interpr2 = 2) ) then
					e3 := new mdsys.sdo_elem_info_array( );
					e3.extend( 9);

					e3( 1) := 1;
					e3( 2) := 4;
					e3( 3) := 2;

					-- Arc1
					e3( 4) := 1;
					e3( 5) := 2;
					e3( 6) := 2;

					-- Arc2
					e3( 7) := geom1.sdo_ordinates.COUNT() - 2;
					e3( 8) := 2;
					e3( 9) := 2;

					return;
				end if;

				/* --- CS + CS -> CS (20170901 SDE) */

				if( (etype1 = 4) AND (etype2 = 4) ) then
					DECLARE
						elemCount 	int := e1( 3) + e2 ( 3);
						offSet		int := geom1.sdo_ordinates.COUNT() - 3;
						newSize		int := e1.COUNT() + e2.COUNT() - 3;
						n			int;
					BEGIN
						e3 := new mdsys.sdo_elem_info_array( );
						e3.extend( newSize);

						e3( 1) := 1;
						e3( 2) := 4;
						e3( 3) := elemCount;

						for i in 4..e1.COUNT() loop
							e3( i) := e1( i);
							n := i;
						end loop;

						n:=n+1;
						for i in 4..e2.COUNT() loop

							-- Offset, we need to modify this (20170901 SDE)
							if( MOD( n-1, 3) = 0) then
								e3( n) := e2( i) + offSet;
								n:=n+1;
								continue;
							end if;

							-- The element, just copy it (20170901 SDE)
							e3( n) := e2( i);
							n := n + 1;
						end loop;
					END;

					return;
				end if;

				/* --- Compound linestring + Arc -> Compound linestring (20170901 SDE) */

				if( (etype1 = 4) AND (etype2 = 2 and interpr2 = 2) ) then
					e3 := geom1.sdo_elem_info;
					e3.extend( 3);

					e3( 3) := geom1.sdo_elem_info( 3) + 1; --- Increase # of elements with 1

					e3( e3.count() - 2 ) := geom1.sdo_ordinates.count() - 2; 	--- Set new offset
					e3( e3.count() - 1 ) := 2;  --- It is a Arc
					e3( e3.count()) 	 := 2;
					return;
				end if;

				/* --- Arc + Compound linestring -> Compound linestring (20170930 SDE) */

				if( (etype1 = 2 and interpr1 = 2) AND (etype2 = 4)) then
					DECLARE
						elemCount 	int := e2( 3) + 1;
						offSet		int := geom1.sdo_ordinates.count() - 3;
						newSize		int := e1.count() + e2.count();
						n			int;
					BEGIN
						e3 := new mdsys.sdo_elem_info_array( );
						e3.extend( newSize);

						e3( 1) := 1;
						e3( 2) := 4;
						e3( 3) := elemCount;

						-- the Arc (20170930 SDE)
						e3( 4) := 1;
						e3( 5) := 2;
						e3( 6) := 2;

						-- Copy Compound Linestring (20170901 SDE)

						n := 7;

						for i in 4..e2.count() loop
							if (MOD( n-1, 3) = 0 ) then
								e3( n) := e2( i) + offSet;
								n := n + 1;
								continue;
							end if;

							e3( n) := e2( i);
							n := n + 1;
						end loop;
					END;

					return;
				end if;

				/* --- Unsupported combination (20170901 SDE) */

				tBuf := utl_lms.format_message( 'Unsupported eleminfo merge operation:etype1=%s, interpr1=%s, etype2=%s, interpr2=%s',
                    TO_CHAR( etype1), TO_CHAR( interpr1), TO_CHAR( etype2), TO_CHAR( interpr2));
				dbms_output.put_line( tBuf);
				return;
			END;

		/* --- Merge Begin */
		BEGIN
			if( geom1 is null) then	return( geom2);	end if;
			if( geom2 is null) then	return( geom1);	end if;

			/* --- Geometries need to have the same gtype (20170808 SDE) */
			if( geom1.sdo_gtype != geom2.sdo_gtype) then
				tBuf := utl_lms.format_message( 'gtype not equal:%s != %s', TO_CHAR( geom1.sdo_gtype), TO_CHAR( geom2.sdo_gtype));
				dbms_output.put_line( tBuf);
				return( null);
			end if;

			/* --- If the dimension is specified it needs to be the same (20170808 SDE) */
			if( geom1.sdo_srid is null) then
				if( geom2.sdo_srid is not null) then
					tBuf := utl_lms.format_message( 'srid1 is null srid2 not');
					dbms_output.put_line( tBuf);
					return( null);
				end if;
			end if;

			if( geom2.sdo_srid is null) then
				if( geom1.sdo_srid is not null) then
					tBuf := utl_lms.format_message( 'srid2 is null srid1 not');
					dbms_output.put_line( tBuf);
					return( null);
				end if;
			end if;

			if( geom1.sdo_srid != geom2.sdo_srid) then
				tBuf := utl_lms.format_message( 'srid not equal:%s != %s', TO_CHAR( geom1.sdo_srid), TO_CHAR( geom2.sdo_srid));
				dbms_output.put_line( tBuf);
				return( null);
			end if;

			/* --- Merge it (20170808 SDE) */
			v_o1 := geom1.sdo_elem_info( 1);
			v_e1 := geom1.sdo_elem_info( 2);
			v_i1 := geom1.sdo_elem_info( 3);

			v_o2 := geom2.sdo_elem_info( 1);
			v_e2 := geom2.sdo_elem_info( 2);
			v_i2 := geom2.sdo_elem_info( 3);

			if( isSupportedGeometry( v_e1, v_i1) = FALSE) then
				tBuf := utl_lms.format_message( 'unsupported elem_info1:%s %s', TO_CHAR( v_e1), TO_CHAR( v_i1));
				dbms_output.put_line( tBuf);
				return( null);
			end if;

			if( isSupportedGeometry( v_e2, v_i2) = FALSE) then
				tBuf := utl_lms.format_message( 'unsupported elem_info2:%s %s', TO_CHAR( v_e1), TO_CHAR( v_i1));
				dbms_output.put_line( tBuf);
				return( null);
			end if;

			ordinates := mergeOrdinates( n1 => geom1.sdo_ordinates, n2 => geom2.sdo_ordinates);

			getMergedElemInfo ( e1 => geom1.sdo_elem_info, e2 => geom2.sdo_elem_info, e3 => elemInfo );

			merged := new mdsys.sdo_geometry( SDO_GTYPE     => 3002
											, SDO_SRID      => geom1.sdo_srid
											, SDO_POINT     => null
											, SDO_ELEM_INFO => elemInfo
											, SDO_ORDINATES => ordinates);
			return( merged);
		end;

		/**
		 * @name SortAndMerge
		 * @author S.Deckers
		 * @date 26-07-2017
		 * @description Sorts and Merges the geometries and return result
		 */

		FUNCTION SortAndMerge return mdsys.sdo_geometry
		AS
			l_cnt 	int;
			sorted	mdsys.sdo_geometry;

			/**
			 * @name mergeSortedGeometries
			 * @purpose Merge set of sorted geometries into a single geometry
			 */

			FUNCTION getSortedMergedGeometries return mdsys.sdo_geometry
			as
				i 		int := getStartingGeo();
				tNext 	int;
				merged	mdsys.sdo_geometry;
			begin
				LOOP
					tNext := geomInfo( i).Next;
					merged := Merge( merged, geomInfo( i).geom);

					if( tNext = 0) then
						return( merged);
					end if;

					i := geomInfo( i).Next;
				END LOOP;
			end;

		BEGIN -- Sort --

			if( collectionIs3D() = FALSE) then
				tBuf := utl_lms.format_message( 'Only 3d geometries are supported');
				dbms_output.put_line( tBuf);
				return( null);
			end if;

			l_cnt := multipleConnectedGeometryCount( );

			/* --- If we have geometries with more then a single geometry connected on either side we cannot do the sorting (20170806 SDE) */

			if( l_cnt > 0) then
				return( null);
			end if;

			l_cnt := getSingleConnectedCount();

			/* --- We need 2 geometries connected on a single side to do the sorting (20170806 SDE) */

			if( l_cnt != 2) then
				return( null);
			end if;

			Sort( );

			/* --- Merge the sorted geometries (20170808 SDE) */

			sorted := getSortedMergedGeometries( );

			return( sorted);
		END; -- Sort --

        /**
         * @name sortGeometries
         * @author S.Deckers
         * @date 06-07-2017
         * @description Sort set of geometries
         */

        FUNCTION SortAndMergeGeometries
        (
        	unsorted in mdsys.sdo_geometry
        ) return mdsys.sdo_geometry
        AS
            valid		int;
        	sorted 		mdsys.sdo_geometry;
			tGeoCount 	int;
        BEGIN
        	/* --- Only sort valid geometries (20170720 SDE) */

			select SDO_CS.MAKE_2D( unsorted).st_isValid() into valid from dual;

        	if( valid = 0 ) then
            	dbms_output.put_line( utl_lms.format_message( 'Invalid geometry', TO_CHAR( valid) ));
            	return( null);
            end if;

			/* --- Enumerate all geometries encountered and fill in temp structure (20170901 SDE) */

			fillTempStructure( theGeom =>self.geom, geoCount => tGeoCount);

			/* --- If there's only a single item, return it (20170808 SDE) */

			if( tGeoCount = 1 ) then
				tBuf := utl_lms.format_message( 'Single geometry');
				dbms_output.put_line( tBuf);
				return( self.geom);
			end if;

			/* --- Put all geometries in structure for processing (20170720 SDE) */

			setConnectionInfo  	( );
			sorted := SortAndMerge( );
			return( sorted);
        end;
    BEGIN
		ret := SortAndMergeGeometries( unsorted => self.geom);
        return( ODCIConst.Success);
    END;

    /**
     * @name ODCIAggregateMerge
     */

    MEMBER FUNCTION ODCIAggregateMerge
    (
    	self IN OUT SDE_MERGE_i
      , ctx2 IN 	SDE_MERGE_i
    ) RETURN number
    IS
    BEGIN
      	return( ODCIConst.Success);
    END;
END;
/

CREATE or replace FUNCTION SDE_MERGE (input mdsys.sdo_geometry)
	RETURN mdsys.sdo_geometry PARALLEL_ENABLE AGGREGATE USING SDE_MERGE_i;
/

























You can also download the complete zip from here.