Unable to run spatial operations through dblinks

bjasti02

Hello All,

I am trying to run spatial operations through dblinks. Please see the example query below

select a.OGC_GEOMETRY.sdo_gtype from <table>@dblink a where sdo_nn(a.OGC_GEOMETRY,mdsys.sdo_geometry(2001,null,mdsys.sdo_point_type(0,0,null),null,null),'sdo_num_res=1')='TRUE'.

Query fails with the following error

ORA-13249:

ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 17
ORA-06512: at "MDSYS.PRVT_IDX", line 9
*13249. 00000 - "%s"*
**Cause: An internal error was encountered in the extensible spatial index*
component. The text of the message is obtained from some
other server component.
**Action: Contact Oracle Support Services with the exact error text.*

Same Query runs fine in the original database but fails with dblinks. Is it possible to run spatial operations through dblink?

Thanks & Regards,
Jasti

Hoek

Welcome to the SQL and PL/SQL Forum.

Spatial has it's own forum @ 3078
You might want to search and/or ask over there as well.

Gaff

I've never used spatial myself but this other user had issues with it and dblinks. They ended up using the dblink to create a local materialized view with local indexes and used those.

849406

--

Found this as well. Does it mean anything to you?

4.2 Querying Spatial Data

This section describes how the structures of a Spatial layer are used to resolve spatial queries and spatial joins.

Spatial uses a two-tier query model with primary and secondary filter operations to resolve spatial queries and spatial joins, as explained in Section 1.6. The term two-tier indicates that two distinct operations are performed to resolve queries. If both operations are performed, the exact result set is returned.

You cannot append a database link (dblink) name to the name of a spatial table in a query if a spatial index is defined on that table.

If a spatial index is created in a database that was created using the UTF8 character set, spatial queries that use the spatial index will fail if the system parameter NLS_LENGTH_SEMANTICS is set to CHAR. For spatial queries to succeed in this case, the NLS_LENGTH_SEMANTICS parameter must be set to BYTE (its default value).

http://docs.oracle.com/cd/B13789_01/appdev.101/b10826/sdo_index_query.htm

Edited by: Gaff on Aug 16, 2012 4:46 PM

bjasti02

Thanks for your reply Gaff.

Your First suggestion:
Local materialized view is not an option in my case as I am trying to query multiple databases dynamically through dblinks. I cannot create those many copies in my local database.

Your Second suggestion:

My tables are all spatially indexed.

This line in Oracle manual *"You cannot append a database link (dblink) name to the name of a spatial table in a query if a spatial index is defined on that table."* leaves me for no other choice than avoiding dblinks.

Can any one suggest alternative solution?

Thanks
Jasti

Gaff

Possibly a local function/procedure on each database that will do the spatial query for you and return results? But I'm guessing you can't get them to allow that, depending on who "they" are.