Return a value/row when selecting SDO_GEOMETRY - Databases

TopAnswers Databases

Meta

dbfiddle add tag

User 1791

In db<>fiddle for Oracle 21c, if I select an SDO_GEOMETRY value, an empty resultset gets returned:


>     select
>         sdo_geometry('LINESTRING (1 2,3 4)') as sdo_geom
>     from
>         dual
> 
>
> | SDO_GEOM |
> | :------- |
> [*empty resultset; no rows returned*]

*db<>fiddle [here](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=bda9de590d5f965bcbf1a3c631acce7a)*

That empty resultset can be confusing/misleading.


Whereas in SQL clients like SQL Developer, a value/row is returned:

```
select
    sdo_geometry('LINESTRING (1 2,3 4)') as sdo_geom
from
    dual

CTRL+F5 Output: [MDSYS.SDO_GEOMETRY]

F5 Output: SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(1, 2, 3, 4))
```

**Question:**

Could db<>fiddle be enhanced so that SDO_GEOMETRIES return a value/row, instead of an empty resultset?

-------------------------------

**Edit:**

DB<>FIDDLE doesn’t seem to like Oracle VARRAYs either:

>     select
>         a.sdo_geom.sdo_elem_info
>     from
>         (
>         select
>           sdo_geometry('LINESTRING (1 2,3 4)') as sdo_geom
>         from
>           dual
>         ) a
> 
> | SDO_GEOM.SDO_ELEM_INFO |
> | :--------------------- |

*db<>fiddle [here](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=f14bf1c0dbdf5ca426bb88f7fb920ba1)*

That query outputs the VARRAY as text in SQL Developer, but in DB<>FIDDLE, it outputs an empty resultset, just like SDO_GEOMETRY.

Jack's solution for converting the SDO_GEOMETRY to WKT works. But for VARRAYs, I haven't found a way to convert them to text (yet).

Top Answer

Jack Douglas

Probably not, but you can modify your SQL to use `sdo_util.to_wktgeometry`

> Could db<>fiddle be enhanced so that SDO_GEOMETRIES return a value/row, instead of an empty resultset?

PHP's [oci_fetch_all](https://www.php.net/manual/en/function.oci-fetch-all.php) doesn't like [abstract data types](https://www.orafaq.com/wiki/ADT) like `SDO_GEOMETRY`, I'm getting errors like this in the logs:

```
PHP Warning:  oci_fetch_all(): ORA-00932: inconsistent datatypes: expected CHAR got ADT
```

Short of a fix, you can work around this with `sdo_util.to_wktgeometry`:

<>https://dbfiddle.uk/?rdbms=oracle_21&fiddle=1d333b8cb4bea46b8522aed2d921a5ec

1 Answer