CODEWITHSUNDEEP
oracle11goracle-sqldeveloperoracle-spatial
user3088554
user3088554

Reputation: 31

Oracle SQL Developer create SDO_GEOMETRY invalid datatype

I am trying to create a spatial database in SQL Developer, which is connected to Oracle 11g Release 2 on AWS.

When I do this

CREATE TABLE cola_markets (
mkt_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape SDO_GEOMETRY);

and compile, I always get this:

errormkt_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape SDO_GEOMETRY)
Error at Command Line : 4 Column : 7
Error report -
SQL Error: ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"
*Cause:    
*Action:

It seems that SQL Developer cannot find the sdo_geometry datatype. How can I resolve this issue?

Upvotes: 2

Views: 3632

Answers (3)

Raúl Moreno
Raúl Moreno

Reputation: 308

In case this happens to anyone with oracle spatial or locator or anything needed correctly installed in MDSYS, and it still doesn't work, you can try granting EXECUTE privileges from MDSYS to your user as I read on

https://blog.ronnyegner-consulting.de/category/oracle-in-general/

It worked for us.

Upvotes: 0

superbCoder
superbCoder

Reputation: 63

Please check the version of your database. For oracle 12c onwards all of SDO_GEOM package is available to oracle locator users. Thus your should work. However, in 12c you may need to deinstall spatial part as mentioned here : https://docs.oracle.com/database/121/SPATL/sdo_locator.htm#SPATL1433

Upvotes: 0

Albert Godfrind
Albert Godfrind

Reputation: 2078

That should work. Are you sure your database does have Oracle Spatial or Oracle Locator installed.

Databases provided on AWS typically don't.

Upvotes: 1

Related Questions