Forum Stats

  • 3,851,866 Users
  • 2,264,048 Discussions
  • 7,904,882 Comments

Discussions

Spatial query does not work with DB-link in a spatial view

I have spatial data in different database schema. But I would only work with the main schema. So, I built views with the database link to the views of the secondary schema and filled the user_sdo_geom_metadata for new views.

A "normal" query on an attribute works fine, but not a spatial query:

SQL:
select count(*) from VIEW_NAME a
where sdo_relate(
a.geom,
MDSYS.sdo_geometry(
3003,
31466,
null,
mdsys.sdo_elem_info_array(1,1003,3),
mdsys.sdo_ordinate_array( x1, y1, 0, x2, y2, 0)), 'mask=ANYINTERACT') = 'TRUE';

Result:
Error starting at line 2 in command:
select count(*) from VIEW_NAME a
where sdo_relate(
a.geom,
MDSYS.sdo_geometry(
3003,
31466,
null,
mdsys.sdo_elem_info_array(1,1003,3),
mdsys.sdo_ordinate_array(+x1+, y1, 0, x2, y2, 0)), 'mask=ANYINTERACT') = 'TRUE'
Error report:
SQL Error: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
ORA-02063: preceding line from DB-LINK-NAME
01445. 00000 - "cannot select ROWID from, or sample, a join view without a key-preserved table"
*Cause:
*Action:


The spatial select statement works in the original schema.

Anyone an idea?

Thanks,
Cord
Tagged:
This discussion has been closed.