How to cast in oracle -Spatial Database?

Question

Im trying to get data inside the table which is below.

CREATE TABLE Eshot (
    eshotNumber NUMBER,
    eshot_geo_location SDO_GEOMETRY);

Using this query returns unsupported data type;

Select e.eshot_geo_location from Eshot e;

Inserted values example

INSERT INTO Eshot VALUES(476,SDO_GEOMETRY(2001, 8307, 
SDO_POINT_TYPE(38.3678709,27.1824648,NULL), NULL, NULL));

How to make cast to grab data correctly? Thanks in advance

Balazs Papp · Answer

Use an up-to-date client and tool. I remember some old clients and tools that do not support all datatypes. For example, if I remember correct, Toad 9.0 for Oracle does not support the TIMESTAMP (or maybe it was caused by and old Oracle client, I do not use Toad, just seen this once at a client on-site). Below works just fine from 12.1.0.2 SQL*Plus or SQL Developer (4.1.5.21).

SQL> desc oe.customers
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                               NOT NULL NUMBER(6)
 CUST_FIRST_NAME                           NOT NULL VARCHAR2(20)
 CUST_LAST_NAME                            NOT NULL VARCHAR2(20)
 CUST_ADDRESS                                       OE.CUST_ADDRESS_TYP
 PHONE_NUMBERS                                      OE.PHONE_LIST_TYP
 NLS_LANGUAGE                                       VARCHAR2(3)
 NLS_TERRITORY                                      VARCHAR2(30)
 CREDIT_LIMIT                                       NUMBER(9,2)
 CUST_EMAIL                                         VARCHAR2(40)
 ACCOUNT_MGR_ID                                     NUMBER(6)
 CUST_GEO_LOCATION                                  MDSYS.SDO_GEOMETRY
 DATE_OF_BIRTH                                      DATE
 MARITAL_STATUS                                     VARCHAR2(20)
 GENDER                                             VARCHAR2(1)
 INCOME_LEVEL                                       VARCHAR2(20)

SQL> select CUST_GEO_LOCATION from oe.customers where rownum = 1;

CUST_GEO_LOCATION(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_OR
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-91.66643, 41.977151, NULL), NULL, NULL)

How to cast in oracle -Spatial Database?

One Answer

Add your own answers!

Ask a Question