Forum Stats

  • 3,855,478 Users
  • 2,264,508 Discussions
  • 7,906,005 Comments

Discussions

Unable to run spatial operations through dblinks

bjasti02
bjasti02 Member Posts: 4
edited Aug 16, 2012 6:26PM in SQL & PL/SQL
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

Answers

  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    edited Aug 16, 2012 4:18PM
    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
    Gaff Member Posts: 1,490
    edited Aug 16, 2012 4:47PM
    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
    Gaff
  • bjasti02
    bjasti02 Member Posts: 4
    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
    Gaff Member Posts: 1,490
    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.
This discussion has been closed.