Write to oracle without repeating name

0

I have this block inside a procedure that records a representative, which comes from a screen in Classic ASP.

v_qtd_rep := ts_obtem_dados_no_xml(v_no_completo,'QTD_REP');

            for i in 1..v_qtd_rep loop

            --delete from prestador_representante where cod_prestador_ts = v_COD_PRESTADOR_TS;

            v_COD_PRESTADOR_TS   := ts_obtem_dados_no_xml(v_no_completo,'COD_PRESTADOR_TS');
            v_NOME_REPRESENTANTE := ts_obtem_dados_no_xml(v_no_completo,'NOME_REP_' || i);
            v_DDD_REPRESENTANTE  := ts_obtem_dados_no_xml(v_no_completo,'DDD_REP_' || i);
            v_TEL_REPRESENTANTE  := ts_obtem_dados_no_xml(v_no_completo,'TEL_REP_' || i);
            v_ID_REPRESENTANTE   := ts_obtem_dados_no_xml(v_no_completo,'ID_REPRESENTANTE_' || i);

            select prestador_representante_seq.nextval into vId from dual;

            --select nom_representante from ts.prestador_representante where cod_prestador_ts = v_COD_PRESTADOR_TS;

            --if v_ID_REPRESENTANTE is null then

              if v_NOME_REPRESENTANTE is not null and v_DDD_REPRESENTANTE is not null and v_TEL_REPRESENTANTE is not null and v_ID_REPRESENTANTE is null then
                      insert into ts.prestador_representante(id_representante, cod_prestador_ts, nom_representante, ddd_representante, tel_representante, dt_atu, cod_usuario_atu)
                                                       values(vId, v_COD_PRESTADOR_TS, v_NOME_REPRESENTANTE , v_DDD_REPRESENTANTE, v_TEL_REPRESENTANTE, sysdate, v_cod_usuario);
              --end if;
            /*else
              update prestador_representante set nom_representante = v_NOME_REPRESENTANTE, ddd_representante = v_DDD_REPRESENTANTE, tel_representante = v_TEL_REPRESENTANTE
                     where cod_prestador_ts = v_COD_PRESTADOR_TS;    */   
            end if;

            end loop;
            commit;

The fields that have the name, ddd, and tel are dynamic. A button is adding fields on the screen. A ASP function takes the data from the screen, transforms it into a XML and sends it to ORACLE . What happens when I open the screen and say it has 3 records, which are shown on the screen. If you order process, it turns into 5 and if process again boosts to 9 and so on. She does not repeat the first record and do not ask me, because I have no idea why. As I do, in the code above not to record the same name. The commented lines are my attempts to make them work, except those already deleted. I'm waiting.

    
asked by anonymous 23.11.2016 / 12:27

1 answer

1

pnet,

You are making an insert, so the record will always increase. Notice that when you get the data from the XML, in the last line you get a v_ID_REPRESENTANT, but just below you go in the provider provider_representant_seq and it brings a new id, which is stored in vId.

I do not understand exactly what you want to do, but I suppose changing this Insert with an Update should resolve.

    
23.11.2016 / 12:35