AnsweredAssumed Answered

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

Question asked by Eletrosul on Oct 2, 2017

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

 

Outcomes