Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query works in on-prem Oracle database, but not in online environment: ORA-13236 (SDO_Join)

User_1871Apr 6 2023 — edited Apr 6 2023

I have sample data and a query that I want to share online for use in forum posts. I can't use Oracle Live SQL for this -- due to a bug: Create SDO_GEOMETRY spatial index in Oracle Live SQL.

So I'm using db<>fiddle instead: https://dbfiddle.uk/-Fm0SQUr (hint: skip down to statement #12)

The sample data works as expected. So do other queries on the data. However the following query is throwing errors, even though the same query (using the same data) works fine in my on-prem Oracle 18c database.

SELECT 
   b.objectid as point_object_id, 
   c.objectid as polygon_object_id
FROM 
   TABLE(sdo_join('PNT','SHAPE','PLY','SHAPE')) a,
   PNT b,
   PLY c
WHERE 
   a.rowid1 = b.rowid
AND a.rowid2 = c.rowid
AND SDO_GEOM.RELATE (b.shape, 'ANYINTERACT', c.shape, 1.0) = 'TRUE'
FETCH FIRST 5 ROWS ONLY

ORA-13236: internal error in R-tree processing: [SDO_Join in active txns not supported]
ORA-06512: at "MDSYS.SDO_JOIN", line 664
ORA-06512: at "MDSYS.RTREEJOIN_IMP_T", line 21
ORA-06512: at "MDSYS.RTREEJOIN_IMP_T", line 16
ORA-06512: at line 1
ORA-06512: at "MDSYS.SDO_JOIN", line 628
ORA-06512: at line 1

Why does the query fail in db<>fiddle (Oracle 18c), but not my on-prem database?

Thanks.

(I posted the same question on TopAnswers, which is the official forum for db<>fiddle: https://topanswers.xyz/fiddle?q=4131. But I doubt that crowd has much experience with Oracle Spatial. So they likely can't help.)

This post has been answered by User_1871 on Apr 6 2023

Jump to Answer

Added on Apr 6 2023

#spatial

5 comments

429 views