Oracle function error: ORA-00932: inconsistent datatypes: expected CHAR got MDSYS.SDO.GEOMETRY

Question

I am attempting to create a function (at the database level) that is to be called from a web map. The function is to enable that when a user clicks on a point the associated region (polygon) is also selected/highlighted. Points and regions are associated at the attribute level via the value in a column called code (eks. not spatially).

I have created the below code but it returns the error:

ORA-00932: inconsistent datatypes: expected CHAR MDSYS.SDO_GEOMETRY (?)

Code:

create or replace function region_select 
  ( 
      p_geom in sdo_geometry   
  )  

RETURN SDO_GEOMETRY
DETERMINISTIC

IS 
  v_pointId number;
  v_code number;
  geom_out sdo_geometry;

BEGIN

select point_id into v_pointId from points where geom = p_geom;
select code into v_code from points where point_id = v_pointId;


    if (v_pointId is not null)
    then
    select geom into geom_out from regions where code  = v_code;
    RETURN geom_out;
    end if;

-- error handling
exception
when others then
    raise_application_error(-20001,'An error was encountered - '|| 
sqlcode ||' -error- '|| sqlerrm);
    rollback;
end;

Answer 1

First of all, where geom = p_geom shows that a spatial selection is used (not for REGIONS, but for POINTS) - and you cannot check geometry equality using where geom = p_geom. You should use select point_id into v_pointId from points t where SDO_EQUALS(t.geom,p_geom) = 'TRUE' - this presupposes that a spatial index exists for POINTS.geom. If you cannot have a spatial index AND table POINTS is really small, you can use where sdo_geom.relate(t.geom,'determine',p_geom)='EQUAL' - but it'll be slow...

Second, I agree with @Alex Poole in that such error handling is not only meaningless, but detrimental as well. The way it's written, you'll get the error (which you would have gotten anyway without the exception) but you will lose e.g. where this error happened. You should firstly get rid of it, run the code and see if you get a better description of the error (CHAR MDSYS.SDO_GEOMETRY? what "CHAR"? in what line?). If what you get doesn't shed any more light, we're here to help...

On a side note, I'm not sure that this function is indeed DETERMINISTIC - you have to make sure it is (and why declare it as such? what's the goal here?). Finally, it's best to declare geometry variables as "MDSYS.SDO_GEOMETRY", not just "SDO_GEOMETRY" - old bugs may come back to bite you...

HTH, let me know if you need anything else.