No update of a PL / SQL collection

0

I'm trying to update a parameter in a table, based on previous values of a collection , it will update the id attribute of a given document table id. I also created an insert into a test table, which performs perfectly, all attributes. I do not understand why the update is not working. If anyone can help, I appreciate it. I tested with a numeric value in the update, to see if there was any problem in the logic, except that in that case it did the update without problems. When I assign with a NUMBER variable receiving from the idDocument attribute or a% type of this attribute, it simply does not update. Here's 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;
    
asked by anonymous 10.10.2016 / 23:57

2 answers

0

The fetch was performed in the variable linhaRegCompara , while p_doc (used in the where clause of the update ) was never initialized, being null . With this, the condition is never satisfied and no record is updated.

    
13.10.2016 / 23:16
0

In some cases, depending on your Oracle configuration, you need to include

commit;

After inserting, deleting or updating records, otherwise it will not update the database.

    
20.10.2016 / 14:23