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_geome try.
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_p oint_geom(pf_id , layerid)) indextype is
mdsys.spatial_i ndex.
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_GEOME TRY 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_I D);
IF PSRID = NULL THEN
RAISE_APPLICATI ON_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_APPLICATI ON_ERROR(-20002,'No specified point found!Check
PF_ID...');
RETURN NULL;
END IF;
RETURN MDSYS.SDO_GEOME TRY(2001,PSRID, MDSYS.SDO_POINT _TYPE(PLON,PLAT ,NULL),NULL
,NULL);
END GET_POINT_GEOM;
Many thanks,
Qiang
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_geome try.
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_p oint_geom(pf_id , layerid)) indextype is
mdsys.spatial_i ndex.
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_GEOME TRY 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_I D);
IF PSRID = NULL THEN
RAISE_APPLICATI ON_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_APPLICATI ON_ERROR(-20002,'No specified point found!Check
PF_ID...');
RETURN NULL;
END IF;
RETURN MDSYS.SDO_GEOME TRY(2001,PSRID, MDSYS.SDO_POINT _TYPE(PLON,PLAT ,NULL),NULL
,NULL);
END GET_POINT_GEOM;
Many thanks,
Qiang