OraFAQ Forum: Spatial » UPDATE-SET Command does not work

Home » Server Options » Spatial » UPDATE-SET Command does not work (Oracle Locator 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

UPDATE-SET Command does not work [message #653379]

Mon, 04 July 2016 06:25

moehre
Messages: 43
Registered: June 2016

Member

Hi,

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

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: UPDATE-SET Command does not work [message #653380 is a reply to message #653379]

Mon, 04 July 2016 06:50

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: UPDATE-SET Command does not work [message #653389 is a reply to message #653380]

Mon, 04 July 2016 09:22

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:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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

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:

Code: [Select all] [Show/ hide]

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

...

Report message to a moderator

Previous Topic:	Update-SET Query for multiple rows
Next Topic:	Spatial query SDO_Contains() does not work

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 17 08:22:27 CDT 2025