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

1668
3
10-02-2017 01:48 PM
SamuelAbati
New Contributor III

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) :

0 Kudos
3 Replies
parksh
by
New Contributor II

Hi @SamuelAbati 

In 2021, I have the same issue.

Did you find any solution?

0 Kudos
JorgeLuisYupanqui
New Contributor

Hi, 

Same problem here, have you found a solution?

0 Kudos
MarceloMarques
Esri Regular Contributor

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

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov
0 Kudos