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
@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
– Diego
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
@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?
– Diego
Yes, as long as the numbering is not flawed.
– Motta
http://glufke.net/oracle/viewtopic.php?f=2&t=9806
– Motta