Non-CLOB get handler query using ORDS:
select json_object(
'type' value 'Feature',
'properties' value json_object (
'ETL_TRANSFORM_ID' value ETL_TRANSFORM_ID,
'AL_ROUTE' value AL_ROUTE,
'AL_FROM' value AL_FROM,
'AL_TO' value AL_TO,
'TRANSFORM_TYPE_ID' value TRANSFORM_TYPE_ID,
'NOTES' value NOTES
),
'geometry' value (sdo_cs.transform(sdo_lrs.convert_to_std_geom(c.AL_GEOM),4326)).get_geojson() format json
) as geojson
from ETL_TRANSFORMS_ELRSP2ALRS c
where c.ETL_TRANSFORM_ID=:etl_transform_id
Results:
{"type":"Feature","properties":{"ETL_TRANSFORM_ID":1,"AL_ROUTE":"RP106I","AL_FROM":0,"AL_TO":0.546,"TRANSFORM_TYPE_ID":6,"NOTES":"ROUTES WITH EQUAL FROM TOS"},"geometry":{ "type": "LineString", "coordinates": [ [-109.229318364208, 41.6062253006731], [-109.228821595856, 41.6062387345447], [-109.228310454459, 41.6062467948663], [-109.227881958069, 41.6062561985735], [-109.227691515228, 41.606270975825], [-109.227483106083, 41.6063193377154], [-109.227238764325, 41.6064032992444], [-109.227019575396, 41.6065141282955], [-109.226864166852, 41.6066148818131], [-109.226721334722, 41.6067566081614], [-109.226601858789, 41.6068869155246], [-109.226544366611, 41.6069769214877], [-109.226484179487, 41.6070944663998], [-109.226441060353, 41.6072375350609], [-109.226372788391, 41.607527701512], [-109.226312601267, 41.6077668191807], [-109.226249719197, 41.6079468285264], [-109.226167074191, 41.6081234790005], [-109.226080835924, 41.608288710565], [-109.225946986947, 41.6084727484762], [-109.225808646393, 41.6086446958312], [-109.225646051327, 41.6088052243935], [-109.225506812458, 41.6089261243022], [-109.225328047716, 41.6090658305812], [-109.225152876236, 41.6091880733272], [-109.224957043504, 41.6092982259329], [-109.224722583214, 41.6094124083134], [-109.224482733034, 41.6095077840293], [-109.224259052528, 41.6095830098466], [-109.224022795608, 41.6096521906547], [-109.223731741456, 41.6097314462466], [-109.223435297412, 41.6098012985519], [-109.223182870817, 41.6098590609781], [-109.222899003188, 41.6099141367316], [-109.222618728819, 41.6099658541646], [-109.222262995967, 41.6100216014811], [-109.221875822079, 41.6100753337887], [-109.221618903908, 41.6101062298453], [-109.221365578998, 41.6101418274574] ] }}
CLOB get handler query using ORDS:
select json_object(
'type' value 'Feature',
'properties' value json_object (
'ETL_TRANSFORM_ID' value ETL_TRANSFORM_ID,
'AL_ROUTE' value AL_ROUTE,
'AL_FROM' value AL_FROM,
'AL_TO' value AL_TO,
'TRANSFORM_TYPE_ID' value TRANSFORM_TYPE_ID,
'NOTES' value NOTES
),
'geometry' value SDO_UTIL.TO_GEOJSON(sdo_cs.transform(sdo_lrs.convert_to_std_geom(c.AL_GEOM),4326)) returning clob
) as geojson
from ETL_TRANSFORMS_ELRSP2ALRS c
where c.ETL_TRANSFORM_ID=:etl_transform_id
Results:
{"type":"Feature","properties":{"ETL_TRANSFORM_ID":7,"AL_ROUTE":"RP121I","AL_FROM":0,"AL_TO":0.283,"TRANSFORM_TYPE_ID":6,"NOTES":"ROUTES WITH EQUAL FROM TOS"},"geometry":"{ \"type\": \"LineString\", \"coordinates\": [ [-108.787816165, 41.6794345937937], [-108.787465822039, 41.6793426761891], [-108.787057088584, 41.6792500875202], [-108.786398623481, 41.6791145589387], [-108.786156078354, 41.6790582004346], [-108.785884787139, 41.6789843975571], [-108.785596427932, 41.6788857699431], [-108.785432036235, 41.6788300821759], [-108.785188592793, 41.678747556842], [-108.784772672817, 41.6785918990135], [-108.784436702901, 41.678482535745], [-108.784124987497, 41.6783725013486], [-108.783668643333, 41.6782228811054], [-108.783221282321, 41.6780792990203], [-108.782925736593, 41.6779994565073], [-108.782730802176, 41.6779538321696] ] }"}
Note the addition characters “\” added to the CLOB. These additional characters have to be removed using multiple regexp_replace commends. Seems clumsy. First case works great with other geojson readers, CLOB case has to be clumsily manipulated. Is there a better way for handling CLOB case in ORDS so this doesn't happen.?