SDE_MERGE download page

























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.