0
I am trying to update a parameter in a table, based on previous values of a Collection, it will update the idAccept attribute of a particular document table id. I also created an Insert in a test table, which performs perfectly, all the attributes. I don’t understand why the update isn’t working. If anyone can help, I’d appreciate it. I tested with a numerical value in the update, to see if there was any problem in the logic, but in this case did the update without problems. When assigning with a NUMBER variable receiving from the idDocument attribute or a %type of that attribute, it simply does not update. Follow the script:
DECLARE
doc NUMBER;
CURSOR regCompara IS SELECT TO_NUMBER(SEQ_ID) AS seqColecao ,TO_NUMBER(c001) AS idPessoa, TO_NUMBER(c002) AS idDocumento, TO_NUMBER(c003) AS idAcolhimento FROM APEX_collections
WHERE collection_name = 'COLECAO_DOCUMENTO_PESSOA';
linhaRegCompara regCompara%ROWTYPE;
p_doc linhaRegCompara.idDocumento%TYPE;
BEGIN
--Faz um loop sem comparação atualizando o atributo do acolhimento
IF :P60_ID_NOVO IS NOT NULL THEN
OPEN regCompara;
LOOP
FETCH regCompara INTO linharegCompara;
EXIT WHEN regCompara%NOTFOUND ;
--doc := linhaRegCompara.idDocumento;
update DOCUMENTO SET id_acolhimento = :P60_ID_NOVO WHERE id = p_doc;
insert into teste (id_acolhimento, id_doc,id_pessoa) values (:P60_ID_NOVO,linhaRegCompara.idDocumento,linhaRegCompara.idPessoa);
END LOOP ;
CLOSE regCompara;
END IF;
END;
Dear, I ended up finding another solution that solved the update problem. I performed an update, without cursors, doing with nested selects. But I couldn’t figure out why it didn’t work, at first. If anyone knows why, please help out there. Here is the update command: update document set id_host = :P60_ID_NOVO Where id in (select id from document Where id in (select C001 FROM Apex_collections WHERE collection_name = 'COLECAO_DOCUMENTO_PESSOA'));
– Jose Gustavo Lima