No update of a Collection PL/SQL

Asked

Viewed 265 times

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;
oracle pl-sql

  • 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'));

2 answers

1

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

commit;

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

0

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

  • Actually it does not need to be initialized, because p_doc is the variable that will store the idDocument column, as long as the query brings some result

  • p_doc is declared in the declare block, and is referenced only once in the program, in the Where of the update clause. Fetch is performed over lineRegCompra, so it would be necessary to use linharegcompra.iddocument in the update, instead of p_doc

Browser other questions tagged oracle pl-sql

You are not signed in. Login or sign up in order to post.