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

[GeoffLau]

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;

[SamuelAbati]

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

[JorgeLuisYupanqui]

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 ?

[MarceloMarques]

@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.