Bug of function-based index

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 Received on Wed Nov 26 2003 - 16:18:00 CET