Sequence in Oracle SQL with strange behavior

Asked

Viewed 69 times

0

I created an Oracle database with the following script:

CREATE SEQUENCE pcn_integra_ser_nfe
 INCREMENT BY 1
 START WITH 1
 MINVALUE 1
 MAXVALUE 9999999
 NOCYCLE
 NOORDER
 CACHE 20

It is used in only one package, outside any loop, and is called as follows:

   BEGIN
     -- SEQUENCE DO ARQUIVO DE NFE
     SELECT pcn_integra_ser_nfe.NEXTVAL
       INTO v_sequence
       FROM DUAL;
       pcn_salva_log_tab('diego',1,v_sequence);
   END;

Note that I put a routine (pcn_salva_log_tab) to be sure of the passages. In this control records only when I call the routine, so far everything is correct. But Quence has a totally erratic behavior. It normally starts at 1,2,3,4,5 and when I run some time later the routine is at 20,21,22 and then goes to 65,66,67 and so on. I have already reviewed all package, in the bank does not have this Quence anywhere else. It is called just below the cursors declaration, as below:

BEGIN -- PRINCIPAL

   BEGIN
     -- SEQUENCE DO ARQUIVO DE NFE
     SELECT pcn_integra_ser_nfe.NEXTVAL
       INTO v_sequence
       FROM DUAL;
       pcn_salva_log_tab('diego',1,v_sequence);
   END;

   -- DEFINE O NOME DO ARQUIVO
   v_label_arq := 'A'||Lpad(v_sequence,7,'0')||'.nfe'; 

   if not utl_file.is_open(arq_saida) then  -- abre o arquivo_saida e define onde vai gravar
       arq_saida := utl_file.fopen ( ltrim ( rtrim ( v_directory ) ) , ltrim ( rtrim ( v_label_arq ) ) , 'w' ); -- w > gravar
   end if;

     --CABEÇALHO
     v_registros :=   
           Rpad('HDR',3,' ')                            || 
           Rpad(v_codigo_empresa,3,' ')                 || 
           Rpad(v_compl_empresa,2,' ')                  ||
           Rpad(v_compl_empresa2,2,' ')                 || 
           Rpad('89425888000118',20,' ')                || 
           Rpad(' ',87,' ')                             || 
           Lpad(v_contador,6,'0'); 
     utl_file.put(arq_saida, v_registros||CRLF);
     utl_file.fflush(arq_saida);
     fnd_file.put_line(fnd_file.output,v_registros);

FOR r_nfe_cabecalho IN c_nfe_cabecalho LOOP

     -- USA PARA CONTAGEM DAS LINHAS 
     v_contador := v_contador + 1;

What could be wrong?

  • Publish pcn_salva_log_tab , the package’s precedent may be entering into some treated exception ? Remember that Quence ensures uniqueness but not integrity to avoid "toothless" it takes other controls.Some applications like tax bills or policy numbers do not allow failures.

  • @Motta a pcn_salva_log_tab only writes in a table the passage, we create it to not have direct commit in the package. On the exception dealt with, in this specific block are 2 querys only for the data load and have no Exception. I believe I will have to create a control table if I cannot run the Quence

  • Do a search in bd all_source or dependencies to make sure nothing calls the Quence and publish the sp all possible , cara Quence works since the 1970s. A front-end search (if applicable). But there can be no failure never use Quence , it is not effected by commits or rollbacks.

  • @Motta even created another Quence, the behavior continues. It has no front-end, it is a package that generates a txt for integration in another ERP. Think viavél create a custom table instead of using Quence?

  • Yes, as long as the numbering is not flawed.

  • http://glufke.net/oracle/viewtopic.php?f=2&t=9806

Show 1 more comment

1 answer

0


If someone goes through this weird situation, the solution is simple, just take the CACHE 20 for NOCACHE. The CACHE 20 made it "hold" 20 numbers each session, then evaluated and really that was the pattern. Apart from this it will always follow the value of CURRVAL + 1

CREATE SEQUENCE pcn_integra_ser_nfe
 INCREMENT BY 1
 START WITH 1
 MINVALUE 1
 MAXVALUE 9999999
 NOCYCLE
 NOORDER
 NOCACHE
  • This one I didn’t know, noted. I’ll take a test São Tomé ...

Browser other questions tagged

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