Bug of function-based index

[Qiang Han]

Hi, Folks,
I am really frustrated by a bug of function-based index. Is there
anyone has any idea on this? Please drop some line, thanks first.

DETAILS:

I have a table called Point_tab. I generated a spatial index on this
table by using function-based index.

Function is called get_point_geom(ppf_id, playerid) return
mdsys.sdo_geometry.

table is defined by:

create table point_tab(
pf_id number,
layerid number,
lon number,
lat number,
constraint pk_pid primary key(pf_id, layerid)
)

Index is generated by:

Create index on point_tab(get_point_geom(pf_id, layerid)) indextype is
mdsys.spatial_index.

So far, everything seems fine. Spatial index works perfectly.

However, when I am trying to delete any row from this table, I got:
ORA-04091 table string.string is mutating, trigger/function may not
see it.

It sounds like if I generate a function-based spatial index, then no
updates are allowed any more since while I dropped the index, I can
delete and insert. It is really annoyed. Is there anyone has similar
problems before? Can you share your solutions here? (The same problem
applies on insert but not on update).

The following is the function definition:

FUNCTION GET_POINT_GEOM(PPF_ID IN NUMBER, PLAYERID IN NUMBER)
RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC IS

PSRID NUMBER :=NULL;
PLON NUMBER := NULL;
PLAT NUMBER := NULL;

CURSOR get_lonlat(ppf_id IN NUMBER, playerid IN NUMBER) IS SELECT
LON,LAT
FROM POINT_TAB
WHERE playerid = layerid AND ppf_id = pf_id;

BEGIN

PSRID := 8265; --GET_SRID(PTOP_ID);

IF PSRID = NULL THEN
RAISE_APPLICATION_ERROR(-20001,' No topological layer found ! ');
RETURN NULL;
END IF;

OPEN get_lonlat(PPF_ID,PLAYERID);
FETCH get_lonlat INTO PLON, PLAT;

IF PLON = NULL OR PLAT = NULL THEN
RAISE_APPLICATION_ERROR(-20002,'No specified point found!Check
PF_ID...');
RETURN NULL;
END IF;

RETURN MDSYS.SDO_GEOMETRY(2001,PSRID,MDSYS.SDO_POINT_TYPE (PLON,PLAT,NULL),NULL
,NULL);
END GET_POINT_GEOM;
Many thanks,
Qiang