Trouble with ST_GEOMETRY

[AdamZiegler]

Greetings all,

I have run into a wall and am in need of some direction. I recently tried to create a new feature class using ST_GEOMETRY and I cannot get the feature class to draw in ArcMap. I have the same issue when trying to preview the feature class in ArcCatalog.

The selected object failed to draw: ST_GEOM_TEST: Underlying DBMS error [ORA-00942: table or view does not exist][APPDEV1.ST_GEOM_TEST]

So, I have an Oracle 11g database with SDE 10.0 installed. Full disclosure on the upgrade to 10.0 from 9.3.1 is that we did not have the proper set up for ST_GEOMETRY on our database server box. We do not use SDE service anymore and were not planning to use ST_GEOMETRY. I have since updated the SDE libraries to know where to find the ST_GEOMETRY and RASTER .so binaries. We are running IBM AIX 6.1. There were some invalid objects that recompiled to valid after I fixed the SDE libraries. I also re-configured the listener per the instructions and all of that seems to be working properly.

I can use sde -o describe and describe_long on the table. The long description is below (fields between OBJECTID and SHAPE omitted for space preservation).

ArcSDE 10.0  for Oracle11g Build 2063 Tue May 22 14:45:23  2012
Attribute   Administration Utility
-----------------------------------------------------

----------------------------------------------------------------
Column Owner           : APPDEV1
Column Table           : ST_GEOM_TEST
Column Name            : OBJECTID
Row ID Column Type     : SDE Maintained
SDE Column Type        : SE_INT32
Column Size            : 10
Decimal Digits         : 0
Null Allowed?          : False

----------------------------------------------------------------
Column Owner           : APPDEV1
Column Table           : ST_GEOM_TEST
Column Name            : SHAPE
SDE Column Type        : SE_SHAPE
Column Size            : 0
Decimal Digits         : 0
Null Allowed?          : True
Layer ID               : 1167

Anybody have any ideas on where I can look or troubleshoot next? Any and all insights/ideas are welcome and let me know if more information is needed.

Thanks!
Adam Z

[VinceAngelo · Accepted solution]

I had a client site where a script was run to "secure" the database, and it wound up
removing all sorts of necessary permissions from the ST_GEOMETRY packages,
which generated spurious "does not exist" errors ("SDE.function" was interpreted
as "SDE.table", so the error wasn't about the package).  I had to exercise all the
ST_* methods to demonstrate the issue, then used an operational instance to
determine which EXECUTEs had been removed from PUBLIC.  Tech Support could
help you do the same.

- V

[AdamZiegler]

Great, thanks for the insight Vince.  I'll work through it and re-post any findings/solutions.

Adam Z

[AdamZiegler]

Okay, I am back in ST_GEOMETRY business. The problem had a few causes.

1. The database and database server were not properly set up to use ST_GEOMETRY (see help)


2. Privileges were not granted to PUBLIC on all of the ST_% objects - EXECUTE and some SELECT, UPDATE, INSERT, DELETE


3. PUBLIC Synonyms were not created on all the necessary ST_% objects

To get up and running again:

1. Configured the database and database server correctly (see help)

[INDENT]


1. Created the proper libraries pointing to the ST_GEOMETRY binaries


2. Added entries in the database server tnsnames.ora and listener.ora


3. Recompiled the invalid ST objects in the SDE schema


[/INDENT]

2. Applied the proper privileges to the ST_GEOMETRY objects

[INDENT]


1. I was fortunate enough to get my hands on the grants of a fresh proper 10.0 database - thanks Jake


2. Using that I compared the grants to what my DB had


3. Here's the select statement to get the grants:

[INDENT]


• select grantee, owner, grantor, privilege, table_name from dba_tab_privs where owner='SDE' and table_name LIKE 'ST_%' order by table_name;


[/INDENT]

3. The grants that I was missing were added to a SQL script that I ran against the DB

[/INDENT]

Create the PUBLIC synonyms still needed

[INDENT]

1. Jake hooked me up again with a nice list of the synonyms from a good DB


2. That was compared to my DB


3. I created a sql script to create the synonyms


4. Here are the select statements to retrieve the necessary synonyms

[INDENT]


• select synonym_name from all_synonyms where owner = 'PUBLIC' and synonym_name LIKE 'ST_%';


• select synonym_name from all_synonyms where owner = 'PUBLIC' and synonym_name LIKE 'USER_ST_GEOM%';


• select synonym_name from all_synonyms where owner = 'PUBLIC' and synonym_name LIKE 'ALL_ST_GEOM%';

[/INDENT]

[/INDENT]

So, thank you to Vince for pointing me in the direction of my problem and thank you to Jake for getting me what a correct DB should be. I've attached the output of what the proper DB privileges and synonyms should be.

Thanks,
Adam Z