simple sde.st_buffer spatial view, ORA errors

521
7
10-09-2017 02:24 PM
Highlighted
New Contributor III

I want to create a spatial view that draws a buffer around my input features, like this:

SELECT 
a.OBJECTID,
sde.st_buffer (a.SHAPE, 33) as SHAPE
FROM schemaname.tablename a
WHERE OBJECTID IS NOT NULL

I get the following errors:

Any ideas what the problem could be?

Geodatabase version 10.3; Oracle version 12.1.0.2. Configured according to this doc.

Tags (2)
0 Kudos
7 Replies
Highlighted
MVP Regular Contributor

Make sure the Extproc was configured correctly. I just ran a quick test and it is working fine.

Connect as SDE in SQLPlus and execute the below query. If it gives you an error instead of returning a result as below, the Extproc has not been configured correctly then:

SQL> select sde.ST_AsText(SDE.ST_Geometry('POINT (10 10)', 0)) from dual;

SDE.ST_ASTEXT(SDE.ST_GEOMETRY('POINT(1010)',0))
--------------------------------------------------------------------------------

POINT  ( 10.00000000 10.00000000)

https://community.esri.com/community/gis/managing-data?sr=search&searchId=f8869f23-d604-4c6c-8ccc-7f...

Highlighted
New Contributor III

Thank you for your reply Asrujit. I found this Esri doc. that describes a configuration consideration with Oracle 12: http://support.esri.com/en/technical-article/000012391

Your test query (...from dual) works now. My view still fails with the same error, but I'm actually not sure anymore that Esri can create new geometries (e.g. buffer) via spatial views that I can then consume in ArcMap/ArcGIS Server.

0 Kudos
Highlighted
MVP Regular Contributor

Tobias

Error: ORA-06598: insufficient INHERIT PRIVILEGES privilege" during Oracle 12c Data Pump import  this Article mentions about database Restore issues, so it should not be related to your problem.

As I mentioned in my first Post, I tried the same SQL that you are using and it works as expected and I can see the View in ArcMap. So using it in ArcGIS Server should work as well.

SELECT a.OBJECTID, sde.st_buffer (a.SHAPE, 65) as SHAPE
FROM sde.Testdata a
WHERE OBJECTID IS NOT NULL

I used the above and it works perfectly.

I was using Oracle 11.2.0.4 and a 10.2.1 geodatabase for the quick test, which was readily available to me.

0 Kudos
Highlighted
New Contributor III

Ok, so it is possible. Thank you.

I had to 'GRANT INHERENT PRIVILEGES ON ...' to get even your simple test query to work. Now I guess I have to look further....

0 Kudos
Highlighted
MVP Regular Contributor

So this is how it looks in ArcMap and I believe this is what you wanted..

0 Kudos
Highlighted
New Contributor III

Just to close this thread: it turned out that our libst_shapelib file was quite old. After we replaced it with a more recent one the error does not appear.

0 Kudos
Highlighted
MVP Esteemed Contributor

Thanks for sharing the resolution.

0 Kudos