topic Re: ORA-29470: Effective userid or roles are not the same as when cursor was parsed, when trying to work with a compound trigger in Geodatabase Questions

ORA-29470: Effective userid or roles are not the same as when cursor was parsed, when trying to work with a compound trigger

SamuelAbati — Mon, 02 Oct 2017 20:48:15 GMT

Hello, I started receiving this error after creating a compound trigger in one of our features, in a oracle 11g database, the sde version is 10.4.1

Trigger code :

create or replace TRIGGER trigger_trecho_teste_simples
FOR DELETE or UPDATE OF shape ON tr_eixo_lt_ope
COMPOUND TRIGGER
trechos num_list := num_list();

BEFORE EACH ROW IS
BEGIN
trechos.extend;
IF UPDATING THEN
trechos(trechos.count) := :NEW.PK_CD_TR_LT_OPE;
ELSE
trechos(trechos.count) := :OLD.PK_CD_TR_LT_OPE;
end if;
END BEFORE EACH ROW;
AFTER STATEMENT IS
BEGIN
for linha in (SELECT DISTINCT FK_CD_LT_OPE cod_lt FROM EIXO_LT_OPE_COM_TR_EIXO_LT_OPE WHERE FK_CD_TR_LT_OPE MEMBER OF trechos)
loop
MERGE INTO eixo_lt_ope lt_update
USING (select sde.st_aggr_union(TRECHO.shape) as trecho_agg, SUM(TRECHO.ext) as n_ext from TR_EIXO_LT_OPE TRECHO , EIXO_LT_OPE_com_TR_EIXO_LT_OPE EIXO_LINHA
where TRECHO.PK_CD_TR_LT_OPE = EIXO_LINHA.FK_CD_TR_LT_OPE
and EIXO_LINHA.FK_CD_LT_OPE = linha.cod_lt) results
ON (lt_update.PK_CD_LT_OPE = linha.cod_lt)
WHEN MATCHED THEN UPDATE SET
lt_update.shape = results.trecho_agg,
lt_update.ext = results.n_ext;
raise_application_error(-20001,'hi');
end loop;
END AFTER STATEMENT;
END trigger_trecho_teste_simples;

Image of the stack trace (Couldn't copy it from arcmap) :

Re: ORA-29470: Effective userid or roles are not the same as when cursor was parsed, when trying to work with a compound trigger

parksh — Thu, 27 May 2021 00:42:38 GMT

Hi @SamuelAbati

In 2021, I have the same issue.

Did you find any solution?

Re: ORA-29470: Effective userid or roles are not the same as when cursor was parsed, when trying to work with a compound trigger

JorgeLuisYupanqui — Mon, 09 Jan 2023 21:09:57 GMT

Hi,

Same problem here, have you found a solution?

Re: ORA-29470: Effective userid or roles are not the same as when cursor was parsed, when trying to work with a compound trigger

MarceloMarques — Wed, 11 Jan 2023 21:36:42 GMT

Please, open a ticket with Esri Technical Support to troubleshoot this further.

Tip: read more in the link below
https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/sql-access-to-geodatabase-data.htm