Create GeoJSON Document from Selection of SDO_Geometry objects.

The SDO2GEOJSON function available on this website converts a single SDO_GEOMETRY object to a GeoJSON Snippet.

Here we present a procedure that creates an GeoJSON document from a collection of SDO_GEOMETRY objects in a SQL SELECT ref_cursor:

 CREATE OR REPLACE
 FUNCTION sdo2geojson_document(r_cur SYS_REFCURSOR)
   RETURN CLOB deterministic
 IS
   GEOM      MDSYS.SDO_GEOMETRY;
   js_geom   CLOB;
   j_geom    CLOB;
   start_tag VARCHAR2(100);
   end_tag   VARCHAR2(100);
 BEGIN
   DBMS_LOB.CREATETEMPORARY (LOB_LOC => JS_GEOM, cache => TRUE);
   start_tag := '{ "type": "FeatureCollection", "features": [';
   DBMS_LOB.WRITE(LOB_LOC => JS_GEOM,
                  AMOUNT => LENGTH(START_TAG),
                  OFFSET => DBMS_LOB.GETLENGTH(JS_GEOM)+1,
                  buffer => start_tag );
   LOOP
     FETCH r_cur INTO geom;
     EXIT WHEN R_CUR%NOTFOUND;
     J_GEOM := SDO2GEOJSON(GEOM);
     DBMS_LOB.APPEND(dest_lob => JS_GEOM,
                     src_lob  => j_geom );
   END LOOP;
   end_tag := '] }';
   DBMS_LOB.WRITE(LOB_LOC => JS_GEOM,
                  AMOUNT => LENGTH(END_TAG),
                  OFFSET => DBMS_LOB.GETLENGTH(JS_GEOM)+1,
                  buffer => end_tag );
   RETURN JS_GEOM;
 END;

I hope this is of use to someone.