Home » Server Options » Spatial » UPDATE-SET Command does not work (Oracle Locator 11g)
UPDATE-SET Command does not work [message #653379] Mon, 04 July 2016 06:25 Go to next message
moehre
Messages: 43
Registered: June 2016
Member
Hi,

I have an Update-Query to update data with data from another table!

UPDATE building a 

Set a.GRID_ID =  (SELECT f.OBJECTID 
                  FROM  FISHNET_500m f
                  WHERE SDO_Contains( f.shape, a.centroid_geom) = 'TRUE');

This is not working at the moment. It occurrs this error message: 29861. 00000 - "domain index is marked LOADING/FAILED/UNUSABLE"

But if I try only the SELECTcomand like this:
SELECT f.OBJECTID 
FROM  FISHNET_500m f, building a
WHERE SDO_Contains(f.shape, a.centroid_geom) = 'TRUE';   

than it works!

Does somebody know what the problem is?
I think the index is ok because my SELECT statement is working fine. Because of the there must be a mistake in the UPDATE statement.
Re: UPDATE-SET Command does not work [message #653380 is a reply to message #653379] Mon, 04 July 2016 06:50 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you consider dropping the index and creating it back again? That might be the simplest option. Otherwise:

Oracle

ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

Cause: An attempt has been made to access a domain index that is being built or is marked failed by an unsuccessful DDL or is marked unusable by a DDL operation.

Action:
- Wait if the specified index is marked LOADING
- Drop the specified index if it is marked FAILED
- Drop or rebuild the specified index if it is marked UNUSABLE.
Re: UPDATE-SET Command does not work [message #653389 is a reply to message #653380] Mon, 04 July 2016 09:22 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
Yes I did already dropping and creating the index back.
If I have no index it is working!

But if I create a spatial index like this:

CREATE INDEX centroid_geom_SPX ON "TEST_3D"."BUILDING"("CENTROID_GEOM") INDEXTYPE IS MDSYS.SPATIAL_INDEX

it says:

ORA-13203: View USER_SDO_GEOM_METADATA not readable
ORA-13203: View USER_SDO_GEOM_METADATA not readable
ORA-13000: number of dimension wrong
ORA-06512: in "MDSYS.SDO_INDEX_METHOD_10I", Zeile 10
29855. 00000 - "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause: Failed to successfully execute the ODCIIndexCreate routine.
*Action: Check to see if the routine has been coded correctly.

Data:

Centroid_Geom = MDSYS.SDO_GEOMETRY(2001,31467,MDSYS.SDO_POINT_TYPE(3482983.53,5377745.754,NULL),NULL,NULL)
Fishnet_500m = MDSYS.SDO_GEOMETRY(2003,31467,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(3486367.35,5375324.36,3486867.35,5375 324.36,3486867.35,5375824.36,3486367.35,5375824.36,3486367.35,5375324.36))

To get some informations about the metadata I used this query:
select * from all_sdo_geom_metadata;

I get this result: OWNER: I | TABLE_NAME: ...| Column_Name: ... | DIMINFO: MDSYS.SDO_DIM_ARRAY() | SRID: 31467


[Updated on: Mon, 04 July 2016 09:36]

Report message to a moderator

Re: UPDATE-SET Command does not work [message #653394 is a reply to message #653389] Mon, 04 July 2016 10:12 Go to previous message
moehre
Messages: 43
Registered: June 2016
Member
Ok I get this!

My information for diminfo inside metadata was wrong (empty).

I did an update like this:
UPDATE user_sdo_geom_metadata
set diminfo = SDO_DIM_ARRAY(
SDO_DIM_ELEMENT('X',0,0.1000000,0.005),
SDO_DIM_ELEMENT('Y',0,0.1000000,0.005))
WHERE table_name = 'building'
AND COLUMN_NAME = 'centroid_geom';

Now I can create a spatial index and my spatial query is working fine Wink...
Previous Topic: Update-SET Query for multiple rows
Next Topic: Spatial query SDO_Contains() does not work
Goto Forum:
  


Current Time: Thu Mar 28 12:24:32 CDT 2024