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;

If you remove the exception handler you might get a better idea of where the error is occurring. (You shouldn't normally commit/rollback inside a procedure, but you never reach the rollback anyway; all you're doing is raising a custom exception that hides useful info.) Is the error actually thrown inside the function, or by the call being made (i.e. trying to assign the return from the function to a string variable)? Including the table DDL would be useful too. Your code seems a bit circular though; isn't geom_out always just going to be the same as p_geom with the queries you're using? — Alex Poole
thanks that is really helpful. After removing the exception it became clear that it doesn't like the: select point_id into v_pointId from points where geom = p_geom; p_geom is the in-parameter point geometry , while geom_out is polygon geometry to be selected based on the in parameter — Ric

GregStef GregStef · Accepted Answer · 2019-05-29T18:54:22

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.

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

1 Answers