Query works in on-prem Oracle database, but not in db<>fiddle: ORA-13236 (SDO_Join) - db<>fiddle

TopAnswers db<>fiddle

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

Query works in on-prem Oracle database, but not in db<>fiddle: ORA-13236 (SDO_Join)

add tag

User 1791

I have sample data and a query that works as expected in my on-prem Oracle 18c database.

Sample data and query: https://dbfiddle.uk/-Fm0SQUr (hint: skip down to statement #12)

The query:

```
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
```

I get errors in db<>fiddle:

```
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
```

I don't get those errors in my on-prem database. Any idea why the query fails in db<>fiddle (Oracle 18c)?

Top Answer

User 1791

Solution:

I had to commit the sample data I created:


> [20.8 SDO_JOIN - Usage Notes](https://docs.oracle.com/en/database/oracle/oracle-database/18/spatl/spatial-operators-reference.html#GUID-4D9DD9EF-DD9C-4198-A416-37B900B4E32F)  
>   
> Before you call SDO_JOIN, you must commit any previous DML statements in your session. Otherwise, the following error will be returned: `ORA-13236: internal error in R-tree processing: [SDO_Join in active txns not supported]`

https://dbfiddle.uk/4UO3qKHq

![image.png](/image?hash=3d1d8d7819548092ff0108938c8059627a3c9967b8331b0e4eb1b09ab7a3e211)

Oracle Forum: [Query works in on-prem Oracle database, but not in online environment: ORA-13236 (SDO_Join)](https://forums.oracle.com/ords/apexds/post/query-works-in-on-prem-oracle-database-but-not-in-online-en-7753)

1 Answer