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