Updating st_geometry in an Oracle 11g trigger - index maintenance raises ORA-29470

Question

As described in http://www.davidlitchfield.com/blog/archives/00000023.htm "For Oracle 11g, Oracle has introduced some security changes to the DBMS_SQL package ... A third change to DBMS_SQL is checking the user ID executing the query and the user ID of the person that parsed the query. If the two don't match an error is generated: ORA-29470: Effective userid or roles are not the same as when cursor was parsed". It seems that mainenance of a spatial index on a st_geometry column uses dbms_sql, and that updating of a st_geometry column in a trigger raises this error. One workaround is to set security back to 10g levels by setting the initialization parameter _dbms_sql_security_level = 0 but this is not acceptable. Apart from getting ESRI to revisit index maintenance, has anyone another workaround? My application allows entry of an applications for one or more services - in the simple test I have tried to devise they are rubbish bin services as in my part of Sydney - red (general rubbish), yellow (recyclable materials), green (green garden waste). The system creates an application record for each requested service, but the point geometries are arranged around the centroid of the relevant land parcel so that in a map viewer they are not exactly coincident. An operator can drag a point onto another land parcel in a map editor if the original application was found to be wrong and my trigger tries to move the points for any related applications to again be arranged around the centroid of the new land parcel. My test code is: -- create a test table to hold rubbish bin applications; requests for different bin types from -- the one applicant will be arranged around the centroid of the applicant's land parcel (parcel and -- other details are omitted here for simplicity) so that in a map viewer they are not exactly coincident drop table applications; create table applications (objectid number(5,0) not null, point_shape sde.st_geometry, application_number nvarchar2(20), application_type nvarchar2(20)); -- insert applications for three different bin types from one applicant insert into applications values (1,sde.st_point(100,100,0),'A1','GREEN'); insert into applications values (2,sde.st_point(105,100,0),'A1','RED'); insert into applications values (3,sde.st_point(100,105,0),'A1','YELLOW'); commit; -- add indexes and constraints alter table applications add constraint app_pk primary key (objectid) using index; alter table applications add constraint app_uk unique (application_number,application_type) using index; create index app_idx0 on applications(point_shape) indextype is sde.st_spatial_index parameters('st_grids=10, 0, 0 st_srid=0'); -- now in the map viewer/editor, an application found to be linked to the wrong land parcel can be moved -- by dragging one of the bin points to the correct land parcel; we need a trigger to move related -- bin points as well create or replace trigger app_u for update of point_shape on applications compound trigger type num_tabtype is table of number index by pls_integer; type nvc20_tabtype is table of nvarchar2(30) index by pls_integer; type shape_tabtype is table of sde.st_geometry index by pls_integer; objectids num_tabtype; application_numbers nvc20_tabtype; point_shapes shape_tabtype; client_info varchar2(64); j pls_integer; -- cursor to find related applications whose positions will also need to be updated cursor related_applications (p_application_number in nvarchar2, p_objectid in number) is select objectid from applications where application_number = p_application_number and objectid <> p_objectid; rarec related_applications%rowtype; --------------------------------------------------------- before each row is begin -- check whether this update is the result of an application_point being dragged by a user, -- or the result of the statement part of this trigger dbms_application_info.read_client_info(client_info); if nvl(client_info,'null') <> 'after app_u trigger after upd stmt part' then -- record details of the application being updated and the new application point objectids(nvl(objectids.last,0) + 1):= :old.objectid; application_numbers(nvl(application_numbers.last,0) + 1):= :old.application_number; point_shapes(nvl(point_shapes.last,0) + 1):= :new.point_shape; end if; end before each row; --------------------------------------------------------- after statement is begin -- check whether this update is the result of an application_point being dragged by a user, -- or the result of the statement part of this trigger dbms_application_info.read_client_info(client_info); if nvl(client_info,'null') <> 'after app_u trigger after upd stmt part' then -- this update is caused by a user moving an application point; process it -- set some client info which can be tested to determine whether the update -- is triggered by a user or by the 'after statement' part dbms_application_info.set_client_info('after app_u trigger after upd stmt part'); -- loop through all updated applications for i in application_numbers.first..application_numbers.last loop -- check for related applications j:= 0; for rarec in related_applications (application_numbers(i), objectids(i)) loop -- reposition the points near the moved point j:= j + 1; update applications set point_shape = sde.st_point(sde.st_x(point_shapes(i))+mod(j,2)*5, sde.st_y(point_shapes(i))+mod(j-1,2)*5, sde.st_srid(point_shapes(i))) where objectid = rarec.objectid; end loop; end loop; -- clear the global application context dbms_application_info.set_client_info(null); end if; end after statement; end app_u; / -- check the data before update column objectid heading "OBJECT|ID" format 9999 column application_number heading "APP|NUMBER" format a6 column application_type heading "APP|TYPE" format a6 column point heading "APP|POINT" format a50 select objectid, application_number, application_type, sde.st_astext(point_shape) point from applications order by 2,3; /* output is: OBJECT APP APP APP ID NUMBER TYPE POINT ------ ------ ------ -------------------------------------------------- 1 A1 GREEN POINT ( 100.00000000 100.00000000) 2 A1 RED POINT ( 105.00000000 100.00000000) 3 A1 YELLOW POINT ( 100.00000000 105.00000000) */ -- try an update with the spatial index in place begin dbms_application_info.set_client_info(null); end; / update applications set point_shape = sde.st_point(200,200,sde.st_srid(point_shape)) where objectid = 1; /* output is: update applications set point_shape = sde.st_point(200,200,sde.st_srid(point_sha pe)) * ERROR at line 1: ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine ORA-29470: Effective userid or roles are not the same as when cursor was parsed ORA-06512: at "SYS.DBMS_SQL", line 1350 ORA-06512: at "SDE.SPX_UTIL", line 1906 ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 2112 ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 2239 ORA-06512: at "WEBDATA.APP_U", line 59 ORA-04088: error during execution of trigger 'WEBDATA.APP_U' */ -- try an update without the spatial index drop index app_idx0; begin dbms_application_info.set_client_info(null); end; / update applications set point_shape = sde.st_point(200,200,sde.st_srid(point_shape)) where objectid = 1; /* output is: 1 row updated. */ -- check the data after the update select objectid, application_number, application_type, sde.st_astext(point_shape) point from applications order by 2,3; /* output is: OBJECT APP APP APP ID NUMBER TYPE POINT ------ ------ ------ -------------------------------------------------- 1 A1 GREEN POINT ( 200.00000000 200.00000000) 2 A1 RED POINT ( 205.00000000 200.00000000) 3 A1 YELLOW POINT ( 200.00000000 205.00000000) */ -- clean up drop table applications;

MMBR · Answer

@JorgeLuisYupanqui  - there are many reasons for ORA-29470 see here Esri Support Search-Results

if you cannot find your issue there then please open a ticket with Esri Support to investigate your issue further.

JorgeLuisYupanqui · Answer

I'm having this problem in 2023, any idea on how to solve the

ORA-29470: Effective userid or roles are not the same as when cursor was parsed ?

SamuelAbati · Answer

Just had this same problem in 2017...

Using oracle 11g

I think it is related to using a compound trigger, I tried the "same" logic in a regular trigger and it worked, but we really need the compound trigger, hoping someone has a solution