Oracle spatial operator SDO_JOIN returns ORA-01722 invalid number

Question

Firstly, I have created metadata in user_sdo_geom_metadata for two tables 'test_katt' and 'test_del_ko'. Each table has a unique id (featureid). And one table has points geometry and the other has polygons.

--example

insert into user_sdo_geom_metadata (table_name, column_name, diminfo,srid) values (
    'test_katt',  --table name
    'geometry',         
    SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Y',365000,627000,0.005), --box
    SDO_DIM_ELEMENT('X',28000,195000,0.005)), --box
    null
  );

Secondly: I have created a spatial index for both tables:

create index test_katt_idx 
          on test_katt (geometry) indextype is mdsys.spatial_index;

Finally, I wanted to join data from points in 'test_katt' and data from polygons in 'test_del_ko' based on any spatial interaction. With the result: Error report - ORA-01722: invalid number

select *   
from test_del_ko dk, test_katt ka,  
                table (SDO_JOIN('test_del_ko', 'geometry',   
                'test_katt', 'geometry',   
                'mask = anyinteract') )a  
where a.rowid1 = dk.featureid  
and a.rowid2 = ka.featureid;

I haven't succeeded yet in creating a result with the SDO_JOIN, so any help with the procedure would be very helpful. We have oracle 12 database.

Please edit your question with a complete minimal reproducible example including ALL the DDL statements for your tables including CREATE TABLE, CREATE INDEX, inserts into metadata tables for ALL the tables you are using and some INSERT statements with some sample data that represents the problem. Then if people want to try to help you they have a script they can run and do not have to try to reverse engineer your code into something that runs. Please help us to be able to help you. — MT0

gsalem gsalem · Accepted Answer · 2020-06-18T10:04:35

In your query, a.rowid1 and a.rowid2 are oracle rowids in varchar2 format, and you are comparing them to the featureid columns in your tables, and I suppose they are numbers. That's why you have an error. Use this query instead:

select *   
from test_del_ko dk, test_katt ka,  
                table (SDO_JOIN('test_del_ko', 'geometry',   
                'test_katt', 'geometry',   
                'mask = anyinteract') )a  
where a.rowid1 = dk.rowid  
and a.rowid2 = ka.rowid;

Oracle spatial operator SDO_JOIN returns ORA-01722 invalid number

1 Answers